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

Posted by Kinderchocolate on Stack Overflow See other posts from Stack Overflow or by Kinderchocolate
Published on 2010-04-17T12:05:39Z Indexed on 2010/04/17 12:13 UTC
Read the original article Hit count: 146

Filed under:
|
|

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?

© Stack Overflow or respective owner

Related posts about LINQ

Related posts about linq-to-entities