Linq, How to do groupBy?

project table:

Id, DeptId, Year, Name, Level

Id = 1, DeptId = 1, Year = 2000, Name = "ABC", Level = 1
Id = 2, DeptId = 1, Year = 2001, Name = "ABC1", Level = 1
Id = 3, DeptId = 1, Year = 2002, Name = "ABC2", Level = 1

Id = 4, DeptId = 2, Year = 2000, Name = "ABC3", Level = 1
Id = 5, DeptId = 2, Year = 2002, Name = "ABC4", Level = 1

Id = 6, DeptId = 3, Year = 2000, Name = "ABC5", Level = 1
Id = 7, DeptId = 3, Year = 2001, Name = "ABC6", Level = 1
Id = 8, DeptId = 3, Year = 2002, Name = "ABC7", Level = 1

I have a project table. I need to get the project for each dept for 2001. for DeptId = 2, it has no project for 2001, I need to show previous year project which is 2000.

my linq should return following result back.

Id = 2, DeptId = 1, Year = 2001, Name = "ABC1", Level = 1
Id = 4, DeptId = 2, Year = 2000, Name = "ABC3", Level = 1
Id = 7, DeptId = 3, Year = 2001, Name = "ABC6", Level = 1

I was thinking about using groupby, group the DeptId, but not sure how to write this query.

Update:

This is what I tried.

var projects = project.Where(x=>x.Year == 2001)

but this will not return result for DeptId = 2, that's why I am thinking about using groupby, but clueless how to write it.

回答1:

var query= from p in context.Projects
           group p by p.DeptId into grp
           select grp.Where(x => x.Year <= 2001)
                     .OrderByDescending(x => x.Year)
                     .FirstOrDefault();

回答2:

from l in context.Projects.Where(w => w.Year <= 2001 )
        group l by l.DeptId into depts
        select depts.LastOrDefault();

Lambda version:

    context.Projects.Where(w => w.Year <= 2001)
        .GroupBy(g => g.DeptId)
        .Select (s => s.LastOrDefault());

Or alternatively:

    context.Projects.Where(w => w.Year <= 2001)
        .GroupBy(g => g.DeptId)
        .Select (s => s.OrderBy(o => o.Year).LastOrDefault());