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.

Any .net methods or classes cannot be translated to the query. your convert.toint() and Duration.totalminutes is probably your issue. If you still want these in there, add .tolist() before your select. — aweyeahdawg, Dec 18 '19 at 23:54
Is `TotalTempoParado = pr.StopRecords pr.StopRecords.Sum(c => Convert.ToInt32(c.Duration.TotalMinutes))` a typo? — Erik Philips, Dec 19 '19 at 00:00
You should post this question to EF Core GitHub to see what's their vision of handling something like this. Apparently switching to client evaluation as suggested is not directly possible, and adding eager/lazy loading would make it highly inefficient. — Ivan Stoev, Dec 19 '19 at 00:29
@ivanstoev client evaluation is possible, if you call asnotracking, then tolist, then filter those results. — aweyeahdawg, Dec 19 '19 at 00:53

timur · Accepted Answer · 2019-12-19T00:26:31.347

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.

also just be aware AsNoTracking has to be before AsEnumerable after — Jackal, Dec 18 '19 at 23:59
Worth noting that this will likely cause additional queries to be made to each of the three tables being summed in the Select clause. Potentially N+1 times — devNull, Dec 19 '19 at 00:03
@devNull i know but this will do until i can optimize the timespan — Jackal, Dec 19 '19 at 00:06
@devNull Or either NRE or 0 sums in case lazy loading is not enabled. The "answer" basically repeats the exception message and the proposed client side evaluation solution there, which is not good in general. — Ivan Stoev, Dec 19 '19 at 00:07
Be careful from an open use of IEnumerable data process, as it processing at the app level while it is a resource costly. — AbuDawood, Dec 19 '19 at 00:10
@IvanStoev fair point, I added a bit more context and a proposed way to solve the root cause properly. Hopefully that adds enough value to the OP and community — timur, Dec 19 '19 at 00:30

score 6 · Answer 2 · answered Dec 19 '19 at 01:48

6

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)");

answered Dec 19 '19 at 01:48

Jeremy Lakeman

9,515
25
29

I like this approach, i get to keep my query and just use the computed column which is more performant. I don't make it the right question because i already have accepted. – Jackal Dec 19 '19 at 08:42

Entityframework Core 3 linq expression could not be translated

2 Answers2

Linked