Return all row having a maximum value

Question

Hi!

I'm using entity framework to connect the database.

I've a table(Let's call it "File") that haves several fields:

ID, Version, XYZ Primarky key is based on ID AND Version. so I can have several line with the same ID but different version(and inversly).

The question is:

How can I, with a LAMBDA expression, ask my Entity Framework, to return me all last version of a "file".

Example: Datas:

 ID;Version;Other
 1;1;YX
 1;2;YZ
 2;1;AH
 2;2;BH
 2;5;CA
 1;3;AAA

Result:

 1;3;AAA
 2;5;CA

Thank you!

!! The goal is that the database doesn't need to return all rows, and is called only one time, so forget solution like GetAllRows and read the whole collection and save only the latest, or get a list of all possible ID and get the last version foreach in another request. Thanks!

Answer 1

You can use the following LinqToEntites query for this:

var result = from f in myEntities.Files
             group f by f.ID into g
             select g.OrderByDescending(f => f.Version).FirstOrDefault();

It would perhaps make more sense to use First instead of FirstOrDefault but then you get an UnsupportedException:

The method 'First' can only be used as a final query operation. Consider using the method 'FirstOrDefault' in this instance instead