Workaround for TotalMinutes functionality in EF query?-CodePudding

I have the following query.

var query = DbContext.Trucks
    .Where(t => t.Facility.Company.CompanyCode == companyCode)
    .Where(t => t.Departure >= startMonth && t.Departure < endMonth);

if (customerId.HasValue)
    query = query.Where(t => t.PurchaseOrder.CustomerId == customerId);

var results = await query.GroupBy(t => new { t.Departure!.Value.Year, t.Departure!.Value.Month })
    .Select(g => new
    {
        Month = new DateTime(g.Key.Year, g.Key.Month, 1),
        Dwell = g.Average(t => (t.Departure!.Value - t.Arrival).TotalMinutes)
    })
    .ToDictionaryAsync(g => g.Month, g => g.Dwell);

Currently, the query fails.

The LINQ expression 'GroupByShaperExpression:
KeySelector: new { 
    Year = DATEPART(year, t.Departure), 
    Month = DATEPART(month, t.Departure)
 }, 
GroupingEnumerable:ShapedQueryExpression: 
    QueryExpression: 
        Projection Mapping:
            Outer.Outer -> EntityProjectionExpression: Truck
            Outer.Inner -> EntityProjectionExpression: Facility
            Inner -> EntityProjectionExpression: Company
        SELECT 1
        FROM Trucks AS t
        INNER JOIN Facilities AS f ON t.FacilityId == f.Id
        INNER JOIN Companies AS c ON f.CompanyId == c.Id
        WHERE ((c.CompanyCode == @__companyCode_0) && ((t.Departure >= @__startMonth_1) && (t.Departure < @__endMonth_2))) && ((DATEPART(year, t.Departure) == DATEPART(year, t.Departure)) && (DATEPART(month, t.Departure) == DATEPART(month, t.Departure)))
    ShaperExpression: new TransparentIdentifier<TransparentIdentifier<Truck, Facility>, Company>(
            Outer = new TransparentIdentifier<Truck, Facility>(
                Outer = EntityShaperExpression: 
                    PegasusEntities.Models.Truck
                    ValueBufferExpression: 
                        ProjectionBindingExpression: Outer.Outer
                    IsNullable: False
                , 
                Inner = EntityShaperExpression: 
                    PegasusEntities.Models.Facility
                    ValueBufferExpression: 
                        ProjectionBindingExpression: Outer.Inner
                    IsNullable: False
            ), 
            Inner = EntityShaperExpression: 
                PegasusEntities.Models.Company
                ValueBufferExpression: 
                    ProjectionBindingExpression: Inner
                IsNullable: False
        )

    .AsQueryable()
    .Average(e => (e.Outer.Outer.Departure.Value - e.Outer.Outer.Arrival).TotalMinutes)' 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 believe I've isolated the problem to TimeSpan.TotalMinutes (in the assignment to Dwell), which apparently cannot be translated to SQL.

Short of asking Microsoft to add support for this, has anyone figured out a workaround for it? Running this on the client side would require pulling down a load of data that I don't need.

CodePudding user response：

For SQL Server you can use EF.Functions.DateDiffMinute

ie change

(t.Departure!.Value - t.Arrival).TotalMinutes

to something like

EF.Functions.DateDiffMinute(t.Arrival, t.Departure.Value)

Note that DATEDIFF counts boundary-crossings, while TimeSpan.TotalMinutes is like TimeSpan.TotalSeconds/60. So automatically translating TimeSpan.TotalMinutes to DateDiffMinute would be dangerous. If that's the required output you can use something like:

EF.Functions.DateDiffSecond(t.Arrival, t.Departure.Value) / 60.0

Other databases handle this scenario differently. EG Oracle and I think Postgres allow datetime arithmetic in SQL and so allow you to simply subtract dates. So the answer really provider-specific.