LINQ: Select the Min and Max values from a collection on an entity after grouping

.net-core c# entity-framework-core linq


The goal is to get the first DateTime and Last DateTime from a collection on an Entity (Foreign Key). My Entity is an organization and my collection are Invoices. I'm grouping results since Organizations unfortunately are not Unique. I'm dealing with duplicate data and cannot assume my organizations are unique so I'm grouping by a Number field on my Entity.

I'm using .NET Core 2.1.2 with Entity Framework.

I'm trying to get the following query generated from LINQ:

SELECT MIN([organization].[Id]) AS Id, MIN([organization].[Name]) AS Name, 
MIN([organization].[Number]) AS Number, MIN([invoice].[Date]) 
AS First, MAX([invoice].[Date]) AS Last
FROM [organization]
INNER JOIN [invoice] ON [invoice].[OrganizationId] = [organization].[Id]
GROUP BY [organization].[Number], [organization].[Name]
ORDER BY [organization].[Name]

However I have no idea how to get to write the LINQ query to get it to generate this result.

I got as far as:

await _context
            .Where(z => z.Invoices.Any())
            .GroupBy(organization => new
            .Select(grouping => new
                Id = grouping.Min(organization => organization.Id),
                Name = grouping.Min(organization => organization.Name),
                Number= grouping.Min(organization => organization.Number),
                //First = ?,
                //Last = ?
            .OrderBy(z => z.Name)

I have no clue how to write the LINQ query in such a way that it generates the above.

I have a couple questions still:

  • Are the Min statements for Id, Name and Number correct ways of getting the first element in the grouping?
  • Do I need a join statement or is "WHERE EXISTS" better (this got generated before I changed the code)?

Does anyone know how to finish writing the LINQ statement? Because I have to get the first and last Date from the Invoices Collection on my Organization Entity:

organization.Invoices.Min(invoice => invoice.Date)
organization.Invoices.Max(invoice => invoice.Date)
8/10/2018 4:09:27 PM

Accepted Answer

Here is the trick.

To make inner join by using collection navigation property simple use SelectMany and project all primitive properties that you need later (this is important for the current EF Core query translator). Then perform the GroupBy and project the key properties / aggregates. Finally do the ordering.


var query = _context
    .SelectMany(organization => organization.Invoices, (organization, invoice) => new
    .GroupBy(e => new
    .Select(g => new
        Id = g.Min(e => e.Id),
        Name = g.Key.Name,
        Number = g.Key.Number,
        First = g.Min(e => e.Date),
        Last = g.Max(e => e.Date),
    .OrderBy(e => e.Name);

is translated to

SELECT MIN([organization].[Id]) AS [Id], [organization].[Name], [organization].[Number], 
    MIN([organization.Invoice].[Date]) AS [First], MAX([organization.Invoice].[Date]) AS [Last]
FROM [Organization] AS [organization]
INNER JOIN [Invoice] AS [organization.Invoice] ON [organization].[Id] = [organization.Invoice].[OrganizationId]
GROUP BY [organization].[Number], [organization].[Name]
ORDER BY [organization].[Name]
8/10/2018 4:24:26 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