c# - Linq - Grouping by date and selecting count -

i working through problem run query groups results date selected.

for example, imagine simple model so:

public class user {       public datetime lastlogin {get; set;}       public string name {get; set;} } 

the solution looking count of users logged in date. in database datetime stored both date , time components, query care date.

what have this:

    context.users             .where((x.lastlogin  >= lastweek)                     && (x.lastlogin <= datetime.now))             .groupby(x => entityfunctions.truncatetime(x.lastlogin))             .select(x => new             {                 value = x.count(),                 day = (datetime)entityfunctions.truncatetime(x.key)             }).tolist(); 

the above returns empty list.

end goal have list of objects, contain value (the count of users logged in on day) , day (the day in question)

any thoughts?

upon changing query to:

    context.users             .where((x.lastlogin  >= lastweek)                     && (x.lastlogin <= datetime.now))             .groupby(x => entityfunctions.truncatetime(x.lastlogin))             .select(x => new             {                 value = x.count(),                 day = (datetime)x.key             }).tolist(); 

it returns list single item, value being total count of users match clause, , day being first day. still hasn't seemed able group days

note: turns out above code right, doing else wrong.

sql generating (note might slight syntactical errors here me adjusting example):

select  1 [c1],  [groupby1].[a1] [c2],   cast( [groupby1].[k1] datetime2) [c3] ( select          [filter1].[k1] [k1],          count([filter1].[a1]) [a1]         ( select                   convert (datetime2, convert(varchar(255), [extent1].[lastlogin], 102) ,  102) [k1],                  1 [a1]                 [dbo].[users] [extent1]                 (([extent1].[lastlogin] >= @p__linq__1) , ([extent1].[lastlogin] <= @p__linq__2)         )  [filter1]        group [k1] )  [groupby1]  

you not need second truncatetime in there:

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

the groupby has truncated time datetime already, not need call again.

to use entityfunctions.truncatetime you'll need reference assembly system.data.entity , include using system.data.objects;