efcore: Including entity with a query filter can cause not loading the root entity

I would expect the following code returns the instance of Child (the same instances). But it depends on a query filter. If child’s master is filtered, the second line returns null.

Child child = dbContext.Set<Child>().Where(c => c.Id == 1).FirstOrDefault();
Child childInludingMaster = dbContext.Set<Child>().Include(m => m.Master /* has a query filter */).Where(c => c.Id == 1).FirstOrDefault();

Steps to reproduce

Install NuGet packages
Create database IssueDemo (or change connection string)
Generate database migration
Run unit test

	[TestClass]
	public class TestIssue
	{
		[TestMethod]
		public void DbSet_ShouldReturnSameInstancesWheneverIncludeIsCalled()
		{
			// Arrange
			#region Data initialization
			int childId;

			using (var dbContext = new MyDbContext())
			{
				dbContext.Database.Migrate();

				Child child = new Child { IsDeleted = false };
				Master master = new Master { IsDeleted = true, Children = new List<Child> { child } };

				dbContext.Set<Master>().AddRange(master);
				dbContext.SaveChanges();

				childId = child.Id;
			}
			#endregion

			// Act
			using (var dbContext = new MyDbContext())
			{
				Child child = dbContext.Set<Child>().Where(c => c.Id == childId).FirstOrDefault();
				Child childInludingMaster = dbContext.Set<Child>().Include(m => m.Master /* has a query filter */).Where(c => c.Id == childId).FirstOrDefault();

				// Assert				
				Assert.AreSame(Object.ReferenceEquals(child, childInludingMaster), "Instances should be the same.");
			}
		}
	}

	#region Model
	public class Child
	{
		public int Id { get; set; }

		public Master Master { get; set; }
		public int MasterId { get; set; }

		public bool IsDeleted { get; set; }
	}

	public class Master
	{
		public int Id { get; set; }
		
		public List<Child> Children { get; set; }

		public bool IsDeleted { get; set; }
	}
	#endregion

	#region MyDbContext
	public class MyDbContext : DbContext
	{
		protected override void OnModelCreating(ModelBuilder modelBuilder)
		{
			base.OnModelCreating(modelBuilder);

			modelBuilder.Entity<Master>().HasQueryFilter(item => !item.IsDeleted);
			modelBuilder.Entity<Child>().HasQueryFilter(item => !item.IsDeleted);
		}

		protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
		{
			base.OnConfiguring(optionsBuilder);

			optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=IssueDemo;Trusted_Connection=True;");
		}
	}
	#endregion

Further technical details

EF Core version: 2.0.2 Database Provider: Microsoft.EntityFrameworkCore.SqlServer Target .NET Framework: 4.6.1

About this issue

Original URL
State: closed
Created 6 years ago
Comments: 19 (12 by maintainers)

Most upvoted comments

Thank you very much for the answer.

My description contains a synthetic scenario, that’s why Child/Master classes are not well understandable. I will describe it in more details. I am preparing guidelines how to use Entity Framework Core in our applications (tens of line of business applications, tailor-made software development). The issue is related to soft deletes. Softdelete-aware object in for us an object which is no more usable. This for us means:

Retrieving list of (softdelete-aware) objects When retrieving list of objects, a deleted object must not be returned. We want to be able to suppress this constraint to enable undelete scenario (like IgnoreQueryFilters). Example: If a user see list of cars, no deleted car is in the list.
Handling collections of (softdelete-aware) objects When an object A has a collection of objects B, we USUALLY do not want to load deleted objects B in the A’s collection. We would like to have an option to decide (in model definition) whether the collections should or should not contain deleted object. Example: Let’s say, we have a Group and it has a collection of Members in the group (group.Members). When we load a group, we do not want deleted members to be in the collection group.Members.
Handling references to (softdelete-aware) objects When an object A has a reference to object B, we want never to filter out this object. Example: Let’s say Car has a (required) Color. We want to have a car with color even when the color is deleted. Example: An Invoice was issued by an Accountant and we would like to know who it was even when the Accountant is deleted.

There is a conflict in our goal (we never filter out references, but we USUALLY filter out collections), but it fits our practice and business requirements. That’s our interpretation of soft delete 😉.

I tried the behavior of the Entity Framework Core and I did not expect the Include method should limit the root objects. Furthermore, I did not notice such information in the documentation (https://docs.microsoft.com/en-us/ef/core/querying/related-data). XML documentation of Include extension method (https://github.com/aspnet/EntityFrameworkCore/blob/a588722e10f34a12b9cea3d655fd26aafd91051b/src/EFCore/EntityFrameworkQueryableExtensions.cs) is “Specifies related entities to include in the query results.“ (in brief). So I did not expect…

jirikanda on Apr 17, 2018

After going through all the linked issues here

Global query filters are supposed to follow referential integrity constraint defined in the model. If each child object is required to have a parent then then all the child pointing to a parent which is soft-deleted must be soft-deleted. Otherwise it causes orphan children which according to the model is not possible.
We explored idea of always treating the relationship as optional when query filter but Left join can be perf expensive compared to inner join. Doing so in all cases feels like a penalty common cases have to pay to support a misuse of query filters.

Looking at this issue all the related issues,

User wants to apply query filters for some collection navigations & always ignore for reference navigations.
User does not have proper filters. i.e. Parent has filter but child does not have filter hence violating the RI.
User wants to ignore filters for some include but not for raw navigations.

Overall, global query filters are filters which will be applied to DbSet whenever querying for certain entity type. Further, referential integrity constraint should match model, with & without filter because model metadata is used to generate appropriate join. None of above scenarios are actual use case for global query filters.

If referential integrity constraint is not maintained then user is responsible for writing whatever is the required join to generate the result. EF Core cannot do that based on model. Hence for such scenarios navigations MUST not be used.
Applying filters for some navigations only would at best refer to mapping of a navigation property. E.g. if Car has a query filter but when querying for Manufacturer.Cars, it should not be used but when querying for Color.Cars, it should be used. It cannot be specified at entity level hence cannot use global query filter.
Adding a parameter to ignore query filter on include does not work since if user wants to ignore the query filter for some navigation then there is no API for that. Further different navigations can have different requirements about ignoring which goes back to point above.

Folks above who are facing issue or anyone new who arrives here with similar issue, consider using a different solution than global query filters. If the data cannot conform to model configuration about a relationship with & without query filter then it is not supported scenario with query filter. Consider using different refactoring, including manual joins for your case.

Marking this as closed by design.

smitpatel on Jun 10, 2020

@smitpatel - I’m facing this exact issue now in 3.1 also. We really need a way to control query filters on a per-Include basis. For example, assuming all entity types have a soft-delete query filter defined, this would be ideal:

db.Set<Car>() // Only non-deleted cars should come back.
  .Include(c => c.Manufacturer, ignoreQueryFilter: true) // ALWAYS include manufacturer (even if soft-deleted)!
  .Include(c => c.Features) // Only non-deleted features should come back.

In this example, the join onto Manufacturer would not contain the query filter condition. Is there any other way to achieve this behaviour at present?

jonesty on May 25, 2020

I have the same problem as @jirikanda and a similar use case. In my case I have an Archived property on the parent entity.

We have a view where we use IgnoreQueryFilters to explicitly view Archived parents. When we drill into these records we then cannot view the child records because we are including some information from the parent and so they get filtered out because they are archived.

We don’t want to IgnoreQueryFilters on the child collection to get around this because we may add additional Global Filters on the child collection. (In fact, side point, it would be helpful to be able to key global filters so that we ignore them individually , e.g. .IgnoreQueryFilters("Archived"))

hisuwh on Dec 13, 2018