Shulou(Shulou.com)06/01 Report--

This article mainly introduces how to execute the sql sentence in Entity Framework, which is very detailed and has certain reference value. Friends who are interested must finish it!

1. Why execute SQL statements in EF

Using EF to manipulate the database, you can avoid writing SQL statements and finish using Linq implementation, but why execute SQL statements in EF? If you want to write SQL statements, you can use ADO.NET to manipulate the database. Although this is true, but sometimes there are still some inconveniences in using EF to operate the database, for example: if you want to modify a record, follow the normal process of EF, you need to query the data to be modified first, and then modify it, which is not only troublesome but also low performance, when directly using EF to execute SQL statement performance will improve a lot.

Using EF to execute SQL is more convenient than ADO.NET, especially when executing query statements, EF will automatically save the queried data to the data entity, saving the trouble of using DataReader. At the same time, the queried data will also be tracked, and if you modify the queried value, you can easily update it directly to the database using .SaveChanges ().

There is a property of Database in the data context DbContext, and there are two sets of methods in the Database property: ExecuteSqlCommand () and SqlQuery (). Both of these methods can be used to execute SQL statements, but there are differences between them: ExecuteSqlCommand () does not return results, but only the number of rows affected, so ExecuteSqlCommand () is more suitable for creating, inserting, updating, and deleting operations (that is, executing a given DDL/DML command). SqlQuery () returns the results of the query and saves the results in the data entity, so SqlQuery () is more suitable for performing query operations.

Use ExecuteSqlCommand () to execute create, insert, update and delete statements

The use of ExecuteSqlCommand () is as simple as passing in the SQL statement directly, and the number of affected rows is returned when the execution is complete.

In the following example, entity is an object that inherits from DbContext.

1. Execute the creation statement / / execute the creation statement string strCreateSQL = @ "CREATE table test (id int primary key not null,name varchar (16), password varchar (20))"; / / Note: the number of rows affected by the execution of the create statement is-1int result = entity.Database.ExecuteSqlCommand (strCreateSQL); if (result.Equals (- 1)) {Console.WriteLine ("created successfully!") 2. Execute Insert statement / / execute Insert statement string strInsertSQL = @ "INSERT INTO test SELECT 1, 'Xiao Ming', '1234' UNION SELECT 2, 'Xiao Wang,' 1234 'UNION SELECT 3,' Xiao Hong, '1234"; int result = entity.Database.ExecuteSqlCommand (strInsertSQL) If (result > 0) {Console.WriteLine ("insert successfully");} 3. Execute Update statement / / execute Update statement string strUpdateSQL = @ "UPDATE test SET password=@pwd1 WHERE id=@id1;UPDATE test SET password=@pwd2 WHERE id=@id2;" SqlParameter [] para = {new SqlParameter ("@ pwd1", "ceshi12we"), new SqlParameter ("@ id1", 1), new SqlParameter ("@ pwd2", "ceshi127890"), new SqlParameter ("@ id2", 2),}; int result = entity.Database.ExecuteSqlCommand (strUpdateSQL, para) If (result > 0) {Console.WriteLine ("updated successfully");} 4, execute Delete statement / / execute delete statement string strDelSQL = "delete from test"; int result = entity.Database.ExecuteSqlCommand (strDelSQL); if (result > 0) {Console.WriteLine ("deleted successfully");} 5, execute Drop statement string strDropSQL = "drop table test"; int result = entity.Database.ExecuteSqlCommand (strDropSQL) If (result.Equals (- 1)) {Console.WriteLine ("deleted successfully");}

Note: executing DDL statements (create, alter, drop, etc.) returns the number of affected rows as returned by-1MagneDML (insert, update, delete).

Use SqlQuery () to query data

SqlQuery () is used to execute the query. SqlQuery () needs to specify the type of return value before using it. The return value type can be a defined entity type or a primitive type. For example, if you query the complete information of a user, the return type is the user entity type; if you are counting the number of users, the return value is the int type.

Note: the number and name of the returned value must be the same as the number and name of attributes in the type passed in, so it is better to report an error.

In the following example, User is the entity type generated from the database table.

String strSQL = "SELECT * FROM Users WHERE ID > = 10 ORDER BY ID DESC"; var info = entity.Database.SqlQuery (strSQL); foreach (var item in info) {Console.WriteLine ("ID:" + item.ID + "" + "login:" + item.LoginName + "" + "password:" + item.Password);}

Running result:

As mentioned earlier, the number and name of the return value must be the same as the number and name of attributes in the type passed in, so it is better to report an error. If you change the SQL statement to query only ID, login, and password, the following error occurs:

What if I just want to query ID, login, and password? Then you need to define a separate class (only ID, login, password) to save the data.

The newly defined class contains only three attributes: ID, login name, and password:

Public class newUser {public int ID {get; set;} public string LoginName {get; set;} public string Password {get; set;}} / / method 4: SqlQuerytry {string strSQL = "SELECT ID,LoginName,Password FROM Users WHERE ID > = 10 ORDER BY ID DESC"; var info = entity.Database.SqlQuery (strSQL) Foreach (var item in info) {Console.WriteLine ("ID:" + item.ID + "" + "login:" + item.LoginName + "" + "password:" + item.Password);}} catch (Exception ex) {Console.WriteLine (ex.Message);}

Running result:

The return value is the primitive type:

Query the number of users and return the int type

/ / query the number of users string strSQL = "SELECT COUNT (*) FROM test"; var result = entity.Database.SqlQuery (strSQL); / / Note: you must use a loop to actually execute the SQL statement to the database, otherwise the database will not execute the SQL statement (delayed loading of EF) foreach (var item in result) {Console.WriteLine ("number of users:" + item.ToString ());}

Running result:

4. Use SqlQuery () under DbSet

There is also a SqlQuery () under each data entity collection DbSet, with the same function as described above, except that SqlQuery () under DbSet can only return the types contained in DbSet. SqlQuery () under DbSet will also let the database context (DBContext) track the status of the returned data while returning data. If the returned data has been modified, you can use SaveChanges () to save the results directly back to the database. The results found by Database.SqlQuery () cannot track the status of the returned data.

1. Use the SqlQuery () method under the entity collection string strSQL = "SELECT * FROM Users WHERE UserID='002068'"; User user = entity.Users.SqlQuery (strSQL). FirstOrDefault (); user.Password = "Test the SqlQuery method under the entity"; / / call the SaveChanges () method to update the Password field entity.SaveChanges (); 2. Use the SqlQuery () method under Database string strSQL = "SELECT * FROM Users WHERE UserID='002068'"; User user = entity.Database.SqlQuery (strSQL). FirstOrDefault () User.Password = "Test the SqlQuery method under Database"; / / the Password field entity.SaveChanges () cannot be updated by calling the SaveChanges () method.

If you want the data queried using SqlQuery () under Database to be saved to the database after modification, you can use the following code:

String strSQL = "SELECT * FROM Users WHERE UserID='002068'"; User user = entity.Database.SqlQuery (strSQL). FirstOrDefault (); user.Password = "Test the SqlQuery method under Database"; / / set the status of this data to: Modified, so that the data context can be notified, and the record has also been modified entity.Entry (user). State = System.Data.Entity.EntityState.Modified;// cannot update the Password field entity.SaveChanges () by calling the SaveChanges () method These are all the contents of the article "how to execute sql statements in Entity Framework". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.