CODEWITHSUNDEEP
c#sql-serverentity-frameworklinq
free_to_fly_wi
free_to_fly_wi

Reputation: 29

Select last records in sorted groups

I have a result from a linq query which is a join between two SQL tables

var result1 = db.TABLE1.Join(
    db.TABLE2, 
    p1 => p1.ID, 
    p2 => p2.ID, 
    (p1, p2) => new 
    { 
        Id = p1.ID, 
        Date = p2.DATE, 
        Prop1 = p1.Prop1, 
        Prop2 = p2.Prop2, 
        Prop3 = p2.Prop3, 
        Prop4 = p2.Prop4
    });

And then I attempt to group the result by Id and select the rows with the latest dates per Id

var result2 = result1.GroupBy(p => p.Id)
    .Select(q => q.OrderBy(p => p.Date).LastOrDefault())
    .ToList();

But this fails with a

LINQ to Entities does not recognize the method '<>f__AnonymousTypee'6[System.Decimal,System.Double,System.Nullable'1[System.Decimal],System.Nullable'1[System.Decimal],System.String,System.String] LastOrDefault<>f__AnonymousTypee'6' method, and this method cannot be translated into a store expression." error.

Can anyone help me figure out what I'm doing wrong?

Upvotes: 0

Views: 54

Answers (1)

Habib
Habib

Reputation: 223402

You can't use Last/LastOrDefault in LINQ to entities queries, since there is no available translation to under laying data source language (SQL). Instead sort the records by descending order and then get the first or default like:

q.OrderByDescending(p => p.Date).FirstOrDefault()

Upvotes: 2

Related Questions