Entity Framework Code First添加修改及删除外键关联实体

1、添加外键关联实体

　　1>、添加新的Province及City实体

using (var ctx = new PortalContext()){    var city1 = new City    {        CityNo = "10010",        CityName = "测试城市1"    };    var city2 = new City    {        CityNo = "10020",        CityName = "测试城市2"    };    var province = new Province    {        ProvinceNo = "10000",        ProvinceName = "测试省份"    };    province.Cities.Add(city1);    province.Cities.Add(city2);    ctx.Provinces.Add(province);    ctx.SaveChanges();}

　　代码运行所执行的SQL语句：

exec sp_executesql N'insert [dbo].[Province]([ProvinceNo], [ProvinceName])values (@0, @1)select [ProvinceID]from [dbo].[Province]where @@ROWCOUNT > 0 and [ProvinceID] = scope_identity()',N'@0 nvarchar(10),@1 nvarchar(50)',@0=N'10000',@1=N'测试省份'

exec sp_executesql N'insert [dbo].[City]([ProvinceID], [CityNo], [CityName])values (@0, @1, @2)select [CityID]from [dbo].[City]where @@ROWCOUNT > 0 and [CityID] = scope_identity()',N'@0 int,@1 nvarchar(10),@2 nvarchar(50)',@0=39,@1=N'10010',@2=N'测试城市1'

exec sp_executesql N'insert [dbo].[City]([ProvinceID], [CityNo], [CityName])values (@0, @1, @2)select [CityID]from [dbo].[City]where @@ROWCOUNT > 0 and [CityID] = scope_identity()',N'@0 int,@1 nvarchar(10),@2 nvarchar(50)',@0=39,@1=N'10020',@2=N'测试城市2'

　　2>、添加新的Province实体及现有的City实体

using (var ctx = new PortalContext()){    var city1 = new City    {        CityNo = "10010",        CityName = "测试城市1"    };    var city2 = new City    {        CityNo = "10020",        CityName = "测试城市2"    };    var city3 = ctx.Cities.Find(36);    var province = new Province    {        ProvinceNo = "10000",        ProvinceName = "测试省份"    };    province.Cities.Add(city1);    province.Cities.Add(city2);    province.Cities.Add(city3);    ctx.Provinces.Add(province);    ctx.SaveChanges();}

　　代码运行所执行的SQL语句：

exec sp_executesql N'SELECT [Limit1].[CityID] AS [CityID], [Limit1].[ProvinceID] AS [ProvinceID], [Limit1].[CityNo] AS [CityNo], [Limit1].[CityName] AS [CityName]FROM ( SELECT TOP (2)     [Extent1].[CityID] AS [CityID],     [Extent1].[ProvinceID] AS [ProvinceID],     [Extent1].[CityNo] AS [CityNo],     [Extent1].[CityName] AS [CityName]    FROM [dbo].[City] AS [Extent1]    WHERE [Extent1].[CityID] = @p0)  AS [Limit1]',N'@p0 int',@p0=36

exec sp_executesql N'insert [dbo].[Province]([ProvinceNo], [ProvinceName])values (@0, @1)select [ProvinceID]from [dbo].[Province]where @@ROWCOUNT > 0 and [ProvinceID] = scope_identity()',N'@0 nvarchar(10),@1 nvarchar(50)',@0=N'10000',@1=N'测试省份'

exec sp_executesql N'update [dbo].[City]set [ProvinceID] = @0where ([CityID] = @1)',N'@0 int,@1 int',@0=40,@1=36

exec sp_executesql N'insert [dbo].[City]([ProvinceID], [CityNo], [CityName])values (@0, @1, @2)select [CityID]from [dbo].[City]where @@ROWCOUNT > 0 and [CityID] = scope_identity()',N'@0 int,@1 nvarchar(10),@2 nvarchar(50)',@0=40,@1=N'10010',@2=N'测试城市1'

exec sp_executesql N'insert [dbo].[City]([ProvinceID], [CityNo], [CityName])values (@0, @1, @2)select [CityID]from [dbo].[City]where @@ROWCOUNT > 0 and [CityID] = scope_identity()',N'@0 int,@1 nvarchar(10),@2 nvarchar(50)',@0=40,@1=N'10020',@2=N'测试城市2'

　　2、修改外键关联实体

　　1>、方式1

using (var ctx = new PortalContext()){    var city = ctx.Cities.Find(40);    var province = ctx.Provinces.Find(10);    city.Province = province;    ctx.SaveChanges();}

