CODEWITHSUNDEEP
c#entity-frameworklinq
Thewads
Thewads

Reputation: 5053

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]

Upvotes: 36

Views: 54164

Answers (6)

Kartik Pareek
Kartik Pareek

Reputation: 23

Just convert first IQueryable to IEnumerable(List) with the help of ToList() then use groupby

context.Users
            .Where((x.LastLogIn  >= lastWeek) && (x.LastLogIn <= DateTime.Now))
            .ToList()
            .GroupBy(x => x.LastLogIn.Date))
            .Select(x => new
            {
                Value = x.Count(),
                Day = (DateTime)x.Key
            }).ToList();

Upvotes: -1

Jabez
Jabez

Reputation: 905

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.

Upvotes: 5

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726479

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.

Upvotes: 34

Yahiya Iqbal
Yahiya Iqbal

Reputation: 21

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

Upvotes: 1

SeyedPooya Soofbaf
SeyedPooya Soofbaf

Reputation: 2649

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

Upvotes: 6

AD.Net
AD.Net

Reputation: 13399

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

Upvotes: 6

Related Questions