CODEWITHSUNDEEP
.net-5ef-core-5.0
Ashley Williams
Ashley Williams

Reputation: 31

EF Core 5 Sum difference between two date times

We have upgraded to net 5 / ef core 5, and many of our queries have needed "fixing" due to the changes that have been made.

In reality, the queries were horribly inefficient, because the LINQ could not be translated to SQL and now this has been brought to our attention; one solution is to force this to be worked on the client instead of the SQL Server... however I would like this to make this more efficient.

Below is a query that I can't find a way to do efficiently:

        DateTime toDate = DateTime.Now.AddDays(NumberofDays + 1).Date;
        MyInsights.AvailableHours = DatabaseContext.WorkCenterSchedules
            .Where(x => x.ForWorkCenter.WorkCenterId == WorkCenterID && x.dateTo > DateTime.Now && x.dateTo < toDate)
            .Sum(y => (y.dateTo - (DateTime.Now > y.dateFrom ? DateTime.Now : y.dateFrom)).TotalMinutes) / 60;

The error is:

error image

This could be changed to the following:

        DateTime toDate = DateTime.Now.AddDays(NumberofDays + 1).Date;
        List<WorkCenterSchedule> schedules = DatabaseContext.WorkCenterSchedules
            .Where(x => x.ForWorkCenter.WorkCenterId == WorkCenterID && x.dateTo > DateTime.Now && x.dateTo < toDate).ToList();
        MyInsights.AvailableHours = schedules.Sum(y => (y.dateTo - (DateTime.Now > y.dateFrom ? DateTime.Now : y.dateFrom)).TotalMinutes) / 60;

However, this is far from ideal. I know EF Core isn't ideal for complex queries, but I feel like I am missing something obvious here, is there something I can do to improve this?

Upvotes: 1

Views: 2480

Answers (1)

bricelam
bricelam

Reputation: 30415

You might be able to leverage SQL Server's DATEDIFF function:

EF.Functions.DateDiffMinute(y.dateTo, DateTime.Now > y.dateFrom ? DateTime.Now : y.dateFrom)

Upvotes: 6

Related Questions