EntityFrameworkCodeFirst添加修改及删除外键关联实体

1、En tity Framework Code First添加修改及删除外键关联实体1、添加外键关联实体1、添加新的 Province 及City实体using ( var ctx =newPortalCo ntext()var city1 =n ewCityCityNo =10010,;CityName =测试城市1;var city2 =n ewCityCityNo =10020,;CityName =测试城市2var province :=n ewPro vinceProvin ceNo =10000,;ProvinceName =测试省份”provi nce.Cities.Add(cit

2、yl); provin ce.Cities.Add(city2); ctx.Pro vin ces.Add(pro vin ce);ctx.SaveCha nges();代码运行所执行的 SQL语句：exec sp_executesql N insert dbo.Province(ProvinceNo,Provin ceName)values (0, 1)select Provi ncelDfrom dbo.Pro vin cewhere ROWCOUNT 0 and Provi ncelD = scope_ide ntity(),N0nvarchar(10),1 nvarchar(50),

3、0N10000 , =N 测试省份exec sp_executesql N insert dbo.City(ProvincelD, CityNo, CityName)values (0, 1, 2)select CitylDfrom dbo.Citywhere ROWCOUNT 0 and CityID = scope_ide ntity(),N0 in t,1nvarchar(10),2 nvarchar(50), 039, =N10010 , 2N 测试城市 1exec sp_executesql N insert dbo.City(ProvincelD, CityNo, CityName

4、)values (0, 1, 2)select CityIDfrom dbo.Citywhere ROWCOUNT 0 and CityID = scope_ide ntity(),N0 in t,1nvarchar(10),2 nvarchar(50), 039, =N10020 , 2N 测试城市 22、添加新的Province 实体及现有的City实体using ( var ctx =newPortalContext() var city1 =n ewCityCityNo =10010,CityName =测试城市1;var city2 =n ewCityCityNo =10020,Ci

5、tyName =测试城市2;var city3 = ctx.Cities.Fi nd( 36);var province = n ewPro vinceProvinceNo =10000,ProvinceName =测试省份;provi nce.Cities.Add(city1);provin ce.Cities.Add(city2);provin ce.Cities.Add(city3);ctx.Pro vin ces.Add(pro vin ce);ctx.SaveCha nges();exec sp_executesql N SELECTLimit1.CitylD AS CitylD,L

6、imit1.ProvinceID AS ProvinceID,Limit1.CityNo AS CityNo,Limit1.CityName AS CityNameFROM ( SELECT TOP (2)Extent1.CityID AS CityID,Exte nt1.Provi nceID AS Provi nceID,Extent1.CityNo AS CityNo,Exte nt1.CityName AS CityNameFROM dbo.City AS Extent1WHERE Exte nt1.CityID = p0)AS Limit1 ,Np0 int , p=36exec s

7、p_executesql N insert dbo.Province(ProvinceNo,Provin ceName)values (0, 1)select Provi nceIDfrom dbo.Pro vin cewhere ROWCOUNT 0 and Provi nceID = scope_ide ntity() ,N0 nvarchar(10),1 nvarchar(50), 0N10000 , =N 测试省份exec sp_executesql N update dbo.Cityset Provi nceID = 0where (CityID = 1),N0 int,1 int

8、, =40, =36exec sp_executesql N insert dbo.City(ProvinceID, CityNo,CityName)values (0, 1, 2)select CityIDfrom dbo.Citywhere ROWCOUNT 0 and CityID = scope_ide ntity(),N0 in t,1nvarchar(10),2 nvarchar(50), 040, =N10010 , 2N 测试城市 1exec sp_executesql N insert dbo.City(ProvinceID, CityNo, CityName)values

9、(0, 1, 2)select CityIDfrom dbo.Citywhere ROWCOUNT 0 and CitylD = scope_ide ntity(),N0 in t,1nvarchar(10),2 nvarchar(50), 040, =N10020 , 2N 测试城市 22、修改外键关联实体1、方式1using ( var ctx = newPortalContext()var city = ctx.Cities.Fi nd(40);var province = ctx.Pro vin ces. Find(10);city.Pro vince = provin ce;ctx.

10、SaveCha nges();翩代码运行所执行的 SQL语句：exec sp_executesql N SELECTLimit1.CitylD AS CityID,Limit1.ProvinceID AS ProvinceID,Limit1.CityNo AS CityNo,Limit1.CityName AS CityNameFROM ( SELECT TOP (2)Extent1.CityID AS CityID,Exte nt1.Provi nceID AS Provi nceID,Extent1.CityNo AS CityNo,Exte nt1.CityName AS CityNam

11、eFROM dbo.City AS Exte nt1WHERE Exte nt1.CityID = p0)AS Limit1 ,Np0 int , p=40exec sp_executesql N SELECTLimit1.ProvinceID AS ProvinceID,Limit1.ProvinceNo AS ProvinceNo,Limit1.Provi nceName AS Provi nceName FROM ( SELECT TOP (2)Exte nt1.Provi nceID AS Provi nceID,Exte nt1.Provi nceNo AS Provi nceNo,

12、Exte nt1.Provi nceName AS Provi nceNameFROM dbo.Provinee AS Extent1WHERE Exte nt1.Provi ncelD = p0)AS Limitl ,Np0 int , p=10 exec sp_executesql N update dbo.City set Provi ncelD = 0where (CitylD = 1),N0 int,1 int , =10, =402、方式2using ( var ctx =newPortalContext()var city = ctx.Cities. Find( 41);var

13、province = ctx.Pro vin ces. Find(10);provi nce.Cities.Add(city);ctx.SaveCha nges();方式2的实现方式与方式1的实现方式区别：在执行province.Cities.Add(city)时，会自动调用延迟加载，多执行一次从数据库中根据Provinee 关联获取City的SQL语句。exec sp_executesql N SELECTExtent1.CityID AS CitylD,Extent1.ProvincelD AS ProvincelD,Extent1.CityNo AS CityNo,Extent1.Cit

14、yName AS CityNameFROM dbo.City AS Exte nt1WHERE Exte nt1.Provi ncelD = En tityKeyValue1,NE ntityKeyValue1int , EntityKeyValue1 =103、方式3using ( var ctx =newPortalContext()var city = ctx.Cities.Fi nd( 42);var province = ctx.Pro vin ces. Find(10);city.Provi ncelD = provin ce.Pro vin celD;ctx.SaveCha ng

15、es();方式3与方式1在代码运行之后所执行的SQL语句是一样的。3、删除外键关联实体示例：对外键允许为空的外键表记录删除引用，在City表中外键ProvinceID弓I用Province表，并允许为空。using ( var ctx =newPortalContext()var city = ctx.Cities.Fi nd( 42);ctx.E ntry(city).Refere nce(c = c.Pro vin ce).Load();city.Province =null ;ctx.SaveCha nges();代码运行所执行的 SQL语句：1exec sp_executesql N

16、SELECT Limit1.CitylD AS CityID, Limit1.ProvinceID AS ProvinceID, Limit1.CityNo AS CityNo, Limit1.CityName AS CityName FROM ( SELECT TOP (2)Extent1.CityID AS CityID,Exte nt1.Provi nceID AS Provi nceID,Extent1.CityNo AS CityNo,Exte nt1.CityName AS CityName FROM dbo.City AS Exte nt1 WHERE Exte nt1.CityID = p0)AS Limit1 ,Np0 int , p=42副exec sp_executesql N SELECTExtent1.ProvinceID AS ProvinceID,Exte nt1.Provi nceNo AS Provin ceNo,Exte nt1.Provi nceName AS Provi nceName FROM dbo.Provi nee AS Exte nt1,NE ntityKeyValuelWHERE Exte nt1.Provi ncelD = En tityKeyValuel int , EntityKeyValuel =10exec sp_