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

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