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] = @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();
}