Entity Framework Code First新增修改及刪除外來鍵關聯實體

libingql發表於2013-10-26
Framework

　　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] = @0
where ([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] = @0
where ([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] = null
where ([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();
}

相關文章