需要帮助Linq Queries左外加入

how can i write this in LINQ :

我怎么能在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

Need help in writing Left Outer Join & Group by in LINQ .

在LINQ中需要帮助撰写Left Outer Join&Group by。

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

Thanks.

2 个解决方案

#1

To perform an outer join in Linq, you need to use the DefaultIfEmpty extension method:

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

Note that you don't need both t.TestId and q.TestId in the group by clause, since they will have the same value. For the last part, I'm using AsEnumerable so that the final projection is performed in memory rather than in the DB, which enables the use of string.Format.

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

#2

If you have the mappings, it should be easy without any joins:

如果你有映射,它应该很容易没有任何连接:

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

EDIT: After reading Thomas Levesque's answer, I realized this wouldn't work, but the following should:

编辑:在阅读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();
    }

#1