Force inner join from EF Cores Include() on optional relationship

asp.net-core c# entity-framework-core

Question

We changed a relationship from required to optional, now the resulting SQL produced by EF Core's Include() does a left outer join rather than inner join. Problem is that those optional entities have query filters on them that is required.

Let's say we have the following;

public class First 
{
    public int? SecondId { get; set; }
}
public class Second 
{
    public First First { get; set; }
    public int ThirdId { get; set; }
}
public class Third
{
    public Second Second { get; set; }
    public string Tenant { get; set; }
}

public class MyContext : DbContext 
{
    protected readonly string _tenant;
    ...
    modelBuilder.Entity<Third>(p =>
    {
        p.HasQueryFilter(x => Tenant == _tenant);
    });
    ...
}

And then we do the following:

MyContext.First.Include(p => p.Second).ThenInclude(p => p.Third);

This will produce a LEFT OUTER JOIN since the relationship is optional. This will of course then bypass the query filter. Is there a way to make this Include an INNER JOIN instead?

Currently this is solved with adding some more conditions to the where later:

.Where(p => p.Second.Third.Tenant == _tenant);

But this is undesirable since in some edge cases the _tenant is null and will then give the wrong data.

I know I can flip it around and go for

MyContext.Third.Include() ...

But this is also undesirable as First in this scenario has a lot of other related data and I don't want to endlessly chain Include().ThenInclude() to the point of ad absurdum.

Can I force inner joins with optional entities? Or do I manually have to write the SQL for this?

1
1
4/26/2019 7:07:13 AM

Accepted Answer

Can I force inner joins with optional entities?

You can't. And you shouldn't. Because while inner join would probably solve your particular case, in general it would filter all dependent entities having null FK (e.g. First.SecondId == null) which is against the whole concept of optional relationship.

The problem I see is that you seem to be trying to use Include for filtering. By idea Include is what it says - for each entity returned by the query, also include the related data. It's not intended to filter neither the entity nor the related data.

So what you need has to be a query filter.

The actual problem is that EF Core Global Query Filters do not support criteria based on navigation properties. That's why people in such scenarios break normalization (introduce redundancy) and put TenantId property(column) in every entity(table), which allows them to set global filter for each entity.

With that being said, the explicit query filter (Where) is currently the only option.

   .Where(p => p.Second.Third.Tenant == _tenant);

But this is undesirable since in some edge cases the _tenant is null and will then give the wrong data.

Well, you just need a correct criteria taking into account the optional relationships, for instance

.Where(p => p.SecondId == null || p.Second.Third.Tenant == _tenant);

But that actually shows the problem of not having Tenant on each entity - when First.SecondId == null, you can't tell which Tenant is owning the First.

2
4/26/2019 11:10:49 AM


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