Entity framework core groupBy and distinct

Viewed 1065

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

0 Answers
Related