Entityframework Core 3 linq expression could not be translated

Question

I just upgrade to EF 3 and one of my queries that used to work, gives an exception now

   ProductionRecords = _context.ProductionRecords
          .Where(r => r.DataCriacao.Date == DateTime.Now.Date)
            .Select(pr => new ProductionRecordViewModel
            {
                Id = pr.Id,
                Operador = pr.Operador,
                DataCriacao = pr.DataCriacao,
                Celula = pr.Celula.Name,
                Turno = pr.Turno.Name,
                TotalPecasSemDefeito = pr.ReferenceRecords.Sum(c => c.Quantity),
                TotalPecasComDefeito = pr.DefectRecords.Sum(c => c.Quantidade),
                TotalTempoParado = pr.StopRecords.Sum(c => Convert.ToInt32(c.Duration.TotalMinutes)),
            })
          .AsNoTracking()
          .ToList();

The exception happens when i'm trying to sum the collection with the timespan with the duration....

How am i supposed to handle this now?

here is the exception

InvalidOperationException: The LINQ expression '(EntityShaperExpression: EntityType: StopRecord ValueBufferExpression: (ProjectionBindingExpression: EmptyProjectionMember) IsNullable: False ).Duration.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 either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Answer 1

There's been a breaking change in EF3 that will not automatically revert to client side evaluation unless at the very end of query chain (which your Convert.ToInt32(c.Duration.TotalMinutes) was likely relying on).

Try rewriting your query like so:

 ProductionRecords = _context.ProductionRecords
      .Where(r => r.DataCriacao.Date == DateTime.Now.Date)
        .AsNoTracking()
        .AsEnumerable()
        .Select(pr => new ProductionRecordViewModel
        {
            Id = pr.Id,
            Operador = pr.Operador,
            DataCriacao = pr.DataCriacao,
            Celula = pr.Celula.Name,
            Turno = pr.Turno.Name,
            TotalPecasSemDefeito = pr.ReferenceRecords.Sum(c => c.Quantity),
            TotalPecasComDefeito = pr.DefectRecords.Sum(c => c.Quantidade),
            TotalTempoParado = pr.StopRecords pr.StopRecords.Sum(c => Convert.ToInt32(c.Duration.TotalMinutes)),
        })
      .ToList();

UPD As it's been rightly pointed out in the comments - this will basically defer the .Select evaluation to the client side. Which will likely cause performance issues. Most likely this behaviour has been the reason this change was made to EF Core 3 in the first place.

I don't have enough specifics to recommend you a proper solution, but it seems you can't really get away from loading StopRecords on all your results. Which is where writing a custom method translator can help you. See my other answer on how to do that. I quickly checked EF Core 3 source and it seems IMethodCallTranslator is still there. Which means you have pretty high chance of building a custom function that will convert dates to TotalMinutes in SQL.

Answer 2

Instead of trying to teach EF Core how to sum a timespan, how about adding a computed column to the database?

        public TimeSpan Duration { get; set; }
        public int Minutes { get; }


        entity.Property(e => e.Minutes)
            .HasComputedColumnSql("DATEDIFF(MINUTE, 0, Duration)");