score:31

Accepted answer

you do not need the second truncatetime in there:

context.users
    .where((x.lastlogin  >= lastweek) && (x.lastlogin <= datetime.now))
    .groupby(x => dbfunctions.truncatetime(x.lastlogin))
    .select(x => new
    {
        value = x.count(),
        // replace the commented line
        //day = (datetime)dbfunctions.truncatetime(x.key)
        // ...with this line
        day = (datetime)x.key
    }).tolist();

the groupby has truncated the time from the datetime already, so you do not need to call it again.

to use dbfunctions.truncatetime you'll need to reference the assembly system.data.entity and include using system.data.entity;

note: edited to address deprecation of entityfunctions.

score:1

you can also do it in one line.

var b = (from a in ctx.candidates select a)
            .groupby(x => x.createdtimestamp.date).select(g => new { date=(g.key).toshortdatestring(), count = g.count() });

score:1

i solved the grouping only by date in this way:

var result = _context.yourdbmodel
          .groupby(x=> dbfunctions.truncatetime(x.yourdatetimecolumn))
          .select(x=> new 
          {
            count = x.count(),
            date = (datetime)x.key 
          })
          .tolist();

to use dbfunctions.truncatetime you will need to reference the assembly system.data.entity and include using system.data.entity.

score:3

i came across this same problem to get a count based on group by a datetime column. this is what i did. thanks for some of the answers here. i tried the minimal version and i used this code in .netcore solution.

var result = _context.yourdbmodel
              .groupby(x=>x.yourdatetimecolumn.value.date)
              .select(x=> new 
              {
                count = x.count(),
                date = (datetime)x.key // or x.key.date (excluding time info) or x.key.date.tostring() (give only date in string format) 
              })
              .tolist();

sample output:

[ { "requestdate": "2020-04-01", "requestcount": 3 }, { "requestdate": "2020-04-07", "requestcount": 14 } ]

hope this helps someone in future.

score:6

try this:

 .groupby(x => new {year = x.lastlogin.year, month = x.lastlogin.month, day = x.lastlogin.day)
                .select(x => new
                {
                    value = x.count(),
                    year = x.key.year,
                    month = x.key.month,
                    day = x.key.day
                })

score:6

you can do it easily:

yourdatelist.groupby(i => i.tostring("yyyymmdd"))
             .select(i => new
             {
                 date = datetime.parseexact(i.key, "yyyymmdd", cultureinfo.invariantculture, datetimestyles.none),
                 count = i.count()
             });

Related Query

More Query from same tag