EF Core Queryable<T>. Count() returns different number than Queryable<T> .ToList().Count(). Is this even possible or is it a bug?

Question

I have a query that grabs some filtered data, but it's giving me some strange results. See the attached image with the VS Code debugger (the var sourceis a Queryable, something like _dbContext.ModelName)

var count= await source.CountAsync(); is giving a different result than var count2 = (await source.ToListAsync()).Count();

How is this even possible? With these results, everything I thought I knew about EF becomes a lie. The same is true for the sync methods.

Can anyone explain to me in which scenario is this possible? Could it be a bug in EF Core 3.1?

Context of the program: side project, DataBase is not accessed by anyone, just by me. There is no other operations in this scenario

edit : the variable source has an Include, so it is _dbContext.ModelName.Include(b=>b.OtherModel). When I remove the Include, it works.

edit2 The ModelName.OtherModel property is null in some cases, but OtherModel.Id (the primary key) cannot be null, so, I guess, when Include performs the Join, excludes the occurrences of ModelName that haven't an OtherModel. Could be this?

Answer 1

Under normal circumstances, with referential integrity intact, this cannot happen. Take a look at the following code, where both count operations will correctly return a result of 3:

using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace IssueConsoleTemplate
{
    public class IceCream
    {
        public int IceCreamId { get; set; }
        public string Name { get; set; }
        public int IceCreamBrandId { get; set; }

        public IceCreamBrand Brand { get; set; }
    }

    public class IceCreamBrand
    {
        public int IceCreamBrandId { get; set; }
        public string Name { get; set; }
                
        public virtual ICollection<IceCream> IceCreams { get; set; } = new HashSet<IceCream>();
    }

    public class Context : DbContext
    {
        public DbSet<IceCream> IceCreams { get; set; }
        public DbSet<IceCreamBrand> IceCreamBrands { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder
                .UseMySql(
                    "server=127.0.0.1;port=3306;user=root;password=;database=So63071963",
                    b => b.ServerVersion("8.0.20-mysql"))
                //.UseSqlServer(@"Data Source=.\MSSQL14;Integrated Security=SSPI;Initial Catalog=So63071963")
                .UseLoggerFactory(
                    LoggerFactory.Create(
                        b => b
                            .AddConsole()
                            .AddFilter(level => level >= LogLevel.Information)))
                .EnableSensitiveDataLogging()
                .EnableDetailedErrors();
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<IceCream>()
                .HasData(
                    new IceCream {IceCreamId = 1, Name = "Vanilla", IceCreamBrandId = 1},
                    new IceCream {IceCreamId = 2, Name = "Chocolate", IceCreamBrandId = 2},
                    new IceCream {IceCreamId = 3, Name = "Matcha", IceCreamBrandId = 3});

            modelBuilder.Entity<IceCreamBrand>()
                .HasData(
                    new IceCreamBrand {IceCreamBrandId = 1, Name = "My Brand"},
                    new IceCreamBrand {IceCreamBrandId = 2, Name = "Your Brand"},
                    new IceCreamBrand {IceCreamBrandId = 3, Name = "Our Brand"});
        }
    }
    
    internal static class Program
    {
        private static void Main()
        {
            //
            // Operations with referential integrity intact:
            //
            
            using var context = new Context();

            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            // Does not use INNER JOIN. Directly uses COUNT(*) on `IceCreams`:
            // SELECT COUNT(*)
            // FROM `IceCreams` AS `i`
            var databaseSideCount = context.IceCreams
                .Include(s => s.Brand)
                .Count();
            
            // Does use INNER JOIN. Counts using Linq:
            // SELECT `i`.`IceCreamId`, `i`.`IceCreamBrandId`, `i`.`Name`, `i0`.`IceCreamBrandId`, `i0`.`Name`
            // FROM `IceCreams` AS `i`
            // INNER JOIN `IceCreamBrands` AS `i0` ON `i`.`IceCreamBrandId` = `i0`.`IceCreamBrandId`
            var clientSideCount = context.IceCreams
                .Include(s => s.Brand)
                .AsEnumerable() // or ToList() etc.
                .Count();

            Debug.Assert(databaseSideCount == 3);
            Debug.Assert(clientSideCount == 3);
            Debug.Assert(databaseSideCount == clientSideCount);
        }
    }
}

Here it is also not possible to damage the referential integrity, because it is guarded by a foreign key constraint in the database.

