LINQ group by expression syntax

Question

I've got a T-SQL query similar to this:

SELECT r_id, r_name, count(*) FROM RoomBindings GROUP BY r_id, r_name 

I would like to do the same using LINQ. So far I got here:

var rooms = from roomBinding in DALManager.Context.RoomBindings                         group roomBinding by roomBinding.R_ID into g                         select new { ID = g.Key }; 

How can I extract the count(*) and r_name part?

Answer 1

Try this:

var rooms = from roomBinding in DALManager.Context.RoomBindings                         group roomBinding by new                          {                             Id = roomBinding.R_ID,                             Name = roomBinding.r_name                         }                         into g                         select new                          {                             Id = g.Key.Id,                            Name = g.Key.Name,                            Count = g.Count()                           }; 

Edit by Nick - Added method chain syntax for comparison

var rooms = roomBinding.GroupBy(g => new { Id = g.R_ID, Name = g.r_name })                        .Select(g => new                            {                                Id = g.Key.Id,                                Name = g.Key.Name,                                Count = g.Count()                            });