Lambda : GroupBy multiple columns where one of them is DbFunctions.TruncateTime()

Question

NOTE : This is NOT a duplicate of this useful SO question, my problem is all about the TruncateTime inside the GroupBy clause. See explanations below :

I'd like to use DbFunctions.TruncateTime in a multiple GroupBy clause, but it doesn't seem to work in my ASP.NET MVC5 project.

Here is a first lambda I wrote, it gives me the total number of views per day for a set of data.
It gives me the expected result :

var qViews = dbContext.TABLE_C.Where(c => c.IdUser == 1234)
            .Join(dbContext.TABLE_V.Where(v => v.Date > DbFunctions.AddMonths(DateTime.Now, -1)), c => c.Id, v => v.Id, (c, v) => new { c, v })
            .GroupBy(x => DbFunctions.TruncateTime(x.v.MyDateTimeColumn))
            .Select(g => new
            {
                Date = (DateTime)g.Key,
                NbViews = g.Count(),
            }).ToDictionary(p => p.Date, p => p.NbViews);

Result is something like that :

...
Date            | Views
03/07/2018  |   15
03/08/2018  |   8
03/09/2018  |   23

Now, I'd like a more detailled result, with the number of views per day AND PER ITEM on the same set of data.

Here is what I'd like to write :

var qViews = dbContext.TABLE_C.Where(c => c.IdUser == 1234)
            .Join(dbContext.TABLE_V.Where(v => v.Date > DbFunctions.AddMonths(DateTime.Now, -1)), c => c.Id, v => v.Id, (c, v) => new { c, v })
            .GroupBy(x => new { DbFunctions.TruncateTime(x.v.MyDateTimeColumn), x.c.Id})  // Issue #1
            .Select(g => new
            {
                Date = g.Key.Date,  //Issue #2
                NbViews = g.Count(),
            }).ToDictionary(p => p.Date, p => p.NbViews);

And I expected something like that :

...
Date            | Views | ID Item
03/07/2018  |   4      | 456789
03/07/2018  |   11    | 845674
03/08/2018  |   6      | 325987
03/08/2018  |   1      | 548965
03/08/2018  |   1      | 222695
03/09/2018  |   23    | 157896

So, this request have two issues (see comments above)

Issue #1 : It seems I can't GroupBy multiple columns, which one of them use DbFunctions. If I use .GroupBy(x => new { x.v.MyDateTimeColumn, x.c.Id }), code compiles, but doesn't give me the expected result, as I want to group by date, not date + time

Issue #2 : Date = g.Key.Date, seems wrong for the compiler. When I wrote g.Key, autocompletion only suggests me the Id column, but it doesn't see the truncated date.

Why can't I GroupBy multiple columns, with one of them is a truncated Date ?
Is there any workaround ?

Answer 1

You need to give your anonymous type's properties names if you want to use them later on:

.GroupBy(x => new 
              { Date = DbFunctions.TruncateTime(x.v.MyDateTimeColumn),
                Id = x.c.Id
              }) 

Then you can project on that:

.Select(g => new
{
    Date = g.Date,  
    NbViews = g.Count(),
})

And finally you cannot do this:

.ToDictionary(p => p.Date, p => p.NbViews);

because you will get this error:

An item with the same key has already been added.

Why? Because the Date is not unique since you just grouped by Date and Id so Date(s) will be duplicated. It is the same as this but this is a list of string:

var nums = new List<string> { "1", "1", "1", "2" };
nums.ToDictionary(x => x, x => x);

But, perhaps, you may want to do this:

var lu = nums.ToLookup(x => x, x => x);

And now you can look them up:

// Returns 3 items since there are 3 "1"s
IEnumerable<string> ones = lu["1"];