1

In my project I am currently having an issue with the following LINQ query:

context.WebPages.GroupBy(x => x.Url).Select(x => new { x.Key, x.FirstOrDefault()?.LogoId } ).ToList();

Basically, I am trying to get from our DB web pages distinct by their URL and the first logo ID that is assigned to them. However, I am struggling with the warning saying: The LINQ expressio could not be translated and will be evaluated locally. Since I am storing a couple of million web pages, I don't want to load unnecessary data. And I definitely don't want the expression to be evaluated locally.

I have tried several optimizations of the LINQ expressions, e.g.: 

context.WebPages.GroupBY(x => x.Url, x => new {x.LogoId}).Select(x => new { x.Key, x.FirstOrDefault()?.LogoId } ).ToList();
context.WebPages.GroupBy(x => x.Url).Select(x => new { x.Key, x.First().LogoId } ).ToList();
context.WebPages.GroupBy(x => x.Url).Select(x => x.First()).ToList();
context.WebPages.GroupBy(x => x.Url).ToList();

but I always ended up with the same warning. The only query that could be translated (but is useless to me), was:

context.WebPages.GroupBy(x => x.Url).Select(x => x.Key).ToList();

Is there any alternative LINQ expression that could work (or even a set of LINQ expressions)? Or do I need to use plain SQL expression?

Side note: We are also planning to move to .NET Core 3.0, but that is a couple of months distant future... and I cannot wait until "then".

Storm
  • 3,062
  • 4
  • 23
  • 54
  • 1
    I think if you move to 3.0 then this warning will become an compile time error. Have a look [at this post](https://stackoverflow.com/questions/58092869/ef-core-2-2-linq-query-not-working-in-ef-core-3-0) – Mong Zhu Nov 19 '19 at 12:02
  • One more reason to get rid of it as soon as possible. – Storm Nov 19 '19 at 12:03
  • You can also read [this for further information](https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.0/breaking-changes#linq-queries-are-no-longer-evaluated-on-the-client) – Mong Zhu Nov 19 '19 at 12:04
  • I think the problem here is the null check using the `?` operator. It cannot be translated to SQL so it will collect the entire data and then make the select in your memory – Mong Zhu Nov 19 '19 at 12:05
  • No, that is not the problem. I have also tried `x.First().LogoId`, but it did not work either. – Storm Nov 19 '19 at 12:06
  • and if you select only x? without the anonymous type? – Mong Zhu Nov 19 '19 at 12:07
  • No, still not able to translate. – Storm Nov 19 '19 at 12:17
  • a simple group by statement is not translateable? what da hack? could you post the SQL query that is generated when this linq query is executed? – Mong Zhu Nov 19 '19 at 12:19
  • Well, because the query could not be translated, there is no SQL generated... or at least I cannot see any in the logs. Moreover, it is not just a simple `GroupBy`, there is also the `FirstOrDefault`. – Storm Nov 19 '19 at 12:30
  • "a simple group by statement is not translateable?" this comment was related to the question "and if you select only x? without the anonymous type?" because I assumed there would be no `FirstOrDefault` – Mong Zhu Nov 19 '19 at 13:56
  • No, it wrote `The LINQ expression 'GroupBy([x].Url, [x])' could not be translated and will be evaluated locally.` – Storm Nov 19 '19 at 14:00
  • what about context.WebPages.GroupBy(x => x.Url).Select(x => x.FirstOrDefault()).Select(x => new { x.Key, x.LogoId } ).ToList(); I have a similar query in my code and this worked for me. – Ben Nov 19 '19 at 14:20
  • Sadly, EF Core 2.2 has limited `GroupBy` handling and I don't think was ready for production use. EF Core 3 is much better, and would probably be fine with this. What is the type of `x.Url`? – NetMage Nov 19 '19 at 22:39
  • @Ben that code is going to cause a type error - there will be no `Key` member of `x` after you do the first `Select`. – NetMage Nov 19 '19 at 22:40
  • @Ben I have tried `context.WebPages.GroupBy(x => x.Url).Select(x => x.FirstOrDefault()).Select(x => new { x.Url, x.LogoId } ).ToList()`, but it could not be translated. @NetMage Url is a string (or character varying on Postgre side). – Storm Nov 20 '19 at 06:59
  • First, it is a known issue for a year. https://github.com/aspnet/EntityFrameworkCore/issues/12088 I know it is a stupid but we have solved it by using EF 6.3 instead of EF Core 3.0 in .Net Core 3 application – Kuroro Nov 28 '19 at 05:51

1 Answers1

0

How about if we try an exclusion join? Assuming LogoId is a comparable type (e.g. int):

var ans = from a in context.WebPages
          where !(from b in context.WebPages where a.Url == b.Url && a.LogoId > b.LogoId select b).Any()
          select new {
              a.Url,
              a.LogoId
          };

Update: I tested this with EF 2.2.6 and it generated (possibly inefficient) SQL fine.

Update 2: I also tested with EF 3 and it worked. My earlier Distinct/GroupJoin failed SQL translation in EF 3.

NetMage
  • 26,163
  • 3
  • 34
  • 55