c# - 使用 LINQ 编写 case 语句(使用 GROUP BY)

Question

我真的在与 LINQ 作斗争。我已经用 SQL 写出了我的查询(实际上它是一个枢轴)，并试图在 LINQ 中写一个等效的语句。任何向前的指针将不胜感激。代码见下:

SELECT b.URL,
SUM(CASE WHEN (a.ExtFlag = 0) THEN 1 ELSE 0 END) AS IntLinks,   
SUM(CASE WHEN (a.ResponseCode >= 400 AND a.ExtFlag = 0) THEN 1 ELSE 0 END) AS IntBroken,
SUM(CASE WHEN (a.ExtFlag = 1) THEN 1 ELSE 0 END) AS ExtLinks,   
SUM(CASE WHEN (a.ResponseCode >= 400 AND a.ExtFlag = 1) THEN 1 ELSE 0 END) AS ExtBroken
FROM Link a
INNER JOIN Host b
ON a.HostID = b.ID
GROUP BY b.URL
ORDER BY b.URL

非常感谢。

Answer 1

我认为这应该可以满足您的需求 - 至少值得一试:

var query = from link in db.Links
            join host in db.Hosts on link.HostID equals host.ID
            group link by host.Url into links
            select new
            {
                Url = links.Url,
                IntLinks  = links.Count(link => link.ExtFlag == 0),
                IntBroken = links.Count(link => link.ExtFlag == 0 &&
                                                link.ResponseCode >= 400),
                ExtLinks =  links.Count(link => link.ExtFlag == 1),
                ExtBroken = links.Count(link => link.ExtFlag == 1 &&
                                                link.ResponseCode >= 400),
            };