实体框架 - Linq查询与order by和group by

问题描述

我有测量具有相关属性的对象 CreationTime （DateTime）和参考（String）和一些其他的值。

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

我想编写一个高效的linq查询到一个 DbContext

I'd like to write an efficient linq query to a DbContext that

• 通过给定的<$>分组我的测量 c $ c>参考


• 通过 CreationTime 第一个测量从 CreationTime 属性


• 的平均值将查询限制为最近的 numOfEntries

• groups my Measurement objects by a given Reference
• orders the groups by either the CreationTime Property of the first Measurement from the group or the average of the CreationTime properties
• limits the query to the most recent numOfEntries

（在后面的一步中，我计算这些返回的组，但我猜这与我的问题无关。）

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

DbContext本身有一个 DbSet< Measurement> 调用测量持有所有测量对象。

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

我想出了以下几点查询，导致列表中正确排序但缺少一些组之间的组列表。

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();

如何选择 Measurement s正确？

我正在使用带有MySQL数据库的实体框架4.4（MySql Connector / Net 6.6.4）。这个例子是简化的，如果需要，我可以进一步详细说明和/或给出一个例子。

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.

谢谢！

推荐答案

它的方法语法（我觉得更容易阅读），但这可能会做到这一点

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

/ strong>

Updated post comment

使用 .FirstOrDefault（）而不是 .First（） code>

Use .FirstOrDefault() instead of .First()

关于日期平均值，您可能必须暂停该订单，因为我无法到达IDE， / p>



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

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();

这篇关于实体框架 - Linq查询与order by和group by的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！