Ignore global query filter for joined entities

Question

Global query filters are very handy when implementing tenant and soft deletion features.

But my problem is is that when i write a query with joins, for instance

dbContext
    .entity1
    .Include("entity2.entity3.entity4")
    .Where(something)
    .select(something)
    .toList();

and every one of those entities has global filters, then in the generated SQL i get for every JOIN a full Subquery where it selects all fields of that entity and checks for the global filters.

But i dont want that. I want the global filters to only apply to the root entity of the query (entity1), and all the other entities to join normaly.

btw the relationships of the entities are:

• 1 entity4 -> N entity3
• 1 entity3 -> N entity2
• 1 entity2 -> N entity1

In my case, every entity gets its "tenant" field set and when soft deleting an entity, that soft deletion is cascaded to all its children and subchildren. Because of that checking those fields for every join is a complete waste of time.

Answer 1

At the moment you can't ignore query filter in an Include. There is an issue on the EF Core repo about improving query filters, but it won't go into EF Core 5.

One approach that might help is running multiple queries and rely on EF Core's relational fixup feature to join things together (I explain relational fixup in my article EF Core In depth – what happens when EF Core reads from the database?).

Here is an example of doing two queries, where relational fixup will join together.

var mainEntities = dbContext.entity1
   .Where(something)
   .Select(something) //But must contain the ent1 primary key, e.g. Id
   .ToList();
var ent2list = dbContext.entity2.IgnoreQueryFilters()
   .Include("entity3.entity4")
   .Where(ent2 => mainEntities.Select(ent1 => ent1.Id).Contains(ent2.Id)
   .ToList();

At the end of this EF Core's relational fixup will fill in the entity2 navigational property in the appropriate entity1 instance. NOTE: Relational fixup doesn't work if you use AsNoTracking.

Answer 2

I was able to do that using explicit loading.

I'm not sure if that resolves your problem but I've tested using ef 5.0.13 and it worked for me.

db.Entry(entity01).Reference(t => t.entity02).Query().IgnoreQueryFilters().Load();