将组中的每个第一个元素取为 [英] Take each first element of the group by

问题描述

如何使用EF 5获取组中的每个第一个元素?

How can I take each first element in the group with EF 5 ?

      var result = await context.SomeDbSet
          .Where(...)
          .GroupBy(x => new { x.SomeField, ... })
          .Select(x => x.First())
          .ToListAsync();

我正在获得不受支持的例外.

I am getting not supported exception.

如何正确重写查询?谢谢.

How to correctly rewrite query? Thanks.

推荐答案

您不能通过分组来做到这一点.SQL有一个限制-使用 GROUP BY ，您只能选择分组键和聚合结果.此限制可以肯定地扩展到 LINQ to Entities -在 GroupBy 之后，您只能选择分组键和聚合结果.

You cannot do that with grouping. SQL has a limitation - with GROUP BY you can select only grouping keys and aggregation result. This limitation for sure extended to LINQ to Entities - after GroupBy you can select only grouping keys and aggregation result.

这种结果可以通过SQL和Window函数来实现:

Such result can be achieved by SQL and Window functions:

SELECT
  r.*,
FROM 
(
   SELECT 
      s.*, 
      ROW_NUMBER() OVER(PARTITION BY s.SomeField1, s.SomeField2 ORDER BY s.SomeDate) AS RN
   FROM SomeDbSet s
   WHERE ...
) r
WHERE r.RN = 1

对于那些想使用LINQ的人，我建议扩展(免责声明:我是扩展创建者)linq2db.EntityFrameworkCore

For those who want to stay with LINQ, I propose extension (disclaimer: I'm extension creator) linq2db.EntityFrameworkCore

您可以通过LINQ在上面编写查询

And you can write query above via LINQ

var rnQuery = 
   from s in context.SomeDbSet
   where ...
   select new 
   {
      Data = s,
      RN = Sql.Ext.RowNumber().Over()
              .PartitionBy(s.SomeField1, s.SomeField2)
              .OrderBy(s.SomeDate)
              .ToValue()
   }

var resultQuery = await rnQuery
   .Where(r => r.RN == 1)
   .Select(r => r.Data)
   .ToLinqToDB();

var result = resultQuery.ToList();

// async variant may need ToListAsyncLinqToDB() call 
// because of collision in async extension methods between EF Core and linq2db
var result = await resultQuery.ToListAsyncLinqToDB();

这篇关于将组中的每个第一个元素取为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！