How to get the count of values in table using linq-CodePudding

im required to make a query to db to fetch data fro a highchart widget in our site here is the code I use currently,

var highChartsData = 
    (from a in db.roomdetails
    join b in db.ApplySchedule on a.RoomId equals b.RoomID
    where b.Status == true
    select new HighlinePie
    {
        Title = a.RoomName,
        Date = b.MDate,
        Value = db.ApplySchedule.Where(x => x.RoomID == a.RoomId).GroupBy(x=>x.MDate).Count(),
}).ToList();

The problem with this approach is right now get the total count but what i need is the count based on date, for example if there was two entry on date 12/09/20201 and three entry on 14/09/20201 the data should be "Title,12/09/20201,2","Title,14/09/20201,3".

CodePudding user response：

You have to use grouping:

var groupQuery = 
    from a in db.roomdetails
    join b in db.ApplySchedule on a.RoomId equals b.RoomID
    where b.Status == true
    group b by new { a.RoomName, b.MDate } into g
    select new HighlinePie
    {
        Title = g.Key.RoomName,
        Date = g.Key.MDate,
        Value = g.Count()
    };

var highChartsData = groupQuery.ToList();