Entity Framework Code First新增修改及刪除單獨實體

libingql發表於2013-10-25
Framework

　　對於一個單獨實體的通常操作有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] = @0
where ([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] = null
where ([CityID] = @0)
',N'@0 int',@0=2
exec sp_executesql N'update [dbo].[City]
set [ProvinceID] = null
where ([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

相關文章