CODEWITHSUNDEEP
sqlentity-framework
Marcin1199
Marcin1199

Reputation: 83

Entity framework select rows with maximum value for one column

I have table with about 20 columns. I want to get rows with all columns that have maximum "version" column for each ID.

ID      | Version | Other data
--------+---------+----------
1       | 1       | text1
1       | 2       | text2
1       | 3       | text3
2       | 1       | text1
3       | 1       | text1
3       | 2       | text2

What I want is:

ID      | Version | Other data
--------+---------+----------
1       | 3       | text3
2       | 1       | text1
3       | 2       | text2

I know how to achieve it in sql. I don't know how to achieve this on entity framework. Especially if I have 20 columns.

Upvotes: 4

Views: 3418

Answers (2)

Marcin1199
Marcin1199

Reputation: 83

I created my own version but it is slow

context.TableName.GroupBy(group=> group.recipeID)
                        .SelectMany(
                            group=>
                                group.Where(
                                    r=> r.version == group.Max(x => x.version))
                                    .Select(r=> r));

Upvotes: 0

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

context.TableName
    .GroupBy(x=>x.ID)
    .Select(x=>new 
    {
        ID = x.Key, 
        row = x.Where(r=>r.Version == x.Max(m=>m.Version)).FirstOrDefault()})
    .Select(x=>new {x.ID, x.row.Version, x.row.OtherData});

Upvotes: 1

Related Questions