代码之家 › 专栏 › 技术社区 › Showyeab Ahmed

如何在Entity Framework Core中使用带有IQuery的LINQ执行求和?

asp.net-core c#

Showyeab Ahmed · 技术社区 · 5 月前

我正在使用Entity Framework Core 3.1进行一个项目,我需要总结候选人多年的总体经验。在我的代码中,我有一个CvExperiences表,其中包含StartDate和EndDate字段,我想计算总数 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);
        }
    }
}

这种方法在我想的时候不起作用 经验 结果,但没有 经验 ,它工作得很好。错误显示:

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.

这里说LINQ不能直接翻译成SQL。我该怎么做?

2 回复 | 直到 5 月前

marc_s 5 月前

您可以翻译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将其翻译为 DATEDIFF(day, StartDate, EndDate) ,然后除以365.0得到近似年份。

因为EF Core 3.1无法将C#计算的天数差异转换为SQL。

Hady Salah 5 月前

我想你也可以使用AsEnumerable,但是 你必须知道 这将在内存中进行所有计算,所以如果你有非常大的数据,这将在你的查询中产生一些延迟,这是代码

Experience = x.CvExperiences
    .Where(e => e.StartDate.HasValue && e.EndDate.HasValue)
    .AsEnumerable()  
    .Sum(e => (e.EndDate.Value - e.StartDate.Value).TotalDays / 365.25);