I have C# application (.NET Core 4) and I have written the following LINQ expression for getting list of assigned tickets from database view.
public async Task<List<TicketSchedulerDTO>> GetAssignedTickets(ScheduleFilterDTO dTO, List<string> BranchIdList)
{
try
{
return await 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(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(),
})
.Where(y =>
(dTO.TicketCategoryIDs == null
|| dTO.TicketCategoryIDs.Count() == 0
|| !dTO.TicketCategoryIDs.Any()
|| dTO.TicketCategoryIDs.Contains(0)
|| dTO.TicketCategoryIDs.Any(z => z == y.CategoryId)) &&
(dTO.TicketStatusIDs == null
|| dTO.TicketStatusIDs.Count() == 0
|| !dTO.TicketStatusIDs.Any()
|| dTO.TicketStatusIDs.Contains(0)
|| dTO.TicketStatusIDs.Any(z => z == y.StatusId)) &&
(dTO.WorkerIDs == null
|| dTO.WorkerIDs.Count() == 0
|| !dTO.WorkerIDs.Any()
|| dTO.WorkerIDs.Contains(0)
|| dTO.WorkerIDs.Any(z => z == y.WorkerId)) &&
(dTO.LocationIDs == null
|| dTO.LocationIDs.Count() == 0
|| !dTO.LocationIDs.Any()
|| dTO.LocationIDs.Contains(0)
|| dTO.LocationIDs.Any(z => z == y.LocationId)) &&
(dTO.DepartmentIDs == null
|| dTO.DepartmentIDs.Count() == 0
|| !dTO.DepartmentIDs.Any()
|| dTO.DepartmentIDs.Contains(0)
|| dTO.DepartmentIDs.Any(z => z == y.DepartmentId)) &&
(dTO.SupervisorID == null
|| dTO.SupervisorID.Count() == 0
|| !dTO.SupervisorID.Any()
|| dTO.SupervisorID.Contains(0)
|| dTO.SupervisorID.Intersect(dTO.WorkerIDs).Any())
).ToListAsync();
}
catch (Exception ex)
{
throw ex;
}
}
Whenever this expression is executed i get the following error
System.InvalidOperationException: 'The LINQ expression '__dTO_TicketCategoryIDs_4
.Any(e0 => (int?)(int)e0 == (int?)new TicketSchedulerDTO{
Id = StructuralTypeShaperExpression(
StructuralType: MOM.Infrastructure.Schedule.Entities.TicketView
ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False).Id,
CallDateTime = StructuralTypeShaperExpression(
StructuralType: MOM.Infrastructure.Schedule.Entities.TicketView
ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False).CallDateTime,
Start = StructuralTypeShaperExpression(
StructuralType: MOM.Infrastructure.Schedule.Entities.TicketView
ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False).ScheduleDateTime,
CompleteDateTime = StructuralTypeShaperExpression(
StructuralType: MOM.Infrastructure.Schedule.Entities.TicketView
ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False).CompleteDateTime,
TotalEstimate = StructuralTypeShaperExpression(
StructuralType: MOM.Infrastructure.Schedule.Entities.TicketView
ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False).TotalEstimate,
End = SchedulerRepository.GetEndFromEstimate(
scheduleDateTime: StructuralTypeShaperExpression(
StructuralType: MOM.Infrastructure.Schedule.Entities.TicketView
ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False).ScheduleDateTime,
totalEstimate: StructuralTypeShaperExpression(
StructuralType: MOM.Infrastructure.Schedule.Entities.TicketView
ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False).TotalEstimate,
workEnd: __dTO_WorkDayEnd_3),
Title = "Ticket ID " + (object)StructuralTypeShaperExpression(
StructuralType: MOM.Infrastructure.Schedule.Entities.TicketView
ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False).Id,
Description = StructuralTypeShaperExpression(
StructuralType: MOM.Infrastructure.Schedule.Entities.TicketView
ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False).Reason,
Address = StructuralTypeShaperExpression(
StructuralType: MOM.Infrastructure.Schedule.Entities.TicketView
ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False).Address,
City = StructuralTypeShaperExpression(
StructuralType: MOM.Infrastructure.Schedule.Entities.TicketView
ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False).City,
StateName = StructuralTypeShaperExpression(
StructuralType: MOM.Infrastructure.Schedule.Entities.TicketView
ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False).StateName,
CountryName = StructuralTypeShaperExpression(
StructuralType: MOM.Infrastructure.Schedule.Entities.TicketView
ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False).CountryName,
WorkerId = StructuralTypeShaperExpression(
StructuralType: MOM.Infrastructure.Schedule.Entities.TicketView
ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False).WorkerId,
WorkerName = StructuralTypeShaperExpression(
StructuralType: MOM.Infrastructure.Schedule.Entities.TicketView
ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False).WorkerId == null ? "Unassigned Ticket" : StructuralTypeShaperExpression(
StructuralType: MOM.Infrastructure.Schedule.Entities.TicketView
ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False).WorkerName,
CategoryName = StructuralTypeShaperExpression(
StructuralType: MOM.Infrastructure.Schedule.Entities.TicketView
ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False).CategoryName,
LocationName = StructuralTypeShaperExpression(
StructuralType: MOM.Infrastructure.Schedule.Entities.TicketView
ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False).LocationName,
Equipment = StructuralTypeShaperExpression(
StructuralType: MOM.Infrastructure.Schedule.Entities.TicketView
ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False).Equipment,
StatusId = StructuralTypeShaperExpression(
StructuralType: MOM.Infrastructure.Schedule.Entities.TicketView
ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False).StatusId,
StatusName = StructuralTypeShaperExpression(
StructuralType: MOM.Infrastructure.Schedule.Entities.TicketView
ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False).StatusName,
StatusColor = StructuralTypeShaperExpression(
StructuralType: MOM.Infrastructure.Schedule.Entities.TicketView
ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False).StatusColor,
Longitude = StructuralTypeShaperExpression(
StructuralType: MOM.Infrastructure.Schedule.Entities.TicketView
ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False).Longitude,
Latitude = StructuralTypeShaperExpression(
StructuralType: MOM.Infrastructure.Schedule.Entities.TicketView
ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False).Latitude,
BranchName = StructuralTypeShaperExpression(
StructuralType: MOM.Infrastructure.Schedule.Entities.TicketView
ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False).BranchName,
CreatedBy = StructuralTypeShaperExpression(
StructuralType: MOM.Infrastructure.Schedule.Entities.TicketView
ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False).CreatedBy,
WorkComplete = (bool?)StructuralTypeShaperExpression(
StructuralType: MOM.Infrastructure.Schedule.Entities.TicketView
ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False).WorkComplete,
EnteredBy = StructuralTypeShaperExpression(
StructuralType: MOM.Infrastructure.Schedule.Entities.TicketView
ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False).EnteredBy,
IsCharge = (bool?)StructuralTypeShaperExpression(
StructuralType: MOM.Infrastructure.Schedule.Entities.TicketView
ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False).IsCharge,
IsDocument = StructuralTypeShaperExpression(
StructuralType: MOM.Infrastructure.Schedule.Entities.TicketView
ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False).IsDocumentExist,
DispatchAlert = StructuralTypeShaperExpression(
StructuralType: MOM.Infrastructure.Schedule.Entities.TicketView
ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False).DispatchAlert,
IsSignature = StructuralTypeShaperExpression(
StructuralType: MOM.Infrastructure.Schedule.Entities.TicketView
ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False).IsSigned,
IsReviewed = StructuralTypeShaperExpression(
StructuralType: MOM.Infrastructure.Schedule.Entities.TicketView
ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False).IsReviewed,
IsTimesheet = StructuralTypeShaperExpression(
StructuralType: MOM.Infrastructure.Schedule.Entities.TicketView
ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False).IsTimesheet,
InvoiceId = StructuralTypeShaperExpression(
StructuralType: MOM.Infrastructure.Schedule.Entities.TicketView
ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False).InvoiceId,
RecurringDate = StructuralTypeShaperExpression(
StructuralType: MOM.Infrastructure.Schedule.Entities.TicketView
ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False).RecurringDate,
OverTime = StructuralTypeShaperExpression(
StructuralType: MOM.Infrastructure.Schedule.Entities.TicketView
ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False).OverTime,
IsPayroll = (bool?)StructuralTypeShaperExpression(
StructuralType: MOM.Infrastructure.Schedule.Entities.TicketView
ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False).IsPayroll,
Icon = DbSet<TicketCategory>()
.Where(t0 => (int?)(int)t0.Id == (int?)StructuralTypeShaperExpression(
StructuralType: MOM.Infrastructure.Schedule.Entities.TicketView
ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False).CategoryId)
.Select(t0 => t0.Icon)
.FirstOrDefault()
}
.CategoryId)' 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 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.'
I do not know what is causing this issue as the same filters are working fine for local testing.
Please advise how can I resolve this issue.
4
EF Core tries to translate your LINQ query to SQL, but it cannot translate the method call GetEndFromEstimate, because this function does not exist in SQL. Therefore, you must perform the Select part of the query locally as LINQ-to-Objects. You can do this by splitting the database part of the query from the objects part with ToListAsync.
Something like this:
// Database query
var temp = await 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)
.ToListAsync();
// Local query
return temp
.Select(x => new TicketSchedulerDTO
{
Id = x.Id,
CallDateTime = x.CallDateTime,
...
// This will be performed locally now:
End = GetEndFromEstimate(x.ScheduleDateTime, x.TotalEstimate, dTO.WorkDayEnd),
...
}).Where(y => ...
).ToList();
Note that it would reduce the network traffic, if you could execute the Where that follows the Select in the database part, as this would reduce the number of returned records.
Note also that you can combine several Where filters into one by combining their conditions with &&.