Count() does not equal number of results if included navigation property does not match query filter

[tmthill]

If a query selects a property from a related entity reached by a required navigation property, but the related entity is excluded by a query filter, query.Count() will not match query.ToList().Count. Actualizing the results with ToList() will exclude the row from its results, as the related entity is filtered out, but Count() does not exclude the result even though the query is selecting a member of the navigation property explicitly.

We use this functionality to build search results one page at a time: build a filtered and projected IQueryable<TEntity> query, use query.Count() to get the total number of results, then query.OrderBy(orderBy).Skip(skip).Take(take).ToList() to resolve a subset of the results. Our actual implementation has a much more complicated projection thanks to AutoMapper, but as recently as EF Core 2.2.6, calling Count() on the queryable returned the same cardinality as the result set (indeed, the code generated for SQL Server has the same FROM and JOIN clauses and differs only in the SELECT lists).

Steps to reproduce

using Microsoft.EntityFrameworkCore;
using System.Linq;
using Xunit;

namespace EntityFrameworkCoreTests
{
    class Entity
    {
        public int EntityId { get; set; }
        public int RelatedEntityId { get; set; }
        public virtual RelatedEntity RelatedEntity { get; set; }
    }

    class RelatedEntity
    {
        public int RelatedEntityId { get; set; }
        public bool IsDeleted { get; set; }
        public string SomeProperty { get; set; }
    }

    class TestContext : DbContext
    {
        public TestContext(DbContextOptions<TestContext> options) : base(options) { }

        public DbSet<Entity> Entities { get; set; }
        public DbSet<RelatedEntity> RelatedEntities { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<RelatedEntity>()
                .HasQueryFilter(relatedEntity => !relatedEntity.IsDeleted);
        }
    }

    public class CountDoesNotMatchTests
    {
        [Fact]
        public void Queryable_Count_should_match_ToList_Count()
        {
            using var context = new TestContext(
                new DbContextOptionsBuilder<TestContext>()
                    .UseInMemoryDatabase(GetType().Name)
                    .Options);

            var entity = new Entity
            {
                RelatedEntity = new RelatedEntity
                {
                    IsDeleted = true
                }
            };

            context.Add(entity);
            context.SaveChanges();

            var query = context.Entities
                .AsNoTracking()
                .Select(e => e.RelatedEntity.SomeProperty);

            Assert.Equal(query.ToList().Count, query.Count());
            // query.ToList().Count is zero; query.Count() is one
            // As of EF Core 2.2.6, they were both zero
        }
    }
}

The code above uses InMemory for brevity, but Sqlite exhibits the same behavior and generates the following query for query.ToList(), returning no rows:

SELECT "t"."SomeProperty"
FROM "Entities" AS "e"
INNER JOIN (
    SELECT "r"."RelatedEntityId", "r"."IsDeleted", "r"."SomeProperty"
    FROM "RelatedEntities" AS "r"
    WHERE NOT ("r"."IsDeleted")
) AS "t" ON "e"."RelatedEntityId" = "t"."RelatedEntityId"

And for query.Count(), returning a count of 1:

SELECT COUNT(*)
FROM "Entities" AS "e"

I also noticed that, if I used .Include(e => e.RelatedEntity) instead of .Select(e => e.RelatedEntity.SomeProperty) in the test above, EF Core 2.2.6 and 3.1.1 both return 1 for query.Count() and 0 for query.ToList().Count. This is not currently causing us issues, but it looks similar yet behaves consistently across versions, so I thought it would be worth mentioning.

Further technical details

EF Core version: 3.1.1
Database provider: Microsoft.EntityFrameworkCore.InMemory 3.1.1, Microsoft.EntityFrameworkCore.Sqlite 3.1.1, Microsoft.EntityFrameworkCore.SqlServer 3.1.1
Target framework: .NET Core 3.1
Operating system: Windows 10
IDE: Visual Studio 2019 16.4.2

[ajcvickers]

Note for team: repros on master.

/cc @maumar @roji

[maumar]

We should be using left join here instead

[alirezanet]

I think I have the same problem #19764
so +1

[maumar]

discrepancy comes from the fact that when navigation rewrite processes Count, we have optimization in place which just applies count on the source directly, completely ignoring pending selector. Assumption is that the count should be the same.

However, if the projection contains required navigation, and the entity that is on the other side of that navigation is filtered out by query filter, we remove the source to maintain referential integrity.

[maumar]

Filed #19801 to introduce model validation for this case as it seems counter-intuitive and can lead to issues.