Group By with multiple column

Question

I have data in a table as below

RowId | User | Date 
--------------------------
  1     A     2015-11-11 08:50:48.243
  2     A     2015-11-11 08:51:01.433
  3     B     2015-11-11 08:51:05.210

Trying to get the data as below:

User, Date,        Count
  A   2015-11-11     2
  B   2015-11-11     1

 Select User,Date,Count(User) from Table1 
 Group By User,Date

It is returning me 3 rows because of time involved in Date field. How to get this in SQL and Linq. Please suggest me.

EDITING:

I am able to get it in SQL

 Select User,Cast(Date as Date),Count(User) from Table1 
 Group By User,Cast(Date as Date)    

EDITING:

adding linq query

var details = db.table1.GroupBy( r => new { r.RowId,r.User,r.Date})
.Select(g => new {Name = g.Key, Count = g.Count()}).ToList();

Answer 1

If the time is the problem, you can first convert it:

select User, CAST(dateColumn AS DATE) as dateConverted
into #tempTable
from myTable

then using a window function or a group by:

select *,
    count(user) over (partition by date) as userCount
    from #tempTable

This should work in SQL server, don't know about Linq

edit: If the date part is the problem, just select into from your table to a table with the casted date. Then you won't have this problem in Linq.

Answer 2

var grouped = from d in db.MyTable.AsEnumerable()
              group d by new {
      User = d.User, 
      Date=d.Date.HasValue ? d.Date.Value.Date : (DateTime?)null} into g
              select new {User=g.Key.User, Date=g.Key.Date, Count=g.Count()};

Sooner or later, someone would say that this is not server side grouping and would suffer from performance and they would be right. Without Enumerable it is serverside but at the cost of another call per group, so here is another way:

public class MyResult
{
  public string User {get;set;}
  public DateTime? Date {get;set;}
  public int Count {get;set;}
}

var grouped = db.ExecuteQuery<MyResult>(@"select [User], 
  Cast([Date] as Date) as [Date],
  Count(*) as [Count]
from myTable
group by [user], Cast([Date] as Date)");

EDIT: I don't know why I thought the other way before, this would just work serverside and do it, AsEnumerable() was not needed:

var grouped = from d in db.MyTable
              group d by new {
      User = d.User, 
      Date=d.Date.HasValue ? d.Date.Value.Date : (DateTime?)null} into g
  select new {User=g.Key.User, Date=g.Key.Date, Count=g.Count()};

Answer 3

For Linq Query just do the following: (you need to import using System.Data.Entity.SqlServer namespace.

Execute this linq query all calculations are done on the server database. Notice that Table1s represents the DbSet for Table1 and context is your DbContext instance.

var query = from item in context.Table1s
    group item by new
    {
       item.User, 
       Year = SqlFunctions.DatePart("yyyy", item.Date), 
       Month = SqlFunctions.DatePart("mm", item.Date), 
       Day = SqlFunctions.DatePart("dd", item.Date)
    } into g
    select new { g.Key.User, g.Key.Year, g.Key.Month, g.Key.Day, Count = g.Count() };

Then create the final result like this:

var result = query.ToList().Select(p =>
    new
    {
        p.User,
        Date = new DateTime(p.Year.Value, p.Month.Value, p.Day.Value),
        p.Count
    }).ToList();

Other solution is to create a SQL View that will be used by DbContext to retrive the data you want. The SQL View body must be the SQL your wrote in your question.

EDIT 2 : DbFunctions

Like Cetin Basoz pointed in comments we can use System.Data.Entity.DbFunctions as well. And the code is more cleaner than using SqlFunctions. This will work only with EF 6 and greater. The version using SqlFunctions work with EF 4 and greater.

var query = from item in context.Table1s
    group item by new
    {
        item.User, 
        Date = DbFunctions.TruncateTime(item.Date)
    } into g
    select new { g.Key.User, g.Key.Date, Count = g.Count() };

EDIT 1 : this is specific for Cetin Basoz's answer :

As we all know using AsEnumerable is not efficient for doing what is needed. The second solution he gives us which is :

var grouped = from d in db.MyTable
          group d by new {
  User = d.User, 
  Date=d.Date.HasValue ? d.Date.Value.Date : (DateTime?)null} into g
  select new {User=g.Key.User, Date=g.Key.Date, Count=g.Count()};

This solution just not work because of this :

The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.