[Translate]-<entity framework>-execute database command directly

Original: [Translate]-<entity framework>-execute database command directly

Purely learning records, non-professional translation, if there is a mistake to welcome correct!

Original address: http://msdn.microsoft.com/en-us/library/gg715124 (v=vs.103)

With EF 4.1 or later, you can execute any database command directly. The methods described in this section allow you to perform native SQL commands against the database.

Get the set of entity objects with SQL query statements

The SQLQuery method in the DbSet class allows you to execute a native SQL query that returns a set of entity objects. By default, the returned object assembly is tracked by the context; This can be canceled by calling the asnotracking method on the Dbsqlquery object returned by the method. The returned result set is typically the type that corresponds to DbSet, otherwise its derived class cannot be returned. If the table being queried contains data from other entity types, the SQL statement executed should be written correctly, guaranteeing that only the data of the specified type entity is returned. The following example uses the SQLQuery method to execute an SQL query that returns an instance set of type Department.

1 using (varnew2 3     var departments =4                     "select * from Department"  5 }

Note: The Asnotracking method must be queried before execution , and the call is invalid after the query executes.

Get a set of non-entity objects through SQL queries

Using the SQLQuery method in the Database class to execute native SQL commands, you can return instances of any type, including native types in. Net. But the obtained data will not be tracked by the context object, even if we use this method to retrieve the entity object. Such as:

1 using (varnew2 3     var names = context. database.sqlquery<string> ("select Name from Department"   4

To have the database perform native non-query SQL commands

Non-query commands can be executed through the Executesqlcommand method in the Database class. For example:

1 using (varnew23     context.) Database.executesqlcommand ("update Department Set name = ' Mathematics ' WHERE name = ' Math '" /c13>4 }

The Executesqlcommand method is sometimes used in the initialization function of a database created by Code first to make some additional configuration of the database (for example, to set an index). It is important to note that the context object does not know what changes are made to the data in the database after the Executesqlcommand method is executed, unless you load or reload the entity set from the database.

Call a stored procedure

Code first does not support mapping of stored procedures. However, you can call the stored procedure directly through the Executesqlcommand or SQLQuery method. For example: Context. Database.executesqlcommand ("EXECUTE [dbo].[ DoSomething] ").

The three methods mentioned in this article (Dbset.sqlquery, Database.sqlquery, Database.executesqlcommand) support parameterized queries , usages, and strings. Format is similar, but the parameters that are passed in are type-converted when the query executes. such as: Context. Departments.sqlquery ("select * from Department where DepartmentID = {0}", "6"); When the statement executes, the string "6" is converted to an integer and then executed in the query statement, which effectively prevents SQL injection.

Preventing SQL injection attacks

Applications often take input from the outside (from users and other external agents) and then perform related actions based on those inputs. Any information obtained directly or indirectly from the user or external agent may take advantage of the syntax of the target programming language to perform illegal operations. When the target language is a Structured Query Language (SQL), such as Transact-SQL, this operation is called a SQL injection attack. A malicious user can inject commands directly into the query to perform operations, delete a table in the database, deny service, or modify the nature of the operation being performed. You should use a parameterized query instead of directly inserting the externally fetched string into the query string.

[Translate]-<entity framework>-execute database command directly