CODEWITHSUNDEEP
sql-serverentity-frameworklinqentity-framework-4
user1464139
user1464139

Reputation:

How can I do a group by on rows with a day of month for each in the group?

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 a count of activity (rows) for a single UserId with a group by day.

Is this something that's possible to do with LINQ or would I need to resort to coding in SQL because of the need to somehow extract the day from the DATETIME field.

Some help and advice on this even if a SQL SELECT would be much appreciated.

Date          Count
---------------------    
29-Feb-2018   25
28-Feb-2018   33
27-Feb-2018   11
26-Feb-2018   44

Upvotes: 0

Views: 44

Answers (2)

Gert Arnold
Gert Arnold

Reputation: 109080

Assuming that your EF model looks like the table, you could use this LINQ statement:

from a in context.Audits
group a by EntityFunctions.TruncateTime(a.Date) into grp
select new
{
    Date = grp.Key,
    Count = grp.Count()
}

Since you tagged entity framework 4, you have to use EntityFunctions. This was changed to DbFunctions in later versions.

Upvotes: 0

Sean Lange
Sean Lange

Reputation: 33571

You should avoid using keywords as column or object names as it makes things really confusing. But using your example this is a pretty simple query.

select [Date] = convert(date, a.Date)
    , [Count] = count(*)
from Audit a
group by convert(date, a.Date)

Upvotes: 1

Related Questions