Sum of count in EF core

Question

I have a database query as below:

 Courses.Where(c => (c.MaxParticipants != 0) && (c.CourseBookings.Sum(c => c.CourseParticipants.Count()) / c.MaxParticipants <= 0.5) && (c.CourseSubs.FirstOrDefault().BookedDates.FirstOrDefault().FromDate - DateTime.Today).Days <= 1)

The query works in ef 6 but not in ef core, which it needs to do.

In an effort to make it work, I've rewritten the query. All parts except c.CourseBooking.Sum (c => c.CourseParticipant.Count() seems to be fine. At the moment I get an exception saying "cannot perform an aggregate function on an expression containing an aggregate or a subquery"

from co in DbContext.Course
            join cb in DbContext.CourseBooking on co.CourseId equals cb.CourseId
            join cs in DbContext.CourseSub on cb.CourseId equals cs.CourseId
            join bd in DbContext.BookedDate on cs.CourseSubId equals bd.CourseSubId
            where (co.MaxParticipants != 0) && (EF.Functions.DateDiffDay(bd.FromDate, DateTime.Today) <= 1)
            select co;

How can I write to take the sum of the counts?

Answer 1

You can do something like this:

from co in DbContext.Course
        join cb in DbContext.CourseBooking on co.CourseId equals cb.CourseId
        join cs in DbContext.CourseSub on cb.CourseId equals cs.CourseId
        join bd in DbContext.BookedDate on cs.CourseSubId equals bd.CourseSubId
        where (co.MaxParticipants != 0) && (EF.Functions.DateDiffDay(bd.FromDate, DateTime.Today) <= 1)
        group new {cb, cs, bd} by co into groupedInfo
        select new 
        {
            Course = groupedInfo.Key,
            Booking = groupInfo.Sum(x=>x.cb.CourseParticipants) // if this is a number field
            // add any other sum you need here
        };