　　代码运行所执行的SQL语句：

exec sp_executesql N'SELECT [Limit1].[CityID] AS [CityID], [Limit1].[ProvinceID] AS [ProvinceID], [Limit1].[CityNo] AS [CityNo], [Limit1].[CityName] AS [CityName]FROM ( SELECT TOP (2)     [Extent1].[CityID] AS [CityID],     [Extent1].[ProvinceID] AS [ProvinceID],     [Extent1].[CityNo] AS [CityNo],     [Extent1].[CityName] AS [CityName]    FROM [dbo].[City] AS [Extent1]    WHERE [Extent1].[CityID] = @p0)  AS [Limit1]',N'@p0 int',@p0=40

exec sp_executesql N'SELECT [Limit1].[ProvinceID] AS [ProvinceID], [Limit1].[ProvinceNo] AS [ProvinceNo], [Limit1].[ProvinceName] AS [ProvinceName]FROM ( SELECT TOP (2)     [Extent1].[ProvinceID] AS [ProvinceID],     [Extent1].[ProvinceNo] AS [ProvinceNo],     [Extent1].[ProvinceName] AS [ProvinceName]    FROM [dbo].[Province] AS [Extent1]    WHERE [Extent1].[ProvinceID] = @p0)  AS [Limit1]',N'@p0 int',@p0=10

exec sp_executesql N'update [dbo].[City]set [ProvinceID] = @0where ([CityID] = @1)',N'@0 int,@1 int',@0=10,@1=40

　　2>、方式2

using (var ctx = new PortalContext()){    var city = ctx.Cities.Find(41);    var province = ctx.Provinces.Find(10);    province.Cities.Add(city);    ctx.SaveChanges();}

　　方式2的实现方式与方式1的实现方式区别：在执行province.Cities.Add(city)时，会自动调用延迟加载，多执行一次从数据库中根据Province关联获取City的SQL语句。

exec sp_executesql N'SELECT [Extent1].[CityID] AS [CityID], [Extent1].[ProvinceID] AS [ProvinceID], [Extent1].[CityNo] AS [CityNo], [Extent1].[CityName] AS [CityName]FROM [dbo].[City] AS [Extent1]WHERE [Extent1].[ProvinceID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=10

　　3>、方式3

using (var ctx = new PortalContext()){    var city = ctx.Cities.Find(42);    var province = ctx.Provinces.Find(10);    city.ProvinceID = province.ProvinceID;    ctx.SaveChanges();}

　　方式3与方式1在代码运行之后所执行的SQL语句是一样的。

　　3、删除外键关联实体

　　示例：对外键允许为空的外键表记录删除引用，在City表中外键ProvinceID引用Province表，并允许为空。

using (var ctx = new PortalContext()){    var city = ctx.Cities.Find(42);    ctx.Entry(city)        .Reference(c => c.Province)        .Load();    city.Province = null;    ctx.SaveChanges();}

　　代码运行所执行的SQL语句：

exec sp_executesql N'SELECT [Limit1].[CityID] AS [CityID], [Limit1].[ProvinceID] AS [ProvinceID], [Limit1].[CityNo] AS [CityNo], [Limit1].[CityName] AS [CityName]FROM ( SELECT TOP (2)     [Extent1].[CityID] AS [CityID],     [Extent1].[ProvinceID] AS [ProvinceID],     [Extent1].[CityNo] AS [CityNo],     [Extent1].[CityName] AS [CityName]    FROM [dbo].[City] AS [Extent1]    WHERE [Extent1].[CityID] = @p0)  AS [Limit1]',N'@p0 int',@p0=42

exec sp_executesql N'SELECT [Extent1].[ProvinceID] AS [ProvinceID], [Extent1].[ProvinceNo] AS [ProvinceNo], [Extent1].[ProvinceName] AS [ProvinceName]FROM [dbo].[Province] AS [Extent1]WHERE [Extent1].[ProvinceID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=10

exec sp_executesql N'update [dbo].[City]set [ProvinceID] = nullwhere ([CityID] = @0)',N'@0 int',@0=42

　　根据外键删除与主键表的关联引用的另外一种实现方式：

using (var ctx = new PortalContext()){    var city = ctx.Cities.Find(42);    ctx.Entry(city)        .Reference(c => c.Province)        .Load();    city.ProvinceID = null;    ctx.SaveChanges();}