.NET Core应用LINQ表达式无法翻译错误的解决求助

问题根源 #

你遇到的错误是因为EF Core无法将「映射到DTO之后再过滤」的逻辑转换为SQL。当你先通过Select创建TicketSchedulerDTO对象，再用Where对DTO的属性进行过滤时，EF Core尝试把整个表达式翻译成SQL，但DTO是内存中的客户端对象，数据库无法识别这种针对DTO字段的过滤条件。本地测试能运行可能是因为启用了隐式客户端评估，而当前环境禁用了该特性（这是EF Core的默认行为，避免性能问题）。

修复方案 #

将所有过滤逻辑提前到Select之前，直接针对数据库视图TicketView的原生字段进行过滤，这样EF Core可以把过滤条件转换为SQL，在数据库层面完成筛选，既解决翻译问题，也提升查询效率。

修改后的代码示例 #

public async Task<List<TicketSchedulerDTO>> GetAssignedTickets(ScheduleFilterDTO dTO, List<string> BranchIdList)
{
    try
    {
        var query = context.TicketView
            .Where(x => BranchIdList.Contains(x.BranchId.ToString()))
            .Where(t => t.ScheduleDateTime <= dTO.EndDate && t.ScheduleDateTime >= dTO.StartDate && t.StatusId != 1 && t.StatusId != 7)
            // 把原Select后的过滤条件移到此处，直接使用TicketView的原生字段
            .Where(x => 
                (dTO.TicketCategoryIDs == null || !dTO.TicketCategoryIDs.Any() || dTO.TicketCategoryIDs.Contains(0) || dTO.TicketCategoryIDs.Contains(x.CategoryId)) &&
                (dTO.TicketStatusIDs == null || !dTO.TicketStatusIDs.Any() || dTO.TicketStatusIDs.Contains(0) || dTO.TicketStatusIDs.Contains(x.StatusId)) &&
                (dTO.WorkerIDs == null || !dTO.WorkerIDs.Any() || dTO.WorkerIDs.Contains(0) || dTO.WorkerIDs.Contains(x.WorkerId)) &&
                (dTO.LocationIDs == null || !dTO.LocationIDs.Any() || dTO.LocationIDs.Contains(0) || dTO.LocationIDs.Contains(x.LocationId)) &&
                (dTO.DepartmentIDs == null || !dTO.DepartmentIDs.Any() || dTO.DepartmentIDs.Contains(0) || dTO.DepartmentIDs.Contains(x.DepartmentId)) &&
                (dTO.SupervisorID == null || !dTO.SupervisorID.Any() || dTO.SupervisorID.Contains(0) || dTO.SupervisorID.Intersect(dTO.WorkerIDs).Any())
            )
            .Select(x => new TicketSchedulerDTO
            {
                Id = x.Id,
                CallDateTime = x.CallDateTime,
                Start = x.ScheduleDateTime,
                CompleteDateTime = x.CompleteDateTime,
                TotalEstimate = x.TotalEstimate,
                End = GetEndFromEstimate(x.ScheduleDateTime, x.TotalEstimate, dTO.WorkDayEnd),
                Title = MOM.Alias.Common.DTO.TicketID + " " + x.Id,
                Description = x.Reason,
                Address = x.Address,
                City = x.City,
                StateName = x.StateName,
                CountryName = x.CountryName,
                WorkerId = x.WorkerId,
                WorkerName = (x.WorkerId == null) ? MOM.Alias.Common.DTO.UnassignedTicket : x.WorkerName,
                CategoryName = x.CategoryName,
                LocationName = x.LocationName,
                Equipment = x.Equipment,
                StatusId = x.StatusId,
                StatusName = x.StatusName,
                StatusColor = x.StatusColor,
                Longitude = x.Longitude,
                Latitude = x.Latitude,
                BranchName = x.BranchName,
                CreatedBy = x.CreatedBy,
                WorkComplete = x.WorkComplete,
                EnteredBy = x.EnteredBy,
                IsCharge = x.IsCharge,
                IsDocument = x.IsDocumentExist,
                DispatchAlert = x.DispatchAlert,
                IsSignature = x.IsSigned,
                IsReviewed = x.IsReviewed,
                IsTimesheet = x.IsTimesheet,
                InvoiceId = x.InvoiceId,
                RecurringDate = x.RecurringDate,
                OverTime = x.OverTime,
                IsPayroll = x.IsPayroll,
                Icon = context.TicketCategory.Where(y => y.Id == x.CategoryId).Select(y => y.Icon).FirstOrDefault(),
            });

        return await query.ToListAsync();
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

关键改动说明 #

• 移除了Select之后的Where，将所有过滤条件移到Select之前，直接使用TicketView的字段（比如x.CategoryId代替原来的y.CategoryId）。
• 简化了过滤条件中的冗余判断（比如Count() == 0和!Any()重复，保留!Any()即可）。
• 确保所有过滤逻辑都能被EF Core转换为SQL语句，在数据库端执行筛选。

内容的提问来源于stack exchange，提问作者Prashil Shah