c# - Linq - Grouping by date and selecting count

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]

Thewads

출처 c# linq entity-framework linq-to-sql

The query looks OK. Do you get a non-zero value back when you replace GroupBy with a straight Count()? - dasblinkenlight
Running with a straight count returns the count expected that matches the where clause, it seems to be the grouping that is killing it - Thewads
Did you try replacing Day = (DateTime)EntityFunctions.TruncateTime(x.Key) with Day = x.Key? It should be the same, because the date is already truncated for grouping. - dasblinkenlight
Ok, removing that has changed some things, it now returns a list with a single item. Will update question with detail - Thewads
On the outside, this looks like a bug in the EF. Did you try capturing SQL and running it in your DB directly to see what's happening? - dasblinkenlight

4 답변

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.

.GroupBy(x => EntityFunctions.TruncateTime(x.LastLogIn)) is important factor here for considering datewise grouping. - sandeep talabathula
@dasblinkenlight Hi, what change do I have to make in your solution to include the days that have count = 0? I need to return all the days in the timeframe even if there are no logins. Thanks. - Patrick
@Patrick I don't think you can do it: LINQ will not return a result unless it in the database. You can convert the results to a dictionary, and then go through the range of dates, one day at a time, and grab the data from the dictionary if it is available. - dasblinkenlight
@dasblinkenlight oohhh :( Ok, thank you. - Patrick

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

hey, this is in the same situation as described above, with it returning a list with a single item which has a count of all the items - Thewads
May be working (although somehow nasty), but why the original query doesn't work? - Alireza

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

It "seems" less valid. But I've used this approach many many many times with fantastic success. Especially coming from the standpoint that "yyyyMMdd" is the universally accepted date format for SQL Server when passing data through. - pimbrouwers

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

Linq - Grouping by date and selecting count

4 답변

Related

Latest