使用Entity Framework 自動產生的Sql語句

標籤：

　對於一個單獨實體的通常操作有3種：添加新的實體、修改實體以及刪除實體。

　　1、添加新的實體

　　Entity Framework Code First添加新的實體通過調用DbSet.Add()方法來實現。

using (var ctx = new PortalContext()){    var province = new Province    {        ProvinceNo = "100000",        ProvinceName = "測試"    };    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‘100000‘,@1=N‘測試‘

　　2、修改實體

　　修改資料庫中已經存在的實體記錄：

using (var ctx = new PortalContext()){    var province = ctx.Provinces.Find(35);    province.ProvinceName = "Test";    ctx.SaveChanges();}

　　代碼運行所執行的SQL語句：

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=35

exec sp_executesql N‘update [dbo].[Province]set [ProvinceName] = @0where ([ProvinceID] = @1)‘,N‘@0 nvarchar(50),@1 int‘,@0=N‘Test‘,@1=35

　　3、刪除實體

　　Entity Framework Code First添加新的實體通過調用DbSet.Remove()方法來實現。

　　1>、根據已執行個體化的實體刪除

　　在根據已執行個體化的實體來刪除實體時，通常需要先從資料庫中讀取該實體的資料，再調用DbSet.Remove()來刪除，並通過DbContext到資料庫中去刪除該記錄。

using (var ctx = new PortalContext()){    var province = ctx.Provinces.Find(35);    ctx.Provinces.Remove(province);    ctx.SaveChanges();}

　　代碼運行所執行的SQL語句：

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=35

exec sp_executesql N‘delete [dbo].[Province]where ([ProvinceID] = @0)‘,N‘@0 int‘,@0=35

　　2>、根據主鍵刪除實體

　　根據主鍵來刪除實體可以少執行一步從資料庫中根據主索引值讀取記錄的操作。

using (var ctx = new PortalContext()){    var province = new Province { ProvinceID = 36 };    ctx.Provinces.Attach(province);    ctx.Provinces.Remove(province);    ctx.SaveChanges();}

　　或

using (var ctx = new PortalContext()){    var province = new Province { ProvinceID = 36 };    ctx.Entry(province).State = EntityState.Deleted;    ctx.SaveChanges();}

　　註：EntityState需要引用命名空間using System.Data。

　　代碼運行所執行的SQL語句：

exec sp_executesql N‘delete [dbo].[Province]where ([ProvinceID] = @0)‘,N‘@0 int‘,@0=36

　　3>、執行SQL語句刪除

using (var ctx = new PortalContext()){    ctx.Database.ExecuteSqlCommand("DELETE FROM [dbo].[Province] WHERE [ProvinceID]=37");}

　　4>、刪除實體關聯資料

　　在需要刪除一條記錄時，若存在外鍵表。在需要在刪除主表記錄時，同時對外鍵表中關聯的資料進行操作。

using (var ctx = new PortalContext()){    var province = new Province { ProvinceID = 3 };    ctx.Provinces.Attach(province);    ctx.Entry(province)        .Collection(p => p.Cities)        .Load();    ctx.Provinces.Remove(province);    ctx.SaveChanges();}

　　代碼運行所執行的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=3

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

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

......

　　從代碼運行之後執行的SQL語句可以看出，在刪除主表記錄時，若引用的外鍵表設定外鍵運行為空白時，將把關聯記錄的外鍵列的值設定為null。

　　若外鍵引用為not null時，及City表中的外鍵欄位ProvinceID為not null，則上面的代碼運行之後執行的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=3

exec sp_executesql N‘delete [dbo].[City]where ([CityID] = @0)‘,N‘@0 int‘,@0=2

exec sp_executesql N‘delete [dbo].[City]where ([CityID] = @0)‘,N‘@0 int‘,@0=3

......

　　在外鍵列允許為空白時，刪除主表記錄，聯帶刪除從表記錄：

using (var ctx = new PortalContext()){    var province = new Province { ProvinceID = 5 };    ctx.Provinces.Attach(province);    ctx.Entry(province)        .Collection(p => p.Cities)        .Load();    ctx.Provinces.Remove(province);    foreach (var city in province.Cities)    {        ctx.Cities.Remove(city);    }    ctx.SaveChanges();}

　　代碼運行之後執行的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=5

exec sp_executesql N‘delete [dbo].[City]where ([CityID] = @0)‘,N‘@0 int‘,@0=20

exec sp_executesql N‘delete [dbo].[City]where ([CityID] = @0)‘,N‘@0 int‘,@0=21

......

exec sp_executesql N‘delete [dbo].[Province]where ([ProvinceID] = @0)‘,N‘@0 int‘,@0=5
來源：http://www.cnblogs.com/libingql/p/3388491.html

使用Entity Framework 自動產生的Sql語句