本文的代码基于.NET Core 3.0和EF Core 3.0
有时候在数据库设计中，一个表自己会和自己是多对多关系。
在SQL Server数据库中，现在我们有Person表，代表一个人，建表语句如下：
CREATE TABLE [dbo].[Person](
[PersonID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[Age] [int] NULL,
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
(
[PersonID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
其中PersonID列是Person表的主键。
因为一个人会有多个朋友，所以实际上这种人与人之间的朋友关系，是Person表自己和自己的多对多关系，所以我们还要建立一张FriendRelation表，来表示Person表自身的多对多关系，FriendRelation表的建表语句如下：
CREATE TABLE [dbo].[FriendRelation](
[FriendRelationID] [int] IDENTITY(1,1) NOT NULL,
[FromPerson] [int] NULL,
[ToPerson] [int] NULL,
[Remark] [nvarchar](100) NULL,
CONSTRAINT [PK_FriendRelation] PRIMARY KEY CLUSTERED
(
[FriendRelationID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO ALTER TABLE [dbo].[FriendRelation] WITH CHECK ADD CONSTRAINT [FK_FriendRelation_Person_From] FOREIGN KEY([FromPerson])
REFERENCES [dbo].[Person] ([PersonID])
GO ALTER TABLE [dbo].[FriendRelation] CHECK CONSTRAINT [FK_FriendRelation_Person_From]
GO ALTER TABLE [dbo].[FriendRelation] WITH CHECK ADD CONSTRAINT [FK_FriendRelation_Person_To] FOREIGN KEY([ToPerson])
REFERENCES [dbo].[Person] ([PersonID])
GO ALTER TABLE [dbo].[FriendRelation] CHECK CONSTRAINT [FK_FriendRelation_Person_To]
GO
其中FriendRelationID列是FriendRelation表的主键，我们可以看到在FriendRelation表中有两个外键关系：
- 外键关系[FK_FriendRelation_Person_From]，通过FriendRelation表的外键列[FromPerson]，关联到Person表的主键列PersonID
- 外键关系[FK_FriendRelation_Person_To]，通过FriendRelation表的外键列[ToPerson]，关联到Person表的主键列PersonID
因此Person表每行数据之间的多对多关系，就通过FriendRelation表的[FromPerson]列和[ToPerson]列建立起来了。
接下来，我们使用EF Core的DB First模式，通过Scaffold-DbContext指令，来生成实体类和DbContext类。
生成Person实体类如下：
using System;
using System.Collections.Generic; namespace EFCoreSelfMany.Entities
{
public partial class Person
{
public Person()
{
FriendRelationFromPersonNavigation = new HashSet<FriendRelation>();
FriendRelationToPersonNavigation = new HashSet<FriendRelation>();
} public int PersonId { get; set; }
public string Name { get; set; }
public int? Age { get; set; } public virtual ICollection<FriendRelation> FriendRelationFromPersonNavigation { get; set; }
public virtual ICollection<FriendRelation> FriendRelationToPersonNavigation { get; set; }
}
}
可以看到EF Core在实体类Person中生成了两个属性：
- FriendRelationFromPersonNavigation属性，对应了FriendRelation表的外键列[FromPerson]
- FriendRelationToPersonNavigation属性，对应了FriendRelation表的外键列[ToPerson]
所以通过这两个属性我们就能知道一个人有哪些朋友。
生成FriendRelation实体类如下:
using System;
using System.Collections.Generic; namespace EFCoreSelfMany.Entities
{
public partial class FriendRelation
{
public int FriendRelationId { get; set; }
public int? FromPerson { get; set; }
public int? ToPerson { get; set; }
public string Remark { get; set; } public virtual Person FromPersonNavigation { get; set; }
public virtual Person ToPersonNavigation { get; set; }
}
}
可以看到EF Core在实体类FriendRelation中也生成了两个属性：
- FromPersonNavigation属性，对应了FriendRelation表的外键列[FromPerson]
- ToPersonNavigation属性，对应了FriendRelation表的外键列[ToPerson]
所以通过这两个属性，我们可以知道一个朋友关系中的两个人（Person表）到底是谁。
最后我们来看看，生成的DbContext类DemoDBContext：
using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata; namespace EFCoreSelfMany.Entities
{
public partial class DemoDBContext : DbContext
{
public DemoDBContext()
{
} public DemoDBContext(DbContextOptions<DemoDBContext> options)
: base(options)
{
} public virtual DbSet<FriendRelation> FriendRelation { get; set; }
public virtual DbSet<Person> Person { get; set; } protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
if (!optionsBuilder.IsConfigured)
{
optionsBuilder.UseSqlServer("Server=localhost;User Id=sa;Password=Dtt!123456;Database=DemoDB"); optionsBuilder.UseLoggerFactory(new EFLoggerFactory());
}
} protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<FriendRelation>(entity =>
{
entity.Property(e => e.FriendRelationId).HasColumnName("FriendRelationID"); entity.Property(e => e.Remark).HasMaxLength(); entity.HasOne(d => d.FromPersonNavigation)
.WithMany(p => p.FriendRelationFromPersonNavigation)
.HasForeignKey(d => d.FromPerson)
.HasConstraintName("FK_FriendRelation_Person_From"); entity.HasOne(d => d.ToPersonNavigation)
.WithMany(p => p.FriendRelationToPersonNavigation)
.HasForeignKey(d => d.ToPerson)
.HasConstraintName("FK_FriendRelation_Person_To");
}); modelBuilder.Entity<Person>(entity =>
{
entity.Property(e => e.PersonId).HasColumnName("PersonID"); entity.Property(e => e.Name).HasMaxLength();
}); OnModelCreatingPartial(modelBuilder);
} partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
}
}
可以看到在实体类FriendRelation的Fluent API中（黄色高亮部分），设置了Person实体类自己与自己的多对多关系。
然后我们在.NET Core控制台项目中，写了几个方法来做测试：
- ClearTables方法，用于清空Person表和FriendRelation表的数据
- InsertPersonAndFriend方法，用于插入数据到Person表和FriendRelation表
- ShowFriend方法，用于显示Person表数据"张三"的朋友
- DeleteFriend方法，用于删除FriendRelation表数据
代码如下所示：
using EFCoreSelfMany.Entities;
using System;
using Microsoft.EntityFrameworkCore;
using System.Linq; namespace EFCoreSelfMany
{
class Program
{
//清空Person表和FriendRelation表的数据
public static void ClearTables()
{
using (var dbContext = new DemoDBContext())
{
string sql = @"DELETE FROM [dbo].[FriendRelation];
DELETE FROM [dbo].[Person];"; //注意在EF Core 3.0中ExecuteSqlCommand方法已经过时，请用下面的ExecuteSqlRaw方法替代
dbContext.Database.ExecuteSqlRaw(sql);
}
} //插入数据到Person表和FriendRelation表
public static void InsertPersonAndFriend()
{
using (var dbContext = new DemoDBContext())
{
//插入Person表数据"张三"
Person personZhangSan = new Person()
{
Name = "张三",
Age =
}; //插入Person表数据"李四"
Person personLiSi = new Person()
{
Name = "李四",
Age =
}; //插入FriendRelation表数据，设置"张三"和"李四"为朋友，注意"张三"是FriendRelation实体类的FromPersonNavigation属性，"李四"是FriendRelation实体类的ToPersonNavigation属性
FriendRelation friendRelation = new FriendRelation()
{
FromPersonNavigation = personZhangSan,
ToPersonNavigation = personLiSi
}; dbContext.Person.Add(personZhangSan);
dbContext.Person.Add(personLiSi);
dbContext.FriendRelation.Add(friendRelation); dbContext.SaveChanges();
} Console.WriteLine("张三 和 李四 已经添加到数据库");
} //显示Person表数据"张三"的朋友
public static void ShowFriend()
{
using (var dbContext = new DemoDBContext())
{
//从数据库Person表中找出"张三"，并且使用EF Core的预加载(Eager Loading)，通过Person实体类的FriendRelationFromPersonNavigation属性查询出FriendRelation表的数据，从而找出"张三"的朋友
//注意，因为"张三"是通过FriendRelation实体类的FromPersonNavigation属性添加到数据库FriendRelation表的，所以这里使用EF Core的预加载(Eager Loading)方法Include时，要使用Person实体类的FriendRelationFromPersonNavigation属性，最后通过FriendRelation实体类的ToPersonNavigation属性从Person表中找出"李四"
var personZhangSan = dbContext.Person.Where(p => p.Name == "张三").Include(p => p.FriendRelationFromPersonNavigation).ThenInclude(f => f.ToPersonNavigation).First(); //判断"张三"是否有朋友
if (personZhangSan.FriendRelationFromPersonNavigation.Count > )
{
Console.WriteLine($"{personZhangSan.Name} 的朋友是 {personZhangSan.FriendRelationFromPersonNavigation.First().ToPersonNavigation.Name}");
}
else
{
Console.WriteLine($"{personZhangSan.Name} 没有朋友");
}
}
} //删除FriendRelation表数据
public static void DeleteFriend()
{
using (var dbContext = new DemoDBContext())
{
//从数据库Person表中找出"张三"，并且使用EF Core的预加载(Eager Loading)，通过Person实体类的FriendRelationFromPersonNavigation属性查询出FriendRelation表的数据
var personZhangSan = dbContext.Person.Where(p => p.Name == "张三").Include(p => p.FriendRelationFromPersonNavigation).First();
var friendRelation = personZhangSan.FriendRelationFromPersonNavigation.First(); //从FriendRelation表中删除数据，也就是删除"张三"和"李四"的朋友关系
dbContext.FriendRelation.Remove(friendRelation);
dbContext.SaveChanges(); Console.WriteLine($"{personZhangSan.Name} 删除了朋友");
}
} static void Main(string[] args)
{
ClearTables(); InsertPersonAndFriend(); ShowFriend(); DeleteFriend(); ShowFriend(); Console.WriteLine("按任意键结束...");
Console.ReadKey();
}
}
}
当代码执行完Program类Main方法中的InsertPersonAndFriend方法后，EF Core后台生成的日志如下：
=============================== EF Core log started ===============================
Executed DbCommand (123ms) [Parameters=[@p0='?' (DbType = Int32), @p1='?' (Size = 50)], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
INSERT INTO [Person] ([Age], [Name])
VALUES (@p0, @p1);
SELECT [PersonID]
FROM [Person]
WHERE @@ROWCOUNT = 1 AND [PersonID] = scope_identity();
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
Executed DbCommand (18ms) [Parameters=[@p0='?' (DbType = Int32), @p1='?' (Size = 50)], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
INSERT INTO [Person] ([Age], [Name])
VALUES (@p0, @p1);
SELECT [PersonID]
FROM [Person]
WHERE @@ROWCOUNT = 1 AND [PersonID] = scope_identity();
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
Executed DbCommand (19ms) [Parameters=[@p2='?' (DbType = Int32), @p3='?' (Size = 100), @p4='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
INSERT INTO [FriendRelation] ([FromPerson], [Remark], [ToPerson])
VALUES (@p2, @p3, @p4);
SELECT [FriendRelationID]
FROM [FriendRelation]
WHERE @@ROWCOUNT = 1 AND [FriendRelationID] = scope_identity();
=============================== EF Core log finished ===============================
可以看到InsertPersonAndFriend方法中，EF Core一共执行了三段SQL语句，前面两段SQL就是在Person表中插入了"张三"和"李四"两行数据，最后一段SQL就是在FriendRelation表中插入了"张三"和"李四"的朋友关系数据。
执行完Program类Main方法中的InsertPersonAndFriend方法后，数据库Person表记录如下：
数据库FriendRelation表记录如下：
控制台输出结果如下：
当代码执行完Program类Main方法中的第一个ShowFriend方法后，EF Core后台生成的日志如下：
=============================== EF Core log started ===============================
Executed DbCommand (13ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [t].[PersonID], [t].[Age], [t].[Name], [t0].[FriendRelationID], [t0].[FromPerson], [t0].[Remark], [t0].[ToPerson], [t0].[PersonID], [t0].[Age], [t0].[Name]
FROM (
SELECT TOP(1) [p].[PersonID], [p].[Age], [p].[Name]
FROM [Person] AS [p]
WHERE ([p].[Name] = N'张三') AND [p].[Name] IS NOT NULL
) AS [t]
LEFT JOIN (
SELECT [f].[FriendRelationID], [f].[FromPerson], [f].[Remark], [f].[ToPerson], [p0].[PersonID], [p0].[Age], [p0].[Name]
FROM [FriendRelation] AS [f]
LEFT JOIN [Person] AS [p0] ON [f].[ToPerson] = [p0].[PersonID]
) AS [t0] ON [t].[PersonID] = [t0].[FromPerson]
ORDER BY [t].[PersonID], [t0].[FriendRelationID]
=============================== EF Core log finished ===============================
可以看到EF Core生成了SQL语句，将"张三"和其朋友的数据都从Person表和FriendRelation表查询出来了。
控制台输出结果如下：
当代码执行完Program类Main方法中的DeleteFriend方法后，EF Core后台生成的日志如下：
=============================== EF Core log started ===============================
Executed DbCommand (28ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [t].[PersonID], [t].[Age], [t].[Name], [f].[FriendRelationID], [f].[FromPerson], [f].[Remark], [f].[ToPerson]
FROM (
SELECT TOP(1) [p].[PersonID], [p].[Age], [p].[Name]
FROM [Person] AS [p]
WHERE ([p].[Name] = N'张三') AND [p].[Name] IS NOT NULL
) AS [t]
LEFT JOIN [FriendRelation] AS [f] ON [t].[PersonID] = [f].[FromPerson]
ORDER BY [t].[PersonID], [f].[FriendRelationID]
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
Executed DbCommand (15ms) [Parameters=[@p0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
DELETE FROM [FriendRelation]
WHERE [FriendRelationID] = @p0;
SELECT @@ROWCOUNT;
=============================== EF Core log finished ===============================
可以看到EF Core生成了两段SQL语句，第一段SQL是通过"张三"找出FriendRelation表的数据，第二段SQL是将找出的FriendRelation表数据进行了删除。
执行完Program类Main方法中的DeleteFriend方法后，数据库FriendRelation表记录如下：
控制台输出结果如下：
当代码执行完Program类Main方法中的第二个ShowFriend方法后，控制台输出结果如下：
所以我们可以看到，EF Core是支持数据库表自己与自己多对多关系的实体类映射的，当实体类生成好后，其使用方法和普通的多对多关系差不多，没有太大的区别。