Automatically generated SQL statements using the entity Framework

There are 3 common actions for a single entity: adding new entities, modifying entities, and deleting entities.

　　1. Add a new entity

　　Entity Framework Code First adds a new entity by calling the Dbset.add () method.

using (var ctx = new Portalcontext ()) {    var province = new province    {        Provinceno = "100000",        provincename = "Test"    };    CTx. Provinces.add (province);    CTx. SaveChanges ();}

The SQL statement executed by the code run:

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 (ten), @1 nvarchar (+) ', @0=n ' 100000 ', @1=n ' Test '

2. Modify the Entity

To modify an entity record that already exists in the database:

using (var ctx = new Portalcontext ()) {    var province = ctx. Provinces.find (+);    Province. Provincename = "Test";    CTx. SaveChanges ();}

The SQL statement executed by the code run:

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 (+), @1 int ', @0=n ' Test ', @1=35

3. Deleting entities

Entity Framework Code First adds a new entity by calling the Dbset.remove () method.

1>, deleting based on instantiated entities

When you delete an entity based on an instantiated entity, you typically need to read the entity's data from the database, call Dbset.remove () to delete it, and delete the record by DbContext to the database.

using (var ctx = new Portalcontext ()) {    var province = ctx. Provinces.find (+);    CTx. Provinces.remove (province);    CTx. SaveChanges ();}

The SQL statement executed by the code run:

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>, deleting entities based on primary key

Deleting an entity based on a primary key can take less steps to read a record from the database based on the primary key value.

using (var ctx = new Portalcontext ()) {    var province = new Province {Provinceid = n};    CTx. Provinces.attach (province);    CTx. Provinces.remove (province);    CTx. SaveChanges ();}

Or

using (var ctx = new Portalcontext ()) {    var province = new Province {Provinceid = n};    CTx. Entry (province). state = entitystate.deleted;    CTx. SaveChanges ();}

Note: EntityState needs to reference namespaces using System.Data.

The SQL statement executed by the code run:

EXEC sp_executesql N ' delete [dbo]. [Province]where ([Provinceid] = @0) ', N ' @0 int ', @0=36

3>, execute SQL statement Delete

using (var ctx = new Portalcontext ()) {    ctx. Database.executesqlcommand ("DELETE from [dbo].[ Province] WHERE [provinceid]=37 ");}

4>, deleting Entity association data

If you need to delete a record, save the foreign key table. When you need to delete a primary table record, the associated data in the foreign key table is also manipulated.

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

The SQL statement executed by the code run:

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

......

As can be seen from the SQL statement executed after the execution of the code, when the primary table record is deleted, the value of the foreign key column of the associated record is set to NULL when the foreign key table setting of the referenced foreign keys run is empty.

If the foreign key reference is not NULL, and the foreign key field in the city table Provinceid is not NULL, then the SQL statement executed after the above code is run is:

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

......

When the foreign key column is allowed to be empty, the primary table record is deleted, and the associated deletions are recorded from the table:

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 statements executed after the code is run:

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
Source: http://www.cnblogs.com/libingql/p/3388491.html

Automatically generated SQL statements using the entity Framework