3

I used the following EF Core Linq query with EF 6 where PK is a Guid and it worked:

var partialSCs = repository.MyClass
                           .Where(x => x.PK.CompareTo(pk) > 0))
                           .OrderBy(x => x.PK)
                           .Take(10);

EF Linq query was successfully translated to a SQL query.

Then we changed to EF Core 7, and now I get this error:

: 'The LINQ expression 'DbSet() .Where(s.PK.CompareTo((object)__pk_2) > 0)' could not be translated. Additional information: Translation of method 'System.Guid.CompareTo' failed. If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information. 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 already read the following articles:

How to compare two GUIDs in Linq to Entities

Entity Framework Core: Guid Greater Than for Paging

What they say is that CompareTo must work. But it doesn't in my case.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
elahehab
  • 325
  • 3
  • 16
  • Doesn't sound like this is relevant to the SQL Server tag, but you might want to add C# & .NET – Dale K Aug 16 '23 at 02:03
  • Why not just `.Where(x => x.PK > pk)` – Charlieface Aug 16 '23 at 02:05
  • @Charlieface Its not working. It gives me compile error: Operand '>' cannot be applied to operands of type 'Guid' and 'Guid' – elahehab Aug 16 '23 at 02:16
  • For some reason they did not add translation of that method, and also for some unknown reason `Guid` type does not provide comparison operators like other types (same for `string` btw). Workaround is provided in the duplicate target. – Ivan Stoev Aug 16 '23 at 02:58
  • 4
    I found the problem! Actually EF can translate CompareTo for 2 Guid objects. My problem was that one of the Guids was nullable it was like Guid.CompareTo(Guid?) So it couldn't translate it. – elahehab Aug 16 '23 at 03:16
  • @elahehab So they must have added translation of `Guid.CompareTo(Guid)` in some later version (5, 6, 7 who knows) then. And you were hitting `Guid.CompareTo(object)` overload (there is no overload with `Guid?`), which is normal not to be translatable. Actually it is seen in the error message (the `object` cast here `s.PK.CompareTo((object)__pk_2) > 0`), didn't noticed it initially. Reopening so you can post a self answer for a future readers. – Ivan Stoev Aug 16 '23 at 05:00

1 Answers1

1

I found the problem! Actually EF can translate CompareTo for 2 Guid objects. My problem was that one of the Guids was nullable it was like Guid.CompareTo(Guid?) So it couldn't translate it.

It can be seen in the error: DbSet() .Where(s.PK.CompareTo((object)__pk_2) > 0) that it is trying to cast __pk_2 to object.

elahehab
  • 325
  • 3
  • 16