Upgrading to EF Core 3 Broke All LINQ Queries

c# ef-core-3.0 entity-framework-core linq

Question

I've read why (https://docs.microsoft.com/en-us/ef/core/querying/client-eval) after I got the errors and I understand the explanation, but I'm not sure what needs to be changed.

So this won't work:

var result = (from ts in _context.TradesSeries
                          join tts in _context.TradesTrades
                          on ts.Id equals tts.SeriesId
                          where seriesIds.Contains(tts.SeriesId) && tts.FirstPartyId == null &&
                                tts.Status != "closed" && tts.Status != "cancelled"
                          group new { ts, tts } by new { tts.SeriesId } into g
                          select new TotalByIsin
                          {
                              SeriesId = g.Key.SeriesId,
                              IsinShortCode = g.Select(i => i.ts.Number).Distinct().First(),
                              Isin = g.Select(i => i.ts.Isin).Distinct().First(),
                              Amount = (double)g.Sum(pt => pt.tts.Amount),
                              NumberOfTrades = g.Count()
                          }).ToList();
            return result;

And neither will this:

var result = (from tt in _context.TradesTrades
                          join ts in _context.TradesSeries on tt.SeriesId equals ts.Id
                          join ttd in _context.TradesTradeDistributors on tt.Id equals ttd.TradeId
                          join to in _context.TradesOrganisations on ttd.DistributorId equals to.Id
                          where seriesIds.Contains(tt.SeriesId) && tt.FirstPartyId == null &&
                                      tt.Status != "closed" && tt.Status != "cancelled" && 
                                      to.DefaultDistributor !=1 && to.ExcludeDistSponView !=1 && to.ExcludeFromDwdpTotal !=1
                          join tc in _context.TradesCountries on to.CountryId equals tc.Id into tcj
                          from tc in tcj.DefaultIfEmpty() // GroupJoin -> left join
                          group new { tt, ts, ttd, to, tc } by new { ttd.DistributorId}
                          into tradeg
                          let commissionTotal = tradeg.Sum(trade => trade.ttd.Commission)
                          orderby commissionTotal descending
                          select new TopDistributors
                          {
                              //SeriesId = tradeg.Key.SeriesId,
                              DistributorName = tradeg.First().to.Name,
                              IsinShortCode = tradeg.First().ts.Number,
                              CountryName = tradeg.First().tc == null ? "N/A" : tradeg.First().tc.Name,
                              Amount = Math.Ceiling((double) commissionTotal)
                          }).Take(5).ToList();
             return result;

...and neither will any of the other 30 some odd queries I've constructed based on help from StackOverflow LINQ experts. What's considered to be client in these queries and what's not? Is there a simple fix to remedy this or should I just use Dapper and go straight SQL just in case Microsoft decides to break LINQ functionality again? Although, I'd rather not have to fully rewrite 30 queries before the rest of the team gets in tomorrow morning. 😂🤷🏻‍♂️

Thanks!

1
-1
11/18/2019 9:24:25 AM

Accepted Answer

I was able to rollback Pomelo.EntityFrameworkCore.MySql to version 2.2.6 and everything is working again.

Will investigate what's client and what's not since Ian provided a link with one example that included a function call - which really doesn't answer the question I posted. I still don't know what's client and what's not...for example, is LET a client side thing?

0
11/19/2019 10:04:38 PM

Popular Answer

It's because of the breaking changes preventing automatic client side evaluation.

You can use AsEnumerable() on your join then use the groupBy and select which are not translatable to SQL.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow