0

I have this table DDL:

CREATE TABLE [dbo].[Audit] 
(
    [AuditId] INT          IDENTITY (1, 1) NOT NULL,
    [Entity]  INT          NOT NULL,
    [UserId]  INT          NOT NULL,
    [Note]    VARCHAR(200) NULL,
    [Date]    DATETIME     NOT NULL,
    [Action]  INT          NOT NULL,

    CONSTRAINT [PK_Audit] 
        PRIMARY KEY CLUSTERED ([AuditId] ASC)
);

What I would like to do is to get a report that shows something like this:

UserId  Action  Count
---------------------    
user1   Insert  25
user1   Update  30
User1   Delete  45

I have been using Entity Framework for my queries like this:

var result = db.Audits
              .Where(a => a.UserId == userId)
              .OrderByDescending(a => a.AuditId)
              .ToList();
return Ok(result);

Can someone tell me: is it possible to get the kind of report that I need with EF or do I need to resort to a SQL statement and then somehow get the output of that?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 2
    Have you tried to search for how to `group by` in Linq? ([for example](http://stackoverflow.com/questions/7325278/group-by-in-linq)) – DavidG Feb 27 '17 at 13:31

1 Answers1

1

You can use this 

Audits.Where(a => a.UserId == userId)
            .GroupBy(a => a.Action)
            .Select(a => new { UserId = userId, Action = a.Key, Count = a.Count() })
            .ToList();
Kahbazi
  • 14,331
  • 3
  • 45
  • 76