entity-framework - EF 核心 Iqueryable。 Count() 返回的数字不同于 Iqueryable .ToList().Count()。这甚至可能还是一个错误？

Question

我有一个查询可以获取一些过滤后的数据，但它给了我一些奇怪的结果。使用 VS Code 调试器查看附件图像(var source 是一个 Queryable，类似于 _dbContext.ModelName)

var count= await source.CountAsync();给出的结果不同于var count2 = (await source.ToListAsync()).Count();

这怎么可能？有了这些结果，我自以为知道的关于 EF 的一切都变成了谎言。同步方法也是如此。

任何人都可以向我解释在哪种情况下这可能吗？这可能是 EF Core 3.1 中的错误吗？

程序上下文:副项目，数据库不被任何人访问，只有我一个人访问。本场景无其他操作

编辑:变量source有一个Include，所以它是 _dbContext.ModelName.Include(b=>b.其他模型)。 当我删除 Include 时，它​​起作用了。

edit2 ModelName.OtherModel属性在某些情况下为null，但是OtherModel.Id(主键)不能为null，所以，我想，当 Include 执行 Join 时，排除了没有 OtherModel 的 ModelName。可能是这个？

Answer 1

在正常情况下，参照完整性完好无损，这不会发生。看看下面的代码，其中两个计数操作都将正确返回 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);
        }
    }
}

这里也不可能破坏参照完整性，因为它受到数据库中外键约束的保护。

---

如果您自己创建数据库(使用定制的 SQL 脚本)并忽略外键约束，但仍然让 EF Core 相信存在一个，然后通过使用外键列中不存在 ID，您可以获得数据库端(此处为 3)和客户端(此处为 2)计数操作的不同结果:

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);
        }
    }
}