Executing SQL statements in EF

You might ask, do I use EF to avoid writing SQL? I might as well use ADO to write SQL. While that's true, there are times when it's inconvenient to use EF to manipulate data, such as allowing you to delete a set of records based on conditions, and if you follow the normal flow, you have to first isolate the data and then delete them one at a time, which is not only cumbersome but also less performance. In this case, SQL shows its power.

While using EF to execute SQL is easier than ADO, especially when executing query statements, EF automatically saves the queried data to the data entity, eliminating the hassle of using DataReader. At the same time the queried data will be tracked, if you change the value of the query, then it can be very convenient to use. SaveChanges () is updated directly to the database.

Executesqlcommand and SQLQuery

There is a database attribute in the instance of the data context Dbmodel, which has two sets of methods. Executesqlcommand () and. SQLQuery (). They can all execute SQL statements, just. Executesqlcommand () is not returning the result, only the number of rows affected is returned, so. Executesqlcommand () is more suitable for creating, updating, and deleting operations. SQLQuery () Returns the result of the query and saves the result in the data entity, so it is more appropriate to perform a query operation.

Use. Executesqlcommand () Implement Create, update, delete

. The use of Executesqlcommand () is also straightforward, with direct incoming SQL statements, and the number of rows affected will be returned when execution is complete.

1. using (var db = new Dbmodel ())//CREATE DATABASE context
2. {
3. //synchronously Execute SQL and return the number of rows affected
4. int result = DB . Database.executesqlcommand (@ "CREATE TABLE ' test '. ' Test ' (
5. ' id ' INT not NULL,
6. PRIMARY KEY (' id ')); ");
8. //Use SqlParameter to avoid SQL injection
9. var p_name = new SqlParameter ("@name", "Lori");
10. var p_age = new SqlParameter ("@age");
12. //If you are using a MySQL database, you need to replace the SqlParameter with Mysqlparameter
13. //var p_name = new Mysqlparameter ("@name", " Lori ");
14. //var p_age = new Mysqlparameter ("@age",);
16. //change student age
17. result = db. Database.executesqlcommand (@ "UPDATE ' Test '. ' Student '
18. SET ' age ' = @age
19. WHERE ' name ' = @name;", P_age, p _name);
21. //asynchronously executes SQL and returns the number of rows affected
22. task<int> RESULT2 = db. Database.executesqlcommandasync ("DROP TABLE ' test '. ' Test ';");
23. }

PS: If you need to create or delete the current database, there is also a. Create () and. Delete () methods, which do not accept parameters and return a BOOL value indicating success or failure.

Use. SQLQuery () Querying data

You can see it from the name. SQLQuery () is used to execute the query: SQLQuery () to specify the data type of the return value before use, such as I query the full information of a student, the type can be specified as the student type. If the number of students is counted, the return value is an integer, set to int.

Note: Not only the number of return values must be the same as the number of property values in the incoming type, but the name must also be the same, or an error will occur. So if I only wanted to get names and ages, I would have to define a class (which contains a string of type name and age of type int) to hold the data.

2. Class Temp
4. {
6. public string name {get; set;}
10. public int Age {get; set;}
12. }
16. static void Main (string[] args)
18. {
20. using (var db = new Dbmodel ())//CREATE DATABASE context
22. {
24. Query the student information called Lori, and specify that the return value type is student
26. dbrawsqlquery<student> RESULT1 = db. Database.sqlquery<student> ("select * from test.student WHERE name = ' Lori '");
28. You can also specify a return value type like this
30. Dbrawsqlquery RESULT1 = db. Database.sqlquery (typeof (Student), "SELECT * from test.student WHERE name = ' Lori '");
32. Console.WriteLine (RESULT1. FirstOrDefault (). name); Print Name
36. Dbrawsqlquery<int> RESULT2 = db. Database.sqlquery<int> ("SELECT count (*) from test.student");
38. Console.WriteLine (RESULT2. FirstOrDefault ()); How many students are printed
42. Check the student's age and name only
44. var RESULT3 = db. Database.sqlquery<temp> ("Select ' Name ', ' age ' from test.student;");
46. foreach (temp item in RESULT3)
48. {
50. Console.WriteLine (Item.name + ":" + item.age);
52. }
54. }

Use the dbset<t> under the. SQLQuery ()

There is also one under each data entity collection dbset<t>. SQLQuery (), the function is the same as described above, but dbset<t> under. SQLQuery () can only return types that are contained in dbset<t>. But under the dbset<t>. SQLQuery () also causes the database context (Dbmodel) to track the status of the returned data while returning the data, which can be used if the returned data has been modified. SaveChanges () Saves the result directly back to the database. The result of the. Database.sqlquery () is not to be found.

1. using (var db = new Dbmodel ())//CREATE DATABASE context
2. {
3. Inquire about the student information called Lori and revise her age
4. Student RESULT1 = Db.students.SqlQuery ("select * from test.student WHERE name = ' Lori '"). FirstOrDefault ();
5. Result1.age = 13; Through the Entity collection. SQLQuery the data that you have queried, and you can save it to the database after you modify it.
7. Student RESULT2 = db. Database.sqlquery<student> ("select * from test.student WHERE name = ' mong Choi '"). FirstOrDefault ();
8. Result2.age = 21; Because it's using. Database.sqlquery query to, so the changes here are not saved to the database
10. If you want to. Data detected under Database.sqlquery can also be saved to the database after modification.
11. Student RESULT3 = db. Database.sqlquery<student> ("select * from test.student WHERE name = ' Xiaoming '"). FirstOrDefault ();
12. Result3.age = 36;
13. Db. Entry<student> (RESULT3). state = System.Data.Entity.EntityState.Modified; Notifies the data context, and this record has been modified
15. Db. SaveChanges ();
16. }

Executing SQL statements in EF