问题:
数据库中的2张表通过一张链接(link)表或交叉(junction)表进行关联。在这个链接表中仅有2个字段,这2个字段组成了表的主键,并且分别是另外2个表的外键。现在我们需要将其导入到EDM中,以建模这个多对多的关系。
解决方案:
数据库图表如下:
数据库脚本如下:
use [EF6Recipes] go create table Chapter2.Album( AlbumId int identity primary key, AlbumName varchar(50) not null); create table Chapter2.Artist( ArtistId int identity primary key, FirstName varchar(50) not null, MiddleName varchar(50), LastName varchar(50) not null); create table Chapter2.LinkTable( AlbumId int foreign key references Chapter2.Album(AlbumId), ArtistId int foreign key references Chapter2.Artist(ArtistId) constraint PK_LinkTable primary key (AlbumId,ArtistId) );
操作步骤同EF6 秘籍 2th:实体数据建模基础 (五)从现有数据库生成模型一致,不再重复。
生成的EDM如图所示:
值得一提的是,在多对多关系中,2个实体的导航属性都是EntityCollection。
原理:
在EDM中,艺人(Artist)可能发行了多张专辑(Album),一张专辑也可能由多个艺人一起发行。然而数据库图表中的LinkTable表并没有被转换成实体,这是因为它没有标量属性,也就是所谓纯的,EF认为它唯一的目的就是关联Album和Artist表。如果它存在标量属性的话,EF将产生另外的模型。
下面的代码将演示对模型的操作。在此之前我们需要重命名EDM的实体容器名称为:EF6RecipesContext;并将数据库架构改为:Chapter2;
using (var context = new EF6RecipesContext()) { //add an artist with two albums var artist = new Artist { FirstName = "Alan", LastName = "Jackson" }; var album1 = new Album { AlbumName = "Drive" }; var album2 = new Album { AlbumName = "Live at Texas Stadium" }; artist.Albums.Add(album1); artist.Albums.Add(album2); context.Artists.Add(artist); //add an album for two artists var artist1 = new Artist { FirstName = "Tobby", LastName = "Keith" }; var artist2 = new Artist { FirstName = "Merle", LastName = "Haggard" }; var album = new Album { AlbumName = "Honkytonk University" }; album.Artists.Add(artist1); album.Artists.Add(artist2); context.Albums.Add(album); context.SaveChanges(); } using (var context = new EF6RecipesContext()) { Console.WriteLine("Artists and their albums..."); var artists = context.Artists; foreach (var artist in artists) { Console.WriteLine("{0} {1}", artist.FirstName, artist.LastName); foreach (var album in artist.Albums) { Console.WriteLine("\t{0}", album.AlbumName); } } Console.WriteLine("\nAlbums and their artists..."); var albums = context.Albums; foreach (var album in albums) { Console.WriteLine("{0}", album.AlbumName); foreach (var artist in album.Artists) { Console.WriteLine("\t{0} {1}", artist.FirstName, artist.LastName); } } }
输出结果如下:
由于多对多关系中,2个实体相互关联,在具体使用那个实体实例为主,添加另一个实体实例到导航属性,其实没有多大关系,上例中其实展示了2种添加方式。一般来说,根据场景实际情况来编写更有意义。