Query with `groupjoin` cannot be translated although it’s documened as being supported

by

The explanation in the linked documentation just follows the EF Core team vision and is ridiculous, because of course it can easily be translated – I had a long discussion with the team here Query with GroupBy or GroupJoin throws exception #17068 and continue here Query: Support GroupJoin when it is final query operator #19930, trying to convince them why it should be supported, with no luck regardless of the arguments.

The whole point is (and that’s the current workaround) it can be processed like if it was correlated subquery (SelectMany), which is translated and processed properly (even though the query result shape has no SQL equivalent.

Anyway, the current status is “Needs Design” (whatever that means), and the workaround is to replace the join with correlated subquery (which is what EF Core is using internally when “expanding” collection navigation properties during the query translation).

In your case, replace

join b in ctx.Bs on a.aId equals b.aId into bs

with

let bs = ctx.Bs.Where(b => a.aId == b.aId)

However, I highly recommend adding and using navigation properties. Not sure why you “can’t use” them, in LINQ to Entities which do not project entities they serve just metadata for relationships, thus produce automatically the necessary joins. By not defining them you just put on yourself unneeded limitations (additionally to EF Core limitations/bugs). In general EF Core works better and support more things when using navigation properties instead of manual joins.

More Related Contents:

  1. How to create a many to many relationship with latest nightly builds of EF Core?
  2. Getting metadata in EF Core: table and column mappings
  3. Raw SQL Query without DbSet – Entity Framework Core
  4. EF Core returns null relations until direct access
  5. Entering keys manually with Entity Framework
  6. .Net Core 3.0 possible object cycle was detected which is not supported
  7. .NET Core 2.1 Identity get all users with their associated roles
  8. Can I reuse code for selecting a custom DTO object for a child property with EF Core?
  9. How and where to call Database.EnsureCreated and Database.Migrate?
  10. Command dotnet ef not found
  11. How to unapply a migration in ASP.NET Core with EF Core
  12. Entity Framework Core Using multiple DbContexts
  13. How to auto create database on first run?
  14. loading a full hierarchy from a self referencing table with EntityFramework.Core
  15. EntityFramework Core automatic migrations
  16. EF Core Mapping EntityTypeConfiguration
  17. Configuring Dbcontext as Transient
  18. Entity Framework | Sequence contains more than one matching element
  19. Entity Framework Core: DbContextOptionsBuilder does not contain a definition for ‘usesqlserver’ and no extension method ‘usesqlserver’
  20. Working with SQL views in Entity Framework Core
  21. Entity Framework core – Contains is case sensitive or case insensitive?
  22. How do I delete multiple rows in Entity Framework Core? [duplicate]
  23. ASP.NET Core & EntityFramework Core: Left (Outer) Join in Linq
  24. Changes in IDENTITY column after EF core 3
  25. Entity Framework Core: `SqlNullValueException: Data is Null.` How to troubleshoot?
  26. What goes into DbContextOptions when invoking a new DbContext?
  27. DbSet doesn’t have a Find method in EF7
  28. How should I manage DbContext Lifetime in MVC Core?
  29. Entity Framework Core 2.0 – Run migrations step by step
  30. DbFunctions.TruncateTime LINQ equivalent in EF CORE

Leave a Comment