Entity Framework Core 3, get foreign key without join

Question

Using EF Core I'd like to fetch the foreign keys without a join with those tables.

I have two tables:

Users [id, name, system(FK)]
System [id, name]

I'd like to be able to fetch the foreign key for System from the Users table. I would have thought EF would be clever enough to remove the join if no other properties than the FK is selected but that's not what I'm seeing when I'm testing.

I've tried with:

var userSystem = DBContext.Users
                          .Where(a => a.Id == 1)
                          .Select(a => new { UserId = a.id, SystemId = a.system.Id })
                          .FirstOrDefault();

but looking at the generated SQL query it contains an unnecessary LEFT JOIN on the system table. Any ideas as to how I can make EF generate code without the join?

Note: The join is obviously not a problem in this example but I have other queries with lots of FK being fetched with strict performance requirements.

user_5202563 · Answer

It used to be smart enough (and probably will in the future), but something has broken with EF Core 3.x query pipeline rewrite, so currently it generates unnecessary joins for this and many other cases (especially noticeable for owned entity types with table splitting).

The only way to workaround it in v3.x is to use EF.Property method, which requires knowing both shadow FK property name and its type. Which of course is not good and error prone, but this is the price for the current EF Core defect.

In your case it could be something like this:

SystemId = EF.Property(a, "systemId")

Hardcoding the FK property name can be avoided by retrieving from EF Core metadata:

var fkPropertyName = DBContext.Model
    .FindEntityType(typeof(User))
    .FindNavigation(nameof(User.system))
    .ForeignKey.Properties[0].Name;

and then

SystemId = EF.Property(a, fkPropertyName)

You still need to hardcode the type though.

Of course you can avoid all this by defining and using explicit FK property in your entity model.

Entity Framework Core 3, get foreign key without join

1 Answers