CODEWITHSUNDEEP
c#mysqllinqentity-framework
zufallsfund
zufallsfund

Reputation: 151

Entity Framework - Linq query with order by and group by

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

(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.

Thanks!

Upvotes: 15

Views: 112546

Answers (4)

user3373870
user3373870

Reputation: 1466

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 

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

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]

Upvotes: 2

Moho
Moho

Reputation: 16498

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:

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"):

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:

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

Upvotes: 0

NinjaNye
NinjaNye

Reputation: 7126

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)
                              .Take(numOfEntries)
                              .ToList();

Upvotes: 15

MarcinJuraszek
MarcinJuraszek

Reputation: 125630

Try moving the order by after 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();

Upvotes: 1

Related Questions