How can I execute a summation utilizing LINQ with IQueryable in Entity Framework Core?

Question

I'm working on a project using Entity Framework Core 3.1, and I need to sum the total experience of a candidate in years. In my code, I have a CvExperiences table with StartDate and EndDate fields, and I want to calculate the total Experience for each candidate in years.

Here is the part of the code where I try to calculate the Experience:

public class GetCandidateCVBankListQuery : DataFetchModel, IRequest<ListViewModel>
{
    public class Handler : IRequestHandler<GetCandidateCVBankListQuery, ListViewModel>
    {
        private readonly IHrmService hrmService;
        public Handler(IHrmService hrmService) => this.hrmService = hrmService;

        public async Task<ListViewModel> Handle(GetCandidateCVBankListQuery request, CancellationToken cancellationToken)
        {
            var query = hrmService.Context.CvPersonalInformations
              .Include(x => x.CvCertifications)
              .Include(x => x.CvEducations)
              .Include(x => x.CvEmergencyContacts)
              .Include(x => x.CvExperiences)
              .Include(x => x.CvSkills)
              .Include(x => x.CvReferenceDetails)
              .Include(x => x.CvProfessionalQualifications)
              .OrderByDescending(x => x.CvEducations.Any(e => e.IsHighestEducation.HasValue))
                .Select(x => new
                {
                    CandidateName = x.FullName,
                    HigherstEducation = x.CvEducations.Select(e => e.EducationLevel).FirstOrDefault(),
                    CGPA = x.CvEducations.Select(e => e.Result).FirstOrDefault(),
                    Institute = x.CvEducations.Select(e => e.InstitutionName).FirstOrDefault(),
                    Age = x.DateOfBirth.HasValue ? (DateTime.Now.Year - x.DateOfBirth.Value.Year -
                                (DateTime.Now.DayOfYear < x.DateOfBirth.Value.DayOfYear ? 1 : 0)) : (int?)null,
                    Experience = x.CvExperiences
                                .Where(e => e.StartDate.HasValue && e.EndDate.HasValue)
                                .Sum(e => (e.EndDate.Value - e.StartDate.Value).TotalDays / 365.25),
                    HomeDistrict = x.HomeDistrict
                })
              .AsQueryable();

            return await this.hrmService.GetListViewDataAsync(query, request, cancellationToken);
        }
    }
}

This approach does not work when I want Experience results, but without Experience, it worked fine. The error shows:

The LINQ expression '(EntityShaperExpression: 
    EntityType: CvExperience
    ValueBufferExpression: 
        ProjectionBindingExpression: EmptyProjectionMember
    IsNullable: False
.EndDate.Value - EntityShaperExpression: 
    EntityType: CvExperience
    ValueBufferExpression: 
        ProjectionBindingExpression: EmptyProjectionMember
    IsNullable: False
.StartDate.Value).TotalDays / 365.25' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.The LINQ expression '(EntityShaperExpression: 
    EntityType: CvExperience
    ValueBufferExpression: 
        ProjectionBindingExpression: EmptyProjectionMember
    IsNullable: False
.EndDate.Value - EntityShaperExpression: 
    EntityType: CvExperience
    ValueBufferExpression: 
        ProjectionBindingExpression: EmptyProjectionMember
    IsNullable: False
.StartDate.Value).TotalDays / 365.25' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Here it is said that LINQ cannot be directly translated into SQL. How do I do this?

Answer 1

You can translate the built-in function in SQL Server (DATEDIFF):

Experience = x.CvExperiences
    .Where(e => e.StartDate.HasValue && e.EndDate.HasValue)
    .Sum(e => 
        EF.Functions.DateDiffDay(e.StartDate.Value, e.EndDate.Value) / 365.0
    ) 

EF Core translates this to DATEDIFF(day, StartDate, EndDate), then divides by 365.0 to get approximate years.

Because EF Core 3.1 can't translate your C# calculations of differences between days into SQL.