LINQ Grouping by day. How to do it easily?

Question

I cannot find any good reference to this. I have a lot of data in SQL with dates. So I wanted to make a line chart to display this data over time. If I want to show this in a few days, I need to group the days ... But LOGDATE is the full date .. not DAY ..

So, I have this below, but LINQ does not 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
                };

Answer 1

You can use DbFunctions.TruncateTime :

to return the specified date with the cleared part of the time

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

Answer 2

, .Date DateTime not DayOfyear, .

Answer 3

:

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

:

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

I'm not sure about this, but it is possible to use an anonymous object like this in your proposal group by, which confuses L2S.

Answer 4

Here you go

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