Return all row having a maximum value

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!
LVL 1
NargzulAsked:
Who is Participating?
 
Carl TawnConnect With a Mentor Systems and Integration DeveloperCommented:
You could try something like:
SampleDBEntities db = new SampleDBEntities();

var files = from file in db.File
            group file by file.FileID into grouped
            from g in grouped
            where g.Version == grouped.Max(m => m.Version)
            select g;

Open in new window

0
 
rfportillaCommented:
0
 
NargzulAuthor Commented:
I'm sorry but I don't understand. doing a Top/limit mean that all my row I want to get are on the top.

but I can't order them correctly, because, if I order by version the previous example and make a top(2) on this example datas:
ID;Version;Other
1;1;YX
1;2;YZ
2;1;AH
2;2;BH
2;4;CA
2;5;CA
1;3;AAA

I will get two results for the "ID" 2.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.