LEFT JOIN Issue - LINQ

Hi Experts!

Thanks for reading this.

Having trouble with a simple LINQ to Entity query..
Say you've three tables to hold a Survey:
SurveyQuestions - the questions
SurveyScales - 0) Never 1) Seldom 2) Sometimes 3) Always
SurveyUserAnswers - person taking the survey (saved to table)

Here's the script to create the table and insert dummy data:
CREATE TABLE [dbo].[SurveyQuestions](
	[SurveyQuestionID] [int] IDENTITY(1,1) NOT NULL,
	[SurveyQuestionCounter] [int] NOT NULL,
	[SurveyQuestionText] [nvarchar](500) NOT NULL,
	[DateCreated] [datetime] NOT NULL,
 CONSTRAINT [PK_dbo.SurveyQuestions] PRIMARY KEY CLUSTERED 
(
	[SurveyQuestionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[SurveyScales](
	[SurveyScaleID] [int] IDENTITY(1,1) NOT NULL,
	[SurveyScaleValue] [int] NOT NULL,
	[SurveyScaleText] [nvarchar](10) NOT NULL,
	[DateCreated] [datetime] NOT NULL,
 CONSTRAINT [PK_dbo.SurveyScales] PRIMARY KEY CLUSTERED 
(
	[SurveyScaleID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[SurveyUserAnswers](
	[SurveyUserAnswerID] [int] IDENTITY(1,1) NOT NULL,
	[SurveyScaleID] [int] NOT NULL,
	[SurveyQuestionID] [int] NOT NULL,
	[UserID] [int] NOT NULL,
	[DateCreated] [datetime] NOT NULL,
 CONSTRAINT [PK_dbo.SurveyUserAnswers] PRIMARY KEY CLUSTERED 
(
	[SurveyUserAnswerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[SurveyUserAnswers]  WITH CHECK ADD  CONSTRAINT [FK_dbo.SurveyUserAnswers_dbo.SurveyQuestions_SurveyQuestionID] FOREIGN KEY([SurveyQuestionID])
REFERENCES [dbo].[SurveyQuestions] ([SurveyQuestionID])
GO

ALTER TABLE [dbo].[SurveyUserAnswers] CHECK CONSTRAINT [FK_dbo.SurveyUserAnswers_dbo.SurveyQuestions_SurveyQuestionID]
GO

ALTER TABLE [dbo].[SurveyUserAnswers]  WITH CHECK ADD  CONSTRAINT [FK_dbo.SurveyUserAnswers_dbo.SurveyScales_SurveyScaleID] FOREIGN KEY([SurveyScaleID])
REFERENCES [dbo].[SurveyScales] ([SurveyScaleID])
GO

ALTER TABLE [dbo].[SurveyUserAnswers] CHECK CONSTRAINT [FK_dbo.SurveyUserAnswers_dbo.SurveyScales_SurveyScaleID]
GO

--INSERTS 

INSERT INTO [dbo].[SurveyScales] ([SurveyScaleValue], [SurveyScaleText], [DateCreated]) VALUES (0, 'Never', GETDATE())
INSERT INTO [dbo].[SurveyScales] ([SurveyScaleValue], [SurveyScaleText], [DateCreated]) VALUES (1, 'Seldom', GETDATE())
INSERT INTO [dbo].[SurveyScales] ([SurveyScaleValue], [SurveyScaleText], [DateCreated]) VALUES (2, 'Sometimes', GETDATE())
INSERT INTO [dbo].[SurveyScales] ([SurveyScaleValue], [SurveyScaleText], [DateCreated]) VALUES (3, 'Always', GETDATE())

INSERT INTO [dbo].[SurveyQuestions] ([SurveyQuestionCounter], [SurveyQuestionText], [DateCreated]) VALUES(1, 'Question A', GETDATE())
INSERT INTO [dbo].[SurveyQuestions] ([SurveyQuestionCounter], [SurveyQuestionText], [DateCreated]) VALUES(2, 'Question B', GETDATE())
INSERT INTO [dbo].[SurveyQuestions] ([SurveyQuestionCounter], [SurveyQuestionText], [DateCreated]) VALUES(3, 'Question C', GETDATE())
INSERT INTO [dbo].[SurveyQuestions] ([SurveyQuestionCounter], [SurveyQuestionText], [DateCreated]) VALUES(4, 'Question D', GETDATE())


INSERT INTO [dbo].[SurveyUserAnswers] ([SurveyScaleID], [SurveyQuestionID], [UserID], [DateCreated]) VALUES(1, 1, 1, GETDATE())

INSERT INTO [dbo].[SurveyUserAnswers] ([SurveyScaleID], [SurveyQuestionID], [UserID], [DateCreated]) VALUES(2, 2, 1, GETDATE())
INSERT INTO [dbo].[SurveyUserAnswers] ([SurveyScaleID], [SurveyQuestionID], [UserID], [DateCreated]) VALUES(3, 3, 1, GETDATE())
INSERT INTO [dbo].[SurveyUserAnswers] ([SurveyScaleID], [SurveyQuestionID], [UserID], [DateCreated]) VALUES(4, 4, 1, GETDATE())
INSERT INTO [dbo].[SurveyUserAnswers] ([SurveyScaleID], [SurveyQuestionID], [UserID], [DateCreated]) VALUES(1, 1, 2, GETDATE())
INSERT INTO [dbo].[SurveyUserAnswers] ([SurveyScaleID], [SurveyQuestionID], [UserID], [DateCreated]) VALUES(2, 2, 2, GETDATE())

Open in new window


So, in T-SQL I've something like this:      
SELECT sq.SurveyQuestionCounter, sq.SurveyQuestionText, ss.SurveyScaleText, ss.SurveyScaleValue
FROM [dbo].SurveyQuestions sq
LEFT JOIN SurveyUserAnswers sa ON sq.SurveyQuestionID = sa.SurveyQuestionID
INNER JOIN SurveyScales ss ON sa.SurveyScaleID = ss.SurveyScaleID
WHERE sq.SurveyQuestionCounter <= 10 AND 
sa.UserID = 2	

Open in new window

It returns this:
SurveyQuestionCounter  SurveyQuestionText    SurveyScaleText SurveyScaleValue
---------------------- -------------------   -------         -----------------
1                      Question A            Never           0
2                      Question B            Seldom          1

Open in new window

I though with the LEFT JOIN to SurveyUserAnswers it would return all the questions and if there's no user anwer then it's null.
But that's not the case.

What I want is:
SurveyQuestionCounter  SurveyQuestionText    SurveyScaleText SurveyScaleValue
---------------------- -------------------   -------         -----------------
1                      Question A            Never           0
2                      Question B            Seldom          1
3                      Question C            NULL            NULL
4                      Question D            NULL            NULL

Open in new window

Would you pleae help write this out in LINQ to Entity?

TIA!
LVL 5
allanau20Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

coreconceptsCommented:
Hi allanau20,

       I made your join to the scale and the questions in a subquery so that the where clause wouldn't filter out your results.  A note being that your FROM clause (including all joins) is processed long before your WHERE clause, so the result set you produce from your join (regardless of how you join INNER, OUTER, CROSS etc.,) is going to filter down your result set.  

Please see if this looks right though, as I got a null where you didn't in your sample - but I think that might have been miskeyed, please advise.. code follows:

SELECT sq.SurveyQuestionCounter
, sq.SurveyQuestionText
, sac.SurveyScaleText
, sac.SurveyScaleValue
FROM [dbo].SurveyQuestions sq
LEFT JOIN 
			(
			SELECT sa.DateCreated
			, sa.SurveyQuestionID
			, sa.SurveyScaleID
			, sc.SurveyScaleText
			, sc.SurveyScaleValue
			, sa.SurveyUserAnswerID
			, sa.UserID
			FROM SurveyUserAnswers sa
			INNER JOIN SurveyScales as sc
				ON sa.SurveyScaleID = sc.SurveyScaleID
			WHERE sa.UserID = 2
			) As sac
			ON sq.SurveyQuestionID = sac.SurveyQuestionID
WHERE sq.SurveyQuestionCounter <= 10 

Open in new window


Edited : actually re-read your result set and mine matches... here's picture:
Allanau20.png
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
allanau20Author Commented:
ahh.. that makes sense.
I can convert that the TSQL to Linq to entity. thanks for your quick post!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.