c# - 如何使用 LINQ 和 EF6 编写外部联接代码？

Question

我有三个表 Exam、Test 和 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])
);

一个考试可以有很多测试，一个用户可以多次尝试任何测试。

如何使用扩展方法语法编写 LINQ 语句，使我能够看到 UserId == 1 的以下内容(我假设 UserId == 1 在 Where 子句中):

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

或者如果 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        

或者如果 UserId 为空

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

请注意，由于我收到的建议，这个问题已经发生了一些变化。现在有一个赏金，我希望能得到一个我可以接受的快速答案。

Answer 1

如果您的 Test 实体有一个 UserTests 集合，您可以使用此查询:

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();

在此处使用 DefaultIfEmpty() 可确保 LEFT OUTER JOIN，这样您始终至少有一个 UserTest 实体(可能是 null) 对于给定的 Test。将 UserTest 的不可空属性(如 UserTestId)强制转换为可空类型(例如 int?)在这里很重要，否则您可以获得一个异常，即从数据库返回的 NULL 值不能存储在不可为 null 的 .NET 类型中。

如果您没有也不希望在您的 Test 实体中使用 UserTests 集合，您可以使用 GroupJoin 作为替代方案基本上会通过 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();