需要帮助在Linq查询左外连接

Question

如何在LINQ中编写:

SELECT 
    T.TestId,
    S.SubjectName+' >> '+T.TestName +' ('+ CONVERT(VARCHAR(10),COUNT(Q.TestId)) +')' TestData
FROM 
    Test T LEFT OUTER JOIN Subject S
    ON T.SubjectId = S.SubjectId
    LEFT OUTER JOIN Quest Q
    ON T.TestId = Q.TestId
GROUP BY 
    Q.TestId, 
    T.TestId,
    T.TestName,
    S.SubjectName
ORDER BY 
    COUNT(Q.TestId) DESC

Left Outer Join &在LINQ中分组。

Case II:

SELECT 
    S.SubjectName,
    T.TestName,
    Q.Question,
    A.Answer,
    A.IsCorrect
FROM Ans A
    INNER JOIN Quest Q
    ON A.QuestId = Q.QuestId
    AND A.QuestId IN ( SELECT 
                            Q.QuestId
                        FROM Quest Q
                            INNER JOIN Test T
                            ON Q.TestId = T.TestId )
    INNER JOIN Subject S
    ON A.SubjectId = S.SubjectId
    INNER JOIN Test T
    ON A.TestId = T.TestId 

谢谢。

Answer 1

要在Linq中执行外连接，您需要使用DefaultIfEmpty扩展方法:

var query =
    from t in db.Test
    join s in db.Subject on t.SubjectId equals s.SubjectId into testSubject
    from s in testSubject.DefaultIfEmpty()
    join q in db.Quest on t.TestId equals q.TestId into testQuest
    from q in testQuest.DefaultIfEmpty()
    group by new
    {
        t.TestId,
        t.TestName,
        s.SubjectName
    }
    select new
    {
        g.Key.TestId,
        g.Key.TestName,
        g.Key.SubjectName,
        Count = g.Count()
    };
var results = from r in query.AsEnumerable()
              select new
              {
                  r.TestId,
                  TestData = string.Format("{0} >> {1} ({2})", r.SubjectName, t.TestName, r.Count);
              }

注意，在group by子句中不需要同时使用t.TestId和q.TestId，因为它们将具有相同的值。对于最后一部分，我使用AsEnumerable，以便在内存中执行最终投影，而不是在DB中，这允许使用string.Format。

Answer 2

如果您有映射，那么不需要任何连接就应该很容易:

from test in tests
let count = test.Quests.Count()
orderby count descending
select
    new
    {
        test.Id,
        TestData =
            test.Subject == null
                ? null
                : string.Format("{0} >> {1} ({2})", test.Subject.Name, test.Name, count)
    };

编辑:看了Thomas Levesque的回答后，我意识到这行不通，但下面的应该:

var query = from test in tests
            let count = test.Quests.Count()
            orderby count descending
            select
                new
                {
                    test.Id,
                    test.Name,
                    test.Subject,
                    Count = count
                };
var results = query
    .AsEnumerable()
    .Select(
        t =>
        new
        {
            t.Id,
            TestData =
            t.Subject == null
                ? null
                : string.Format("{0} >> {1} ({2})", t.Subject.Name, t.Name, t.Count)
        });
        results.Dump();
    }