---

If you create your database on your own however (using a custom crafted SQL script) and leave out the foreign key constraint, but still let EF Core believe that there is one in place, and then violate the referential integrity by using a non existing ID in a foreign key column, you can get different results for database-side (here 3) and client-side (here 2) count operations:

using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace IssueConsoleTemplate
{
    public class IceCream
    {
        public int IceCreamId { get; set; }
        public string Name { get; set; }
        public int IceCreamBrandId { get; set; }

        public IceCreamBrand Brand { get; set; }
    }

    public class IceCreamBrand
    {
        public int IceCreamBrandId { get; set; }
        public string Name { get; set; }
                
        public virtual ICollection<IceCream> IceCreams { get; set; } = new HashSet<IceCream>();
    }

    public class Context : DbContext
    {
        public DbSet<IceCream> IceCreams { get; set; }
        public DbSet<IceCreamBrand> IceCreamBrands { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder
                .UseMySql(
                    "server=127.0.0.1;port=3306;user=root;password=;database=So63071963",
                    b => b.ServerVersion("8.0.20-mysql"))
                //.UseSqlServer(@"Data Source=.\MSSQL14;Integrated Security=SSPI;Initial Catalog=So63071963")
                .UseLoggerFactory(
                    LoggerFactory.Create(
                        b => b
                            .AddConsole()
                            .AddFilter(level => level >= LogLevel.Information)))
                .EnableSensitiveDataLogging()
                .EnableDetailedErrors();
        }
    }
    
    internal static class Program
    {
        private static void Main()
        {
            //
            // Operations with referential integrity violated:
            //
            
            using var context = new Context();

            // Manually create MySQL database with a missing reference between
            // the Matcha ice cream and any brand.
            context.Database.ExecuteSqlRaw(
                @"
DROP DATABASE IF EXISTS `So63071963`;
CREATE DATABASE `So63071963`;
USE `So63071963`;

CREATE TABLE `IceCreamBrands` (
    `IceCreamBrandId` int NOT NULL AUTO_INCREMENT,
    `Name` longtext CHARACTER SET utf8mb4 NULL,
    CONSTRAINT `PK_IceCreamBrands` PRIMARY KEY (`IceCreamBrandId`)
);

CREATE TABLE `IceCreams` (
    `IceCreamId` int NOT NULL AUTO_INCREMENT,
    `Name` longtext CHARACTER SET utf8mb4 NULL,
    `IceCreamBrandId` int NOT NULL,
    CONSTRAINT `PK_IceCreams` PRIMARY KEY (`IceCreamId`)
);

INSERT INTO `IceCreamBrands` (`IceCreamBrandId`, `Name`) VALUES (1, 'My Brand');
INSERT INTO `IceCreamBrands` (`IceCreamBrandId`, `Name`) VALUES (2, 'Your Brand');

INSERT INTO `IceCreams` (`IceCreamId`, `IceCreamBrandId`, `Name`) VALUES (1, 1, 'Vanilla');
INSERT INTO `IceCreams` (`IceCreamId`, `IceCreamBrandId`, `Name`) VALUES (2, 2, 'Chocolate');

 /* Use non-existing brand id 0: */
INSERT INTO `IceCreams` (`IceCreamId`, `IceCreamBrandId`, `Name`) VALUES (3, 0, 'Matcha');
");

            // Does not use INNER JOIN. Directly uses COUNT(*) on `IceCreams`:
            // SELECT COUNT(*)
            // FROM `IceCreams` AS `i`
            var databaseSideCount = context.IceCreams
                .Include(s => s.Brand)
                .Count();
            
            // Does use INNER JOIN. Counts using Linq:
            // SELECT `i`.`IceCreamId`, `i`.`IceCreamBrandId`, `i`.`Name`, `i0`.`IceCreamBrandId`, `i0`.`Name`
            // FROM `IceCreams` AS `i`
            // INNER JOIN `IceCreamBrands` AS `i0` ON `i`.`IceCreamBrandId` = `i0`.`IceCreamBrandId`
            var clientSideCount = context.IceCreams
                .Include(s => s.Brand)
                .AsEnumerable() // or ToList() etc.
                .Count();

            Debug.Assert(databaseSideCount == 3);
            Debug.Assert(clientSideCount == 2);
            Debug.Assert(databaseSideCount != clientSideCount);
        }
    }
}