Linq to SQL: GroupBy() and Max() to get set of data

Question

Consider a table with the following schema

Table Schema

I would like to fetch data from each Status where category is max

1. In '1' Status, max category is 2, so fetch all rows of category 2
2. In '2' Status, max category is null, so fetch all rows of category null

Expected outcome

I need help in creating a linq query to fetch the above data.

I tried a few ways but couldnt get what I wanted. Below is one of the example.

var tableData = (from d in x.Data
                 group d by d.Status into grp
                 select new { Status = grp.Key, Category = grp.Max(d => d.Category) }).ToList();

Answer 1

You're close, your current code selects all the statues & their max categories. Now you just need to rejoin on that

var maxCategories = (from d in x.Data
                 group d by d.Status into grp
                 select new { Status = grp.Key, Category = grp.Max(d => d.Category) });

var tableData = (from d in x.Data
                         join mc in maxCategories on new { d.Status, d.Category } equals new { mc.Status, mc.Category }
                         select new { Id = d.Id, Status = d.Status, Category = d.Category })
                        .ToList();