CODEWITHSUNDEEP
linqc#-4.0linq-to-entities
djdd87
djdd87

Reputation: 68476

What's the LINQ to select the latest item from a number of versioned items?

I've got a class like the following:

public class Invoice
{
    public int InvoiceId {get;set;}
    public int VersionId {get;set;}
}

Each time an Invoice is modified, the VersionId gets incremented, but the InvoiceId remains the same. So given an IEnumerable<Invoice> which has the following results:

InvoiceId VersionId
1         1
1         2
1         3
2         1
2         2

How can I get just the results:

InvoiceId VersionId
1         3
2         2

I.e. I want just the Invoices from the results which have the latest VersionId. I can easily do this in T-SQL, but cannot for the life of me work out the correct LINQ syntax. I'm using Entity Framework 4 Code First.

Upvotes: 3

Views: 1063

Answers (3)

Sorax
Sorax

Reputation: 2203

My version:

var h = from i in Invoices
        group i.VersionId by i.InvoiceId into grouping
        select new {InvoiceId = grouping.Key, VersionId = grouping.Max()};

Update

As was mentioned by Ahmad in the comments, the above query will return a projection. The version below will return a IQueryable<Invoice>. I use composition to build the query because I think it is more clear.

var maxVersions = from i in Invoices
                  group i.VersionId by i.InvoiceId into grouping
                  select new {InvoiceId = grouping.Key, 
                              VersionId = grouping.Max()};

var latestInvoices =  from i in Invoices
                      join m in maxVersions 
                      on new {i.InvoiceId, i.VersionId} equals 
                         new {m.InvoiceId, m.VersionId}
                      select i;

Upvotes: 1

Ani
Ani

Reputation: 113412

EDIT: I've tested both these queries with LINQ to Entities. They seem to work, so perhaps the issue is something else?

Option 1:

var latestInvoices = invoices.GroupBy(i => i.InvoiceId)
                             .Select(group => group.OrderByDescending(i => i.VersionId)
                                                   .FirstOrDefault());

EDIT: Changed 'Last' to 'FirstOrDefault', LINQ to Entities has issues with the 'Last' query operator.

Option 2:

var invoices = from invoice in dc.Invoices
               group invoice by invoice.InvoiceId into invoiceGroup
               let maxVersion = invoiceGroup.Max(i => i.VersionId)
               from candidate in invoiceGroup
               where candidate.VersionId == maxVersion
               select candidate;

Upvotes: 1

Ahmad Mageed
Ahmad Mageed

Reputation: 96497

Order by the VersionId, group them by InvoiceId, then take the first result of each group. Try this:

var query = list.OrderByDescending(i => i.VersionId)
                .GroupBy(i => i.InvoiceId)
                .Select(g => g.First());

EDIT: how about this approach using Max?

var query = list.GroupBy(i => i.InvoiceId)
                .Select(g => g.Single(i => i.VersionId == g.Max(o => o.VersionId)));

Try using FirstOrDefault or SingleOrDefault in place of Single as well... it would give the same result although Single shows the intention better.

Upvotes: 1

Related Questions