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

[jirikanda]

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

1. Install NuGet packages
2. Create database IssueDemo (or change connection string)
3. Generate database migration
4. 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

[ajcvickers]

@jirikanda We discussed this in triage and decided we needed some more information on what you are trying to do. It seems like the case is this:

• There is a relationship in your model that says every Child has a Master
• There is a query filter that filters out some Masters
• A query is executed that queries explicitly for a Child when its Master would have been filtered out

In the case where there is no Include, this results in a Child being returned even though there its Master would never be returned. This basically violates the relationship defined between Child and Master.

In the case where the Include is used, the results could be considered correct, because the filter says that that Master should not be returned, and hence by the definition of the relationship there cannot be any Child with a reference to it.

So, it seems like the problem here is that the combination of a filter on the Master and a query directly against a Child bypassing the Master is resulting in the data not matching what the model says. At least, that's one interpretation. We would like to know how you are thinking about the scenario so that we can try to compare our expectations to yours and decide on a plan of action.

[jirikanda]

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…

[ajcvickers]

@jirikanda Thanks for the detailed description. This is very useful.

[ajcvickers]

Triage: we will fix this post 2.1 by updating the query generation to not use inner join in this case--that is, when principal has a filter.

@jirikanda For now, if you make the relationship optional, then it should use a left join. You might also look into using a filter that uses a navigation property to make the behavior consistent between the Include and no Include case--see #8881

[f135ta]

Could this be linked to issue #8576?