使用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] = @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
来源：http://www.cnblogs.com/libingql/p/3388491.html

转载于:https://www.cnblogs.com/searchbaidu/p/5498194.html