How to insert, modify, and delete data using LINQ to SQL

Both LINQ and LINQ to SQL are no longer a new thing, but I was too late to come into contact with this series. I decided to write this series with an attitude that I knew it was necessary.

 

The test environment used in this article is vs 2010, and the SQL Server 2005 database.

 

1. Starting from cud, how to insert, modify, and delete data using LINQ to SQL

2. query simple queries using LINQ to SQL

3. query delayed loading and immediate loading, using loadwith and associatewith

4. query inner join and left Outer Join

5. aggregate grouping having in LINQ to SQL

6. Do I have to worry about performance when optimizing the query of LINQ to SQL?

 

Article 1 starting from cud, how to insert, modify, and delete data using LINQ to SQL

Preparations: Now the test table student is created in the database. This table has only three fields: ID, name, and hometown. The ID is an auto-increment field of the int type, and the name and howmtown are nvarchar types.

1. Open vs2010 to create a console applicationProgramAnd then add the LINQ to SQL class named dbapp. dbml: after creating a new dbml file, you can open Server Explorer, establish a database connection, and drag the new table to the dbml file. The result is as follows:

 

2. You can click the blank area of the dbml file and press F4 to display the dbml attribute. You can modify the context and generate object namespaces.

 

3. By now, vs2010 has created a method for adding, modifying, and deleting data tables to, corresponding to, object classes, and data table operations.

1) Add add

 
Static void add () {// Add a student astudent = new student {name = "", hometown = "Nanhai Guanyin Emy"}; console. writeline ("---------- begin Add a student"); Using (dbappdatacontext DB = new dbappdatacontext () {dB. log = console. out; dB. students. insertonsubmit (astudent); dB. submitchanges ();} console. writeline ("---------- end add a student ");}
 

SQL statement output

Insert into [DBO]. [Student] ([name], [hometown]) values (@ P0, @ P1) Select convert (INT, scope_identity () as [value] -- @ P0: input nvarchar (size = 4000; prec = 0; scale = 0) [Zhang xiao'er] -- @ P1: Input nvarchar (size = 4000; prec = 0; scale = 0) [Nanhai Guanyin Institute] -- Context: sqlprovider (sql2005) model: attributedmetamodel build: 4.0.30319.1
 

2) use LINQ to SQL to perform the edit operation.

 
Private Static void edit (int id) {console. writeline ("---------- begin edit"); Using (dbappdatacontext DB = new dbappdatacontext () {dB. log = console. out; // retrieve student var editstudent = dB. students. singleordefault <student> (S => S. id = ID); If (editstudent = NULL) {console. writeline ("ID error"); return;} // modify the attributes of student editstudent. name = "James"; editstudent. hometown = "Zhangjiakou zhangjiazhai zhangjiaying"; // execute the update operation dB. submitchanges ();} console. writeline ("--------- end edit student ");}
 

SQL statement output

 
Select [t0]. [ID], [t0]. [name], [t0]. [hometown] from [DBO]. [Student] as [t0] Where [t0]. [ID] = @ P0 -- @ P0: Input int (size =-1; prec = 0; scale = 0) [6] -- Context: sqlprovider (sql2005) model: attributedmetamodel build: 4.0.30319.1update [DBO]. [Student] Set [name] = @ P3, [hometown] = @ p4where ([ID] = @ P0) and ([name] = @ P1) and ([hometown] = @ P2) -- @ P0: Input int (size =-1; prec = 0; scale = 0) [6] -- @ P1: input nvarchar (size = 4000; prec = 0; scale = 0) [Zhang xiao'er] -- @ P2: Input nvarchar (size = 4000; prec = 0; scale = 0) [Nanhai Guanyin Institute] -- @ P3: Input nvarchar (size = 4000; prec = 0; scale = 0) [Zhang xiaosan] -- @ P4: Input nvarchar (size = 4000; prec = 0; scale = 0) [Zhangjiakou zhangjiazhai zhangjiadying] -- Context: sqlprovider (sql2005) model: attributedmetamodel build: 4.0.30319.1
 

3) Use LINQ to SQL to perform the delete operation.

RunCode:

Static void Delete (int id) {console. writeline ("----------- begin delete a student"); Using (dbappdatacontext DB = new dbappdatacontext () {dB. log = console. out; // retrieve student var student = dB. students. singleordefault <student> (S => S. id = ID); If (student = NULL) {console. writeline ("student is null"); return;} dB. students. deleteonsubmit (student); dB. submitchanges ();} console. writeline ("------------ end Delete student ");}
 

Generated SQL statement:

Select [t0]. [ID], [t0]. [name], [t0]. [hometown] from [DBO]. [Student] as [t0] Where [t0]. [ID] = @ P0 -- @ P0: Input int (size =-1; prec = 0; scale = 0) [6] -- Context: sqlprovider (sql2005) model: attributedmetamodel build: 4.0.30319.1delete from [DBO]. [Student] Where ([ID] = @ P0) and ([name] = @ P1) and ([hometown] = @ P2) -- @ P0: input int (size =-1; prec = 0; scale = 0) [6] -- @ P1: Input nvarchar (size = 4000; prec = 0; scale = 0) [Zhang xiaosan] -- @ P2: Input nvarchar (size = 4000; prec = 0; scale = 0) [Zhangjiakou zhangjiazhai zhangjiaying] -- Context: sqlprovider (sql2005) model: attributedmetamodel build: 4.0.30319.1
 

Conclusion: Through the above practices, we can see that it is very convenient to execute the ADD, modify, and delete operations using LINQ to SQL. We do not even need to learn any SQL-related knowledge.

I have two questions:

1. whether the object must be obtained before the operation can be performed before the update and delete operations can be performed. When I try to update an object, instead of getting the object in the database, I declare an object myself and then delete it, but failed

2. the generated update and delete condition statements include the statements "name = @ P" and "hometown = @ P". Link to SQL knows that ID is the unique primary key, why do we still include the missing conditions?