c# - 实体框架核心错误?
问题描述
如果这是 Entity Framework Core 中的错误,任何人都可以确认这一点吗?
SQL:
CREATE TABLE [dbo].[Users]
(
[CreatedById] [UNIQUEIDENTIFIER] NOT NULL,
[UpdatedById] [UNIQUEIDENTIFIER] NULL,
[Id] [UNIQUEIDENTIFIER] NOT NULL,
[Name] [NVARCHAR](150) NOT NULL,
CONSTRAINT [PK_Users]
PRIMARY KEY CLUSTERED ([Id] ASC)
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Users] WITH CHECK
ADD CONSTRAINT [FK_Users_Users_CreatedById]
FOREIGN KEY([CreatedById]) REFERENCES [dbo].[Users] ([Id])
GO
ALTER TABLE [dbo].[Users] WITH CHECK
ADD CONSTRAINT [FK_Users_Users_UpdatedById]
FOREIGN KEY([UpdatedById]) REFERENCES [dbo].[Users] ([Id])
GO
样本数据:
INSERT [dbo].[Users] ([CreatedById], [UpdatedById], [Id], [Name])
VALUES (N'2cfc1025-cf96-4929-a6a4-e2ed77a6ae12', N'2cfc1025-cf96-4929-a6a4-e2ed77a6ae12', N'2cfc1025-cf96-4929-a6a4-e2ed77a6ae12', N'Admin'),
(N'2cfc1025-cf96-4929-a6a4-e2ed77a6ae12', N'60ca72c4-db2d-4cea-9981-c10d6942a11e', N'60ca72c4-db2d-4cea-9981-c10d6942a11e', N'User 01'),
(N'60ca72c4-db2d-4cea-9981-c10d6942a11e', NULL, N'40719114-f53e-4d3f-9a1b-49f63b18002b', N'New User')
模型
[Table("Users")]
public partial class User
{
[Required]
public Guid Id { get; set; }
[Required]
[MaxLength(150)]
public string Name { get; set; }
[Required]
public User CreatedBy { get; set; }
public User UpdatedBy { get; set; }
[Required]
public Guid CreatedById { get; set; }
public Guid? UpdatedById { get; set; }
}
实体框架 6.2 ( .EDMX
) 代码:
using (var context = new Agap2TestEntities())
{
var test = context.Users.Include(p => p.User1).Include(p =>p.User2).ToList();
Console.WriteLine(test.Count);
}
生成的 SQL
SELECT
1 AS [C1],
[Extent1].[CreatedById] AS [CreatedById],
[Extent1].[UpdatedById] AS [UpdatedById],
[Extent1].[Id] AS [Id],
[Extent1].[Name] AS [Name],
[Extent2].[CreatedById] AS [CreatedById1],
[Extent2].[UpdatedById] AS [UpdatedById1],
[Extent2].[Id] AS [Id1],
[Extent2].[Name] AS [Name1],
[Extent3].[CreatedById] AS [CreatedById2],
[Extent3].[UpdatedById] AS [UpdatedById2],
[Extent3].[Id] AS [Id2],
[Extent3].[Name] AS [Name2]
FROM [dbo].[Users] AS [Extent1]
INNER JOIN [dbo].[Users] AS [Extent2] ON [Extent1].[CreatedById] = [Extent2].[Id]
LEFT OUTER JOIN [dbo].[Users] AS [Extent3] ON [Extent1].[UpdatedById] = [Extent3].[Id]
返回3条记录,没关系!
实体框架核心 2.1.4
public class ApplicationDbContext : DbContext
{
public DbSet<User> Users { get; set; }
public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options) {}
}
class Program
{
private static ApplicationDbContext CreateContext()
{
return new ApplicationDbContext(new DbContextOptionsBuilder<ApplicationDbContext>().UseSqlServer("Server=...").Options);
}
static void Main(string[] args)
{
using (var dbContext = CreateContext())
{
var test = dbContext.Set<User>().Include(p => p.CreatedBy).Include(p => p.UpdatedBy).ToList();
Console.WriteLine(test.Count);
}
}
}
生成的 Sql
SELECT [p].[Id], [p].[CreatedById], [p].[Name], [p].[UpdatedById], [p.UpdatedBy].[Id], [p.UpdatedBy].[CreatedById], [p.UpdatedBy].[Name], [p.UpdatedBy].[UpdatedById], [p.CreatedBy].[Id], [p.CreatedBy].[CreatedById], [p.CreatedBy].[Name], [p.CreatedBy].[UpdatedById]
FROM [Users] AS [p]
LEFT JOIN [Users] AS [p.UpdatedBy] ON [p].[Id] = [p.UpdatedBy].[CreatedById]
INNER JOIN [Users] AS [p.CreatedBy] ON [p].[CreatedById] = [p.CreatedBy].[Id]
返回4条记录,KO!
这行“LEFT JOIN [Users] AS [p.UpdatedBy] ON [p].[Id] = [p.UpdatedBy].[CreatedById]”是错误的!
有谁知道为什么会这样?
提前致谢!
解决方案
我无法重现。A 运行该 SQL 并从数据库中构建模型。查询
db.Set<Users>().Include(p => p.CreatedBy).Include(p => p.UpdatedBy).ToList();
正确翻译为
SELECT [p].[Id], [p].[CreatedById], [p].[Name], [p].[UpdatedById], [p.UpdatedBy].[Id], [p.UpdatedBy].[CreatedById], [p.UpdatedBy].[Name], [p.UpdatedBy].[UpdatedById], [p.CreatedBy].[Id], [p.CreatedBy].[CreatedById], [p.CreatedBy].[Name], [p.CreatedBy].[UpdatedById]
FROM [Users] AS [p]
LEFT JOIN [Users] AS [p.UpdatedBy] ON [p].[UpdatedById] = [p.UpdatedBy].[Id]
INNER JOIN [Users] AS [p.CreatedBy] ON [p].[CreatedById] = [p.CreatedBy].[Id]
这是生成的 DbContext:
using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;
namespace EfCoreTest
{
public partial class FooContext : DbContext
{
public FooContext()
{
}
public FooContext(DbContextOptions<FooContext> options)
: base(options)
{
}
public virtual DbSet<Users> Users { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
if (!optionsBuilder.IsConfigured)
{
optionsBuilder.UseSqlServer("server=.;database=Foo;Integrated Security=true");
}
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Users>(entity =>
{
entity.Property(e => e.Id).ValueGeneratedNever();
entity.Property(e => e.Name)
.IsRequired()
.HasMaxLength(150);
entity.HasOne(d => d.CreatedBy)
.WithMany(p => p.InverseCreatedBy)
.HasForeignKey(d => d.CreatedById)
.OnDelete(DeleteBehavior.ClientSetNull);
entity.HasOne(d => d.UpdatedBy)
.WithMany(p => p.InverseUpdatedBy)
.HasForeignKey(d => d.UpdatedById);
});
}
}
}
和生成的实体类:
using System;
using System.Collections.Generic;
namespace EfCoreTest
{
public partial class Users
{
public Users()
{
InverseCreatedBy = new HashSet<Users>();
InverseUpdatedBy = new HashSet<Users>();
}
public Guid CreatedById { get; set; }
public Guid? UpdatedById { get; set; }
public Guid Id { get; set; }
public string Name { get; set; }
public Users CreatedBy { get; set; }
public Users UpdatedBy { get; set; }
public ICollection<Users> InverseCreatedBy { get; set; }
public ICollection<Users> InverseUpdatedBy { get; set; }
}
}
推荐阅读
- scala - 如何在 2d 棋盘游戏 scala 中移动 n 个空格
- mysql - 在具有两个相同最大值的行中,更喜欢列值而不是另一个列值
- java - 显示在系统上运行的进程,但在所有计算机上,而不仅仅是在我的计算机上
- spring - 如何获取 Spring-cache 中缓存的大小?
- python - 正确编译的代码(没有错误)给出分段错误核心转储
- r - 根据列值在列表中删除数据框
- java - javac 版本和 java 版本在命令行上显示不同
- maven - 所选目录对 Tomcat 主目录无效:Windows 8.1
- encoding - Zapier 尝试在 Code 中编码 PDF 时出错
- javafx - Tableview 的第一列在 javafx 8 中占用了大量空间