Entity Framework 6.x 学习之

一、Modeling a Many-to-Many Relationship with No Payload

1. 创建数据库表

CREATE TABLE [Album]
(
[AlbumId] [int] NOT NULL IDENTITY(1, 1),
[AlbumName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [Album] ADD CONSTRAINT [PK_Album] PRIMARY KEY CLUSTERED  ([AlbumId]) ON [PRIMARY]
GO

CREATE TABLE [Artist]
(
[ArtistId] [int] NOT NULL IDENTITY(1, 1),
[FirstName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
[MiddleName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
[LastName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [Artist] ADD CONSTRAINT [PK_Artist] PRIMARY KEY CLUSTERED  ([ArtistId]) ON [PRIMARY]
GO

CREATE TABLE [LinkTable]
(
[ArtistId] [int] NOT NULL,
[AlbumId] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [LinkTable] ADD CONSTRAINT [PK_LinkTable] PRIMARY KEY CLUSTERED  ([ArtistId], [AlbumId]) ON [PRIMARY]
GO
ALTER TABLE [LinkTable] ADD CONSTRAINT [FK_LinkTable_Album] FOREIGN KEY ([AlbumId]) REFERENCES [Album] ([AlbumId])
GO
ALTER TABLE [LinkTable] ADD CONSTRAINT [FK_LinkTable_Artist] FOREIGN KEY ([ArtistId]) REFERENCES [Artist] ([ArtistId])
GO

表关系图：

2. 创建实体模型，如图

备注：明明选了3个表，为什么只有2个实体呢？

因为中间那个表为连接表，所以在映射实体的时候只有2个实体，这种情况叫 no payload 。

为什么连接表不会被创建成一个实体呢？

因为他没有自己的字段，如果给链接表加一个字段，那么创建的实体模型就会和数据库表关系一样，一个一个的对应上。

3. 操作实体类

using System;

namespace ConsoleApplication3
{
    class Program
    {
        static void Main(string[] args)
        {
            //Insert
            using (var context = new test2Entities())
            {
                //添加一个艺术家属于2个专辑
                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);

                //添加一个专辑有2个艺术家
                var artist1 = new Artist { FirstName = "Tobby", LastName = "Keith" };
                var artist2 = new Artist { FirstName = "Merle", LastName = "Haggard" };
                var album = new Album { AlbumName = "Honkytonk University" };
                artist1.Albums.Add(album);
                artist2.Albums.Add(album);
                context.Albums.Add(album);

                context.SaveChanges();
            }

            //Select
            using (var context = new test2Entities())
            {
                Console.WriteLine("艺术家和他们的专辑...");
                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("	{0}", album.AlbumName);
                    }
                }

                Console.WriteLine("
专辑中的艺术家...");
                var albums = context.Albums;
                foreach (var album in albums)
                {
                    Console.WriteLine("{0}", album.AlbumName);
                    foreach (var artist in album.Artists)
                    {
                        Console.WriteLine("	{0} {1}", artist.FirstName, artist.LastName);
                    }
                }
            }
            Console.ReadKey();
        }
    }
}

输出：

程序运行3次后的数据库表结果：

二、Modeling a Many-to-Many Relationship with a Payload

1. 创建数据库表：

CREATE TABLE [Order]
(
[OrderId] [int] NOT NULL,
[OrderDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [Order] ADD CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED  ([OrderId]) ON [PRIMARY]
GO
CREATE TABLE [Item]
(
[SKU] [int] NOT NULL,
[Description] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[Price] [decimal] (18, 2) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [Item] ADD CONSTRAINT [PK_Item] PRIMARY KEY CLUSTERED  ([SKU]) ON [PRIMARY]
GO
CREATE TABLE [OrderItem]
(
[OrderId] [int] NOT NULL,
[SKU] [int] NOT NULL,
[Count] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [OrderItem] ADD CONSTRAINT [PK_OrderItem] PRIMARY KEY CLUSTERED  ([OrderId], [SKU]) ON [PRIMARY]
GO
ALTER TABLE [OrderItem] ADD CONSTRAINT [FK_OrderItem_Item] FOREIGN KEY ([SKU]) REFERENCES [Item] ([SKU])
GO
ALTER TABLE [OrderItem] ADD CONSTRAINT [FK_OrderItem_Order] FOREIGN KEY ([OrderId]) REFERENCES [Order] ([OrderId])
GO

表关系图：

创建模型图：

这回连接表有了实体了，为什么呢？因为OrderItem表有自己的字段Count

3. 操作实体类：

using System;

namespace ConsoleApplication4
{
    class Program
    {
        static void Main(string[] args)
        {
            //Add
            using (var context = new test2Entities())
            {
                var order = new Order { OrderId = 100, OrderDate = new DateTime(2010, 1, 18) };

                var item = new Item { SKU = 11729, Description = "Backpack", Price = 29.97M };
                var oi = new OrderItem { Order = order, Item = item, Count = 1 };
                context.Items.Add(item);
                context.OrderItems.Add(oi);

                item = new Item { SKU = 12929, Description = "Water Filter", Price = 13.97M };
                oi = new OrderItem { Order = order, Item = item, Count = 3 };
                context.Items.Add(item);
                context.OrderItems.Add(oi);

                item = new Item { SKU = 11847, Description = "Camp Stove", Price = 43.99M };
                oi = new OrderItem { Order = order, Item = item, Count = 1 };
                context.Items.Add(item);
                context.OrderItems.Add(oi);

                context.Orders.Add(order);
                context.SaveChanges();
            }

            //Select
            using (var context = new test2Entities())
            {
                foreach (var order in context.Orders)
                {
                    Console.WriteLine("Order # {0}, ordered on {1}", order.OrderId.ToString(), order.OrderDate.ToShortDateString());
                    Console.WriteLine("SKU	Description	Qty	Price");
                    Console.WriteLine("---	-----------	---	-----");
                    foreach (var oi in order.OrderItems)
                    {
                        Console.WriteLine("{0}	{1}	{2}	{3}", oi.Item.SKU, oi.Item.Description, oi.Count.ToString(), oi.Item.Price.ToString("C"));
                    }
                }
            }
            Console.ReadKey();
        }
    }
}