Convert SQL query with multiple GroupBy columns to LINQ

1
votes
SELECT 
  [TimeStampDate]
  ,[User]
  ,count(*) as [Usage]
FROM [EFDP_Dev].[Admin].[AuditLog]
WHERE [target] = '995fc819-954a-49af-b056-387e11a8875d'
GROUP BY [Target], [User] ,[TimeStampDate]
ORDER BY [Target]

My database table has the columns User, TimeStampDate, and Target (which is a GUID).

I want to retrieve all items for each date for each user and display count of entries.

The above SQL query works. How can I convert it into LINQ to SQL? Am using EF 6.1 and my entity class in C# has all the above columns.

Create Filter basically returns an IQueryable of the entire AuditLogSet :

using (var filter = auditLogRepository.CreateFilter())
{
  var query = filter.All
    .Where(it => it.Target == '995fc819-954a-49af-b056-387e11a8875d')
    .GroupBy(i => i.Target, i => i.User, i => i.TimeStamp);
  audits = query.ToList();
}

Am not being allowed to group by on 3 columns in LINQ and I am also not sure how to select like the above SQL query with count. Fairly new to LINQ.

4
c#sql-serverentity-frameworklinqgroup-by
codeducky.org/sql-queries-in-linq may help. - Steven Wexler
kudvenkat LINQ Tutorial - MrDywar
As discussed below, because you are already filtering the results down to a single target, specifying target in the groupby and ordering based on it is useless. You should also consider not forcing the audits into a list, but letting them remain an IQueryable and/or IEnumerable as long as possible. Doing so would allow possible further processing (or shifting further processing to the database server) and allow the records to be processed as they are retrieved which increases throughput, lowers memory usage, and lowers garbage collection. - Robert McKee

4 Answers

4
votes

You need to specify the group by columns in an anonymous type like this:-

var query = filter.All
                  .Where(it => it.Target == '995fc819-954a-49af-b056-387e11a8875d')
                  .GroupBy(x => new { x.User, x.TimeStampDate })
                  .Select(x => new 
                         {
                             TimeStampDate= x.Key.TimeStampDate,
                             User = x.Key.User,
                             Usage = x.Count()
                         }).ToList();
3
votes

Many people find query syntax simpler and easier to read (this might not be the case, I don't know), here's the query syntax version anyway.

var res=(from it in filter.All
    where it.Target=="995fc819-954a-49af-b056-387e11a8875d"
    group it by new {it.Target, it.User, it.TimeStampDate} into g
    orderby g.Key.Target
    select new 
               {
                TimeStampDate= g.Key.TimeStampDate,
                User=g.Key.User,
                Usage=g.Count()
               });

EDIT: By the way you don't need to group by Target neither OrderBy, since is already filtered, I'm leaving the exact translation of the query though.

2
votes

To use GroupBy you need to create an anonymous object like this:

filter.All
      .Where(it => it.Target == '995fc819-954a-49af-b056-387e11a8875d')
      .GroupBy(i => new { i.Target, i.User, i.TimeStamp });
2
votes

It is unnecessary to group by target in your original SQL.

filter.All.Where( d => d.Target == "995fc819-954a-49af-b056-387e11a8875d")
  .GroupBy(d => new {d.User ,d.TimeStampDate} )
  .Select(d => new {
    User = d.Key.User,
    TimeStampDate = d.Key.TimeStampDate,
    Usage = d.Count()
  } );