Entity Framework - Linq query with order by and group by

c# entity-framework linq mysql


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

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

  • 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.)

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

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

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.


3/22/2013 7:46:15 PM

Popular Answer

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

Updated post comment

Use .FirstOrDefault() instead of .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

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)
3/22/2013 6:49:57 PM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow