左外连接和多个计数SQL到LINQ-Java 学习之路

如何使用内部联接，左外部联接，分组依据和两个计数的查询转换为linq？

SELECT
    c.EndowmentID,
    COUNT(DISTINCT f.CriterionID) AS RequiredCriteria,
    COUNT(r.ChoiceID) AS Response
FROM
    Criteria c
INNER JOIN
    Filters f
ON
    c.ID = f.CriterionID
LEFT OUTER JOIN 
    Responses r
ON
    f.ChoiceID = r.ChoiceID
WHERE
    f.IsRequirement = 1
GROUP BY
    c.EndowmentID;

这是我到目前为止所做的：

var result =
                from c in context.Criteria
                join f in context.Filters on c.ID equals f.CriterionID
                join r in context.Responses on f.ChoiceID equals r.ChoiceID into resfil
                from rf in resfil.DefaultIfEmpty()
                group rf by c.EndowmentID into grouped
                select new 
                {
                    EndowmentID = grouped.Key,
                    Requirements = grouped.Count(t=>t.CriterionID),
                    Response = grouped.Count(t=>t.ChoiceID)
                };

1 回答

0
你需要 group 使用匿名类 . 这将允许您访问 select 语句中的所有表
```
group new { c, f, rf } by c.EndowmentID into grouped
```
SQL： COUNT(DISTINCT f.CriterionID) AS RequiredCriteria,

这可以通过首先选择 f.CriterionID 列， Distinct() ， Count() 来编写
```
RequiredCriteria = grouped.Select(x => x.f.CriterionID).Distinct().Count()
```
SQL： COUNT(r.ChoiceID)
```
Response = grouped.Select(x => x.rf.ChoiceID).Count()
```
回复于 2024-04-26T17:07:41+08:00

左外连接和多个计数SQL到LINQ

1 回答

相关问题