Linq - Grouping by date and selecting count

Question

I am currently working through a problem where I would like to run a query which groups the results by the date selected.

For this example, imagine a simple model like so:

public class User
{
      public DateTime LastLogIn {get; set;}
      public string Name {get; set;}
}

The solution I am looking for is to get a count of Users logged in by Date. In the database the DateTime are stored with both date and time components, but for this query I really only care about the date.

What I currently have is 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 however returns an empty list.

End goal is to have a List of objects, which contain a Value (the count of users logged in on a day) and a Day (the day in question)

Any thoughts?

Upon changing the 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 now returns a list with a single item, with the Value being the total count of Users that match the where clause, and the Day being the very first day. It still hasn't seemed to be able to group by the days

NOTE: turns out the above code is right, I was just doing something else wrong.

Sql that it is generating is (note might be very slight syntactical errors here with me adjusting it for the example):

SELECT 
1 AS [C1], 
[GroupBy1].[A1] AS [C2], 
 CAST( [GroupBy1].[K1] AS datetime2) AS [C3]
FROM ( SELECT 
        [Filter1].[K1] AS [K1], 
        COUNT([Filter1].[A1]) AS [A1]
        FROM ( SELECT 
                 convert (datetime2, convert(varchar(255), [Extent1].[LastLogIn], 102) ,  102) AS [K1], 
                1 AS [A1]
                FROM [dbo].[Users] AS [Extent1]
                WHERE (([Extent1].[LastLogIn] >= @p__linq__1) AND ([Extent1].[LastLogIn] <= @p__linq__2)
        )  AS [Filter1]
       GROUP BY [K1]
)  AS [GroupBy1] 

Answer 1

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.

Answer 2

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
                })

Answer 3

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()
             });