Multiple Group By and Count in LINQ Entity Framework

May 3, 2023

I am facing one simple question here and since I am new in LINQ, I really need someone help.

I have one SQL Query to Sum Amount based on Client ID, CommissionName and Date only from a DateTime. Here is the SQL Query :

SELECT ClientId, CommisionName , CAST(Date AS DATE) DateOnly , sum(Amount)
  FROM [MyDB].[dbo].[Commission] group by ClientId, CAST(Date AS DATE), CommisionName

What I got for Grouping in LINQ is :

 var data = from c in await _context.Commission.ToListAsync() 
                   group c by new
                   {
                       c.CommisionName,
                       c.ClientId,
                       c.Date,
                   } into gcs
                   select new
                   {
                       ClientId = gcs.Key.ClientId,
                       CommissionName = gcs.Key.CommisionName,
                       Date = gcs.Key.Date,
                   };

But I am still dont have reference if I need to summarize the Amount.

Please I need someone reference.. Thank you

>Solution :

With gcs.Sum(x => x.Amount) to perform the sum of the Amount.

Note that your current query will query all the records and perform group on the memory side.

To perform the query with group on the database side, the LINQ query should be below:

var data = await (from c in _context.Commission 
                   group c by new
                   {
                       c.CommisionName,
                       c.ClientId,
                       c.Date.Date,
                   } into gcs
                   select new
                   {
                       ClientId = gcs.Key.ClientId,
                       CommissionName = gcs.Key.CommisionName,
                       Date = gcs.Key.Date,
                       Amount = gcs.Sum(x => x.Amount)
                   }
                   ).ToListAsync();