I have this query
items = await context.Bookings
.GroupBy(p => p.Start.Day, p => p.BookedById, (key, g) => new KeyValuePair<int, decimal>(key, g.Distinct().Count()))
.ToListAsync(cancellationToken);
it gives me error: Error generated for warning 'Microsoft.EntityFrameworkCore.Query.QueryClientEvaluationWarning: The LINQ expression 'GroupBy([p].Start.Day, [p].BookedById)' could not be translated and will be evaluated locally
but if I change it to double group by like this
items = await context.Bookings
.GroupBy(p => new { p.Start.Day, p.BookedById })
.Select(c => new { c.Key.Day, c.Key.BookedById, Count = c.Count() })
.GroupBy(z => z.Day)
.Select(c => new KeyValuePair<int, decimal>(c.Key, c.Count()))
.ToListAsync(cancellationToken);
it works is there any way not to use double group only distinct ?
In others words what I want is unique useres list count booked in day
lets have this sample of data: day, user, bookingsCount {(1, ad, 2), (1, ac, 3), (2, be, 1), (2, be 3)} so the result should be dayNumber, usersCount: (1,2) , (2, 1)
and where Bookings class has at least Start, BookedById and End columns
in my second query count in this line is not necessery .Select(c => new { c.Key.Day, c.Key.BookedById, Count = c.Count() })