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