在 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 中分组。

案例二：

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

请注意，您不需要同时使用 t.TestId 和 q.TestId< /code> 位于 group by 子句中，因为它们具有相同的值。对于最后一部分，我使用 AsEnumerable 以便最终投影在内存中而不是在数据库中执行，从而可以使用 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();
    }