How to use LINQ To Entities for filtering when many methods are not supported?

Question

Hi,

I have a table in SQL database:

ID  Data  Value
1    1     0.1
1    2     0.4
2    10    0.3
2    11    0.2
3    10    0.5
3    11    0.6

For each unique value in Data, I want to filter out the row with the largest ID. For example: In the table above, I want to filter out the third and fourth row because the fifth and sixth rows have the same Data values but their IDs (3) are larger (2 in the third and fourth row).

I tried this in Linq to Entities:

IQueryable<DerivedRate> test = ObjectContext.DerivedRates.OrderBy(d => d.Data).ThenBy(d => d.ID).SkipWhile((d, index) => (index == size - 1) || (d.ID != ObjectContext.DerivedRates.ElementAt(index + 1).ID));

Basically, I am sorting the list and removing the duplicates by checking if the next element has an identical ID.

However, this doesn't work because SkipWhile(index) and ElementAt(index) aren't supported in Linq to Entities. I don't want to pull the entire gigantic table into an array before sorting it. Is there a way?

Answer 1

You can use the GroupBy and Max function for that.

IQueryable<DerivedRate> test = (from d in ObjectContext.DerivedRates
                             let grouped = ObjectContext.DerivedRates.GroupBy(dr => dr.Data).First()
                             where d.Data == grouped.Key && d.ID == grouped.Max(dg => dg.ID)
                             orderby d.Data
                             select d);

Answer 2

Femaref's solution is interesting, unfortunately, it doesn't work because an exception is thrown whenever "ObjectContext.DerivedRates.GroupBy(dr => dr.Data).First()" is executed.

His idea has inspired me for another solution, something like this:

    var query = from d in ObjectContext.ProviderRates
                where d.ValueDate == valueDate && d.RevisionID <= valueDateRevision.RevisionID
                group d by d.RateDefID into g
                select g.OrderByDescending(dd => dd.RevisionID).FirstOrDefault();

Now this works.