CODEWITHSUNDEEP
linqentity-frameworkdategrouping
punkouter
punkouter

Reputation: 5356

LINQ. Grouping by days. How to do this easily?

I can't seem to find any good reference on this. I have a lot of data in SQL with dates. So I wanted to make a line chart to show this data over time. If I want to show it over a period of days then I need to group by days.. But the LOGDATE is the full date.. not the DAY..

So I have this below, but LINQ doesn't know what 'DayOfYear' property is...

 var q = from x in dc.ApplicationLogs
                let dt = x.LogDate
                group x by new { dayofyear = dt.Value.DayOfYear } into g
                select new
                {
                    iCount = g.Count(),
                    strDate = g.Key
                };

Upvotes: 5

Views: 8938

Answers (4)

Gert Arnold
Gert Arnold

Reputation: 109109

In EF core you can use DateTime.Date to get the date portion of a DateTime value.

In EF6 you can use DbFunctions.TruncateTime:

to return the given date with the time portion cleared

var q = from x in dc.ApplicationLogs
        let dt = x.LogDate.Date
        // EF6:let dt = DbFunctions.TruncateTime(x.LogDate)
        group x by dt into g
        select new
        {
            iCount = g.Count(),
            strDate = g.Key
        };

Upvotes: 5

Marwan Roushdy
Marwan Roushdy

Reputation: 1230

Here you go

let d = new DateTime(n.time.Year, n.time.Month, n.time.Day) group n by d into grp select grp

Upvotes: 0

Ian Mercer
Ian Mercer

Reputation: 39277

You want .Date to get the date part of a DateTime not DayOfyear unless you are deliberately trying to put the same day of the year from each year into the group.

Upvotes: 3

tzaman
tzaman

Reputation: 47790

Why are you using:

    let dt = x.LogDate
    group x by new { dayofyear = dt.Value.DayOfYear } into g

instead of just:

    group x by x.LogDate.Value.DayOfYear into g

I'm not sure about this, but it's possible using an anonymous object like that in your group by clause is messing up L2S.

Upvotes: 1

Related Questions