我有 Measurement
具有相关属性的对象 CreationTime
(日期时间)和 Reference
(字符串)和一些其他值。
我想写一个有效的linq查询到 DbContext
那
- 我的团体
Measurement
给定的对象 Reference
- 通过任何一个命令组
CreationTime
第一个属性 Measurement
来自小组或平均水平 CreationTime
性能
- 将查询限制为最新
numOfEntries
(在后面的步骤中,我计算这些返回组的平均值,但我猜这与我的问题无关。)
DbContext本身有一个 DbSet<Measurement>
叫 Measurements
抱着一切 Measurement
对象。
我提出了以下查询,这会产生一个正确排序但缺少某些组的列表。
var groupByReference = (from m in context.Measurements
orderby m.CreationTime
group m by new { m.Reference } into g
select g).Take(numOfEntries).ToList();
如何选择“最近的”组 Measurement
正确吗?
我正在使用带有MySQL数据库的实体框架4.4(MySql Connector / Net 6.6.4)。这个例子是简化的,我可以进一步详细说明和/或在必要时给出一个例子。
谢谢!
这是方法语法(我觉得更容易阅读),但这可能会这样做
更新了帖子评论
使用 .FirstOrDefault()
代替 .First()
关于日期平均值,您可能不得不暂时删除该顺序,因为此刻我无法访问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();
您可以先尝试将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);
您的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]
尝试移动 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();
您的要求到处都是,但这是我理解它们的解决方案:
要按引用属性分组:
var refGroupQuery = (from m in context.Measurements
group m by m.Reference into refGroup
select refGroup);
现在你说你想用“最近的numOfEntries”限制结果 - 我认为你想要限制返回的测量值......在这种情况下:
var limitedQuery = from g in refGroupQuery
select new
{
Reference = g.Key,
RecentMeasurements = g.OrderByDescending( p => p.CreationTime ).Take( numOfEntries )
}
要通过第一次测量创建时间来订购组(请注意您应该对测量进行排序;如果您想要最早的CreationTime值,请将“g.SomeProperty”替换为“g.CreationTime”):
var refGroupsOrderedByFirstCreationTimeQuery = limitedQuery.OrderBy( lq => lq.RecentMeasurements.OrderBy( g => g.SomeProperty ).First().CreationTime );
要按平均CreationTime对组进行排序,请使用DateTime结构的Ticks属性:
var refGroupsOrderedByAvgCreationTimeQuery = limitedQuery.OrderBy( lq => lq.RecentMeasurements.Average( g => g.CreationTime.Ticks ) );