C# 实体框架 - 带有 order by 和 group by 的 Linq 查询

Entity Framework - Linq query with order by and group by

提问by zufallsfund

I have MeasurementObjects with the relevant Properties CreationTime(DateTime) and Reference(String) and some other values.

我有Measurement具有相关属性CreationTime(DateTime) 和Reference(String) 以及其他一些值的对象。

I'd like to write an efficient linq query to a DbContextthat

我想写一个高效的LINQ查询到DbContext该

• groups my Measurementobjects by a given Reference
• orders the groups by either the CreationTimeProperty of the first Measurementfrom the group or the average of the CreationTimeproperties
• limits the query to the most recent numOfEntries

• Measurement按给定的对象对我的对象进行分组Reference
• 按组中第一个的CreationTime属性Measurement或CreationTime属性的平均值对组进行排序
• 将查询限制为最近的 numOfEntries

(In a later step i calculate averaged values over these returned groups, but i guess that's not relevant for my problem.)

（在后面的步骤中，我计算了这些返回组的平均值，但我想这与我的问题无关。）

The DbContext itself has a DbSet<Measurement>called Measurementsholding all Measurementobjects.

DbContext 本身有一个保存所有对象的DbSet<Measurement>调用。MeasurementsMeasurement

I came up with the following query, that results in a List of groups that is ordered correctly but is missing some groups in between.

我想出了以下查询，结果是一个正确排序的组列表，但中间缺少一些组。

var groupByReference = (from m in context.Measurements
                          orderby m.CreationTime
                          group m by new { m.Reference } into g
                          select g).Take(numOfEntries).ToList();

How do I select the "most recent" groups of Measurements correctly?

如何Measurement正确选择“最近”的s组？

I'm using Entity Framework 4.4 with a MySQL Database (MySql Connector/Net 6.6.4). This example is simplified, i can go into more detail and/or give an example if necessary.

我将 Entity Framework 4.4 与 MySQL 数据库（MySql Connector/Net 6.6.4）一起使用。这个例子是简化的，如果有必要，我可以更详细地和/或给出一个例子。

Thanks!

谢谢！

回答by MarcinJuraszek

Try moving the order byafter group by:

尝试移动order by之后group by：

var groupByReference = (from m in context.Measurements
                        group m by new { m.Reference } into g
                        order by g.Avg(i => i.CreationTime)
                        select g).Take(numOfEntries).ToList();

回答by NinjaNye

It's method syntax (which I find easier to read) but this might do it

这是方法语法（我觉得更容易阅读）但这可能会做到

Updated post comment

更新了帖子评论

Use .FirstOrDefault()instead of .First()

使用.FirstOrDefault()代替.First()

With regard to the dates average, you may have to drop that ordering for the moment as I am unable to get to an IDE at the moment

关于平均日期，您可能不得不暂时放弃该订单，因为我目前无法访问 IDE

var groupByReference = context.Measurements
                              .GroupBy(m => m.Reference)
                              .Select(g => new {Creation = g.FirstOrDefault().CreationTime, 
//                                              Avg = g.Average(m => m.CreationTime.Ticks),
                                                Items = g })
                              .OrderBy(x => x.Creation)
//                            .ThenBy(x => x.Avg)
                              .Take(numOfEntries)
                              .ToList();

回答by Moho

Your requirements are all over the place, but this is the solution to my understanding of them:

您的要求无处不在，但这是我对它们的理解的解决方案：

To group by Reference property:

按参考属性分组：

var refGroupQuery = (from m in context.Measurements
            group m by m.Reference into refGroup
            select refGroup);

Now you say you want to limit results by "most recent numOfEntries" - I take this to mean you want to limit the returned Measurements... in that case:

现在你说你想通过“最近的 numOfEntries”来限制结果 - 我认为这意味着你想限制返回的测量......在这种情况下：

var limitedQuery = from g in refGroupQuery
                   select new
                   {
                       Reference = g.Key,
                       RecentMeasurements = g.OrderByDescending( p => p.CreationTime ).Take( numOfEntries )
                   }

To order groups by first Measurement creation time (note you should order the measurements; if you want the earliest CreationTime value, substitue "g.SomeProperty" with "g.CreationTime"):

要按第一次测量创建时间对组进行排序（请注意，您应该对测量进行排序；如果您想要最早的 CreationTime 值，请将“g.SomeProperty”替换为“g.CreationTime”）：

var refGroupsOrderedByFirstCreationTimeQuery = limitedQuery.OrderBy( lq => lq.RecentMeasurements.OrderBy( g => g.SomeProperty ).First().CreationTime );

To order groups by average CreationTime, use the Ticks property of the DateTime struct:

要按平均 CreationTime 对组进行排序，请使用 DateTime 结构的 Ticks 属性：

var refGroupsOrderedByAvgCreationTimeQuery = limitedQuery.OrderBy( lq => lq.RecentMeasurements.Average( g => g.CreationTime.Ticks ) );

回答by user3373870

You can try to cast the result of GroupBy and Take into an Enumerable first then process the rest (building on the solution provided by NinjaNye

您可以尝试先将 GroupBy 和 Take 的结果转换为 Enumerable，然后处理其余部分（基于 NinjaNye 提供的解决方案

var groupByReference = (from m in context.Measurements
                              .GroupBy(m => m.Reference)
                              .Take(numOfEntries).AsEnumerable()
                               .Select(g => new {Creation = g.FirstOrDefault().CreationTime, 
                                             Avg = g.Average(m => m.CreationTime.Ticks),
                                                Items = g })
                              .OrderBy(x => x.Creation)
                              .ThenBy(x => x.Avg)
                              .ToList() select m);

Your sql query would look similar (depending on your input) this

您的 sql 查询看起来很相似（取决于您的输入）

SELECT TOP (3) [t1].[Reference] AS [Key]
FROM (
    SELECT [t0].[Reference]
    FROM [Measurements] AS [t0]
    GROUP BY [t0].[Reference]
    ) AS [t1]
GO

-- Region Parameters
DECLARE @x1 NVarChar(1000) = 'Ref1'
-- EndRegion
SELECT [t0].[CreationTime], [t0].[Id], [t0].[Reference]
FROM [Measurements] AS [t0]
WHERE @x1 = [t0].[Reference]
GO

-- Region Parameters
DECLARE @x1 NVarChar(1000) = 'Ref2'
-- EndRegion
SELECT [t0].[CreationTime], [t0].[Id], [t0].[Reference]
FROM [Measurements] AS [t0]
WHERE @x1 = [t0].[Reference]