如何使用 Linq 实现此 Sql 块包含（Having、Join 和 Group By）

Question

我的sql语句是

SELECT c.type,c.title,c.datereg, d.ranknum
FROM T_News AS c
  INNER JOIN (
    SELECT a.id, COUNT(*) AS ranknum
    FROM T_News AS a
          INNER JOIN T_News AS b 
            ON (a.type = b.type) 
            AND (a.datereg >= b.datereg)
    GROUP BY a.id
    HAVING COUNT(*) <= 3
  ) AS d ON (c.id = d.id)
ORDER BY c.type, d.ranknum

我得到http://rickosborne.org/blog/2008/01/sql-getting-top-n-rows-for-a-grouped-query/

用于获取分组查询的前 N ​​行，

EFUnitOfWork EF = new EFUnitOfWork();
T_NewsRepository News = new T_NewsRepository();
News.UnitOfWork = EF;
var query = 
    from news1 in News.All()
    join news2 in News.All() 
        on news1.type equals news2.type into resjoin
    group news1 by news1.id into idgroup
    where idgroup.Count() <= 3
    select new { idgroup };

var x = query.ToList();

我没有得到任何错误，但“where idgroup.Count() <= 3”不起作用，我得到了数据库中的所有行作为结果

Answer 1

将其分解为最小的组件，然后从中组成更大的查询。让我们从最有意义的查询开始：

SELECT 
    a.id, COUNT(*) AS ranknum
FROM 
    T_News AS a
        INNER JOIN T_News AS b ON 
            (a.type = b.type) AND
            (a.datereg >= b.datereg)
GROUP BY 
    a.id
HAVING 
    COUNT(*) <= 3

我将其转换为：

// Items with counts/ranknum
var ranknum = 
    from a in News.All()
    join b in News.All() on
        a.type equals b.type
    where
        a.datereg > b.datereg
    group by a.id into g
    select new { g.Key as id, g.Count() as ranknum };

// Filter the ranknum.
ranknum = ranknum.Where(rn => rn.ranknum <= 3);

然后将其与外部查询连接：

SELECT 
    c.type,c.title,c.datereg, d.ranknum
FROM 
    T_News AS c
        INNER JOIN (<sub-query from above>) as d ON
            c.id = d.id
ORDER BY
    c.type, d.ranknum

该部分变得简单，因为它只是两个现有查询之间的连接。

var query =
    from c in News.All()
    join rn in ranknum on c.id = rn.id
    orderby c.type, rn.ranknum
    select new { c.type, c.title, c.datereg, rn.ranknum };

LINQ-to-Entities 为此生成的 SQL 可能会看起来非常难看，并且可能效率低下，在这种情况下，您可能需要考虑将此逻辑放入存储过程中，然后通过 LINQ-to-Entities 调用that（对于更复杂的查询通常是这样）。