9

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.

M364M4N cro
  • 680
  • 2
  • 10
  • 23
  • Maybe you can try `IgnoreQueryFilters()` to disable global filter.https://learn.microsoft.com/en-us/ef/core/querying/filters#:~:text=This%20feature%20was%20introduced%20in,model%20(usually%20in%20OnModelCreating). – Yiyi You Aug 03 '20 at 09:18
  • 1
    Cannot do that. I need those global filters, but i only need them to apply to the FROM entity. They must not apply to JOIN entities – M364M4N cro Aug 03 '20 at 14:38

2 Answers2

8

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.

Jon P Smith
  • 2,391
  • 1
  • 30
  • 55
  • I think you made some errors in your example. Does the first query really need the .toList();. And is it not more important in the first query to select the foreign key that points to entity2? After i changed your example this way, i get the same result as before, but a different SQL query gets generated. But i do not know if that query is faster – M364M4N cro Aug 06 '20 at 18:05
  • 1
    No, you need the .ToList() on the entity1 query. Your question was how to load entity1 with query filters and then load entity2,3,4 without query filters. You didn't ask about performance. To improve performance I think you need to look at the design of your entities and soft delete to make any big difference in performance. – Jon P Smith Aug 07 '20 at 07:48
  • That's actually a very enlightening detail, and not many people know about it. But it's not surprising to see it from Jon. :) – Leaky Feb 06 '21 at 01:37
  • 2
    Hi @Leaky. You might like my article "What happens when EF Core reads from the database?" https://www.thereformedprogrammer.net/ef-core-in-depth-what-happens-when-ef-core-reads-from-the-database/ for more about EF Core querying and identity resolution. – Jon P Smith Feb 06 '21 at 09:01
  • I had no idea that EF did that kind of fixup, I have managed to make a query much more efficient with this, instead of EF creating a query that just exploded the number of rows required because of a table join I was able to query each table seperatly and massivly reduce the data transfer required and duplication in the SQL data returned. – Matt Jun 23 '23 at 09:37
1

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();
Emanuel Hiroshi
  • 318
  • 1
  • 7