How can I code an outer join using LINQ and EF6?

Question

I have three tables Exam, Test and UserTest.

CREATE TABLE [dbo].[Exam] (
    [ExamId]                      INT            IDENTITY (1, 1) NOT NULL,
    [SubjectId]                   INT            NOT NULL,
    [Name]                        NVARCHAR (50)  NOT NULL,
    [Description]                 NVARCHAR (MAX) NOT NULL,
    CONSTRAINT [PK_Exam] PRIMARY KEY CLUSTERED ([ExamId] ASC),
    CONSTRAINT [FK_ExamSubject] FOREIGN KEY ([SubjectId]) REFERENCES [dbo].[Subject] ([SubjectId]),
    CONSTRAINT [FK_Exam_ExamType] FOREIGN KEY ([ExamTypeId]) REFERENCES [dbo].[ExamType] ([ExamTypeId])
);

CREATE TABLE [dbo].[Test] (
    [TestId]      INT            IDENTITY (1, 1) NOT NULL,
    [ExamId]      INT            NOT NULL,
    [Title]       NVARCHAR (100) NULL,
    [Status]      INT            NOT NULL,
    [CreatedDate] DATETIME       NOT NULL,
    CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED ([TestId] ASC),
    CONSTRAINT [FK_TestExam] FOREIGN KEY ([ExamId]) REFERENCES [dbo].[Exam] ([ExamId])
);

CREATE TABLE [dbo].[UserTest] (
    [UserTestId]              INT            IDENTITY (1, 1) NOT NULL,
    [UserId]                  NVARCHAR (128) NOT NULL,
    [TestId]                  INT            NOT NULL,
    [Result]                  INT            NULL
    CONSTRAINT [PK_UserTest] PRIMARY KEY CLUSTERED ([UserTestId] ASC),
    CONSTRAINT [FK_UserTestTest] FOREIGN KEY ([TestId]) REFERENCES [dbo].[Test] ([TestId])
);

An exam can have many tests and a user can try any test a number of times.

How can I code a LINQ statement using the extension method syntax that allows me to see the following for UserId == 1 (I assume UserId == 1 in a Where clause) :

Exam       Test      Title           UserTestID  UserId     Result
1          1         1a               1           1          20 
1          1         1a               2           1          30
1          1         1a               3           1          40         
1          2         1b               4           1          98 
1          3         1c               5           1          44
2          4         2a
2          5         2b               6           1          12

Or if UserId == 2:

Exam       Test      Title           UserTestID  UserId     Result
1          1         1a               7           2          27  
1          2         1b        
1          3         1c               8           2          45
2          4         2a
2          5         2b        

Or if UserId is null

Exam       Test      Title           UserTestID  UserId     Result
1          1         1a        
1          2         1b
1          3         1c  
2          4         2a
2          5         2b   

Note this question has undergone a few changes thanks to suggestions I received. Now there is a bounty I hope for a quick answer that I can accept.

Answer 1

If your Test entity has a UserTests collection you can use this query:

string userId = "1";
var result = context.Tests
    .SelectMany(t => t.UserTests
        .Where(ut => ut.UserId == userId)
        .DefaultIfEmpty()
        .Select(ut => new
        {
            ExamId = t.ExamId,
            TestId = t.TestId,
            Title = t.Title,
            UserTestId = (int?)ut.UserTestId,
            UserId = ut.UserId,
            Result = ut.Result
        }))
    .OrderBy(x => x.ExamId)
    .ThenBy(x => x.TestId)
    .ThenBy(x => x.UserTestId)
    .ToList();

Using DefaultIfEmpty() here ensures a LEFT OUTER JOIN so that you always have at least one UserTest entity (which is possibly null) for a given Test. Casting the non-nullable properties of the UserTest - like UserTestId - to a nullable type (int? for example) is important here, otherwise you can get an exception that a NULL value returned from the database can't be stored in a non-nullable .NET type.

If you don't have and don't want a UserTests collection in you Test entity you can use a GroupJoin as alternative which would basically left outer join the two tables by the TestId:

string userId = "1";
var result = context.Tests
    .GroupJoin(context.UserTests.Where(ut => ut.UserId == userId),
        t => t.TestId,
        ut => ut.TestId,
        (t, utCollection) => new
        {
            Test = t,
            UserTests = utCollection
        })
    .SelectMany(x => x.UserTests
        .DefaultIfEmpty()
        .Select(ut => new
        {
            ExamId = x.Test.ExamId,
            TestId = x.Test.TestId,
            Title = x.Test.Title,
            UserTestId = (int?)ut.UserTestId,
            UserId = ut.UserId,
            Result = ut.Result
        }))
    .OrderBy(x => x.ExamId)
    .ThenBy(x => x.TestId)
    .ThenBy(x => x.UserTestId)
    .ToList();

Answer 2

 var tests = (from t in context.Tests
       // where !t.UsertTests.Any() //if no user took the test
         //    || t.UserTests.Any(ut=>ut.Student.StudentId == stId)
        select new {Test = t, Exam = t.Exam, 
                 UserTests = t.UserTests.Where(ut=>ut.Student.StudentId == stId))
       .ToList();

On 2nd thought, may be this will be better. This will give you exam, test and usertests if there is any matching ones or null usertests

Answer 3

Check this line here

http://social.msdn.microsoft.com/Forums/en-US/aba0fb67-d290-475c-a639-075424096b29/linq-joining-3-tables?forum=linqtosql

you might get some idea for how to do this.

Answer 4

Here is a link to a discussion that shows how to call stored procedures with a parameter: How to use DbContext.Database.SqlQuery<TElement>(sql, params) with stored procedure? EF Code First CTP5

Here is one way to code the stored procedure:

CREATE PROCEDURE dbo.sample1 (
@oneId NVARCHAR(128) = N'xx') AS
BEGIN
SET NOCOUNT ON;

SELECT @oneId AS userId,
    r.TestId, 
    r.Result
FROM (
    SELECT t.UserId, e.testId, t.Result
    FROM dbo.UserTest AS e
    LEFT OUTER JOIN dbo.UserTest AS t ON e.TestId = t.TestId AND t.UserId = @oneId
    WHERE  e.UserId = 0) AS r 
ORDER BY r.TestId 

END 
go

Answer 5

Try this:

var tests = context.Tests.Include( "Exam" )
    .Select( t => new
    {
        Test = t,
        UserTests = t.UserTests.Where( ut => ut.UserId == studentId )
    } )
    .ToList();