Entity Framework 6 Recipes Chinese Translation series (three)-----chapter III Querying using SQL statements

3-2 updating using native SQL statements

Problem

　　You want to update the underlying data store by using native SQL statements in the Entity Framework.

Solution Solutions

Suppose you have a payment database table shown in 3-2, using the Entity Framework Designer tool to create a model that is shown in 3-2.

Figure 3-2 Payment table, containing payment information for a vendor

Figure 3-3 contains a model of a payment entity

In order to execute a sentence and a multiple-sentence SQL statement in the underlying payment table, you can use the Executesqlcommand () method in the properties database in the DbContext class. While we can query the payment entity in the model, the Executesqlcommand method allows us to query the underlying database directly, discarding certain features of the Entity Framework, such as change tracking. We need a simple model object that contains a context object for executing the SQL command.

The following code listing 3-4 executes a single sentence or multiple SQL statements

Code Listing 3-4 executing a query statement

 1//delete previous test Data 2 using (var context = new Efrecipesentities ()) 3 {4 context. Database.executesqlcommand ("Delete from chapter3.payment");                 5} 6//Insert two rows of data 7 using (var context = new Efrecipesentities ()) 8 {9    var sql = @ "INSERT into chapter3.payment (Amount, Vendor) VALUES (@Amount, @Vendor)";                         Here you can use a parameter placeholder such as @p0, and ADO automatically creates a parameter object for us one by one var parameters = new Dbparameter[]12 {13 New SqlParameter {parametername = "Amount", Value = 99.97m},14 new Sqlparamet er {parametername = "Vendor", Value = "Ace Plumbing"}15};16 + var rowCount = context .                          Database.executesqlcommand (SQL, parameters), parameters = new Dbparameter[]20 {21   New SqlParameter {parametername = "Amount", Value = 43.83m},22                      New SqlParameter23 {parametername = "                     Vendor "," $ = "Joe ' s Trash Service" 26}27 };28 RowCount + = context. Database.executesqlcommand (SQL, Parameters), Console.WriteLine ("{0} rows inserted", rowcount.tostring ()) ; 31}32 33//Get and display data using the (var context = new Efrecipesentities ()) 35 {3 6 Console.WriteLine ("Payments"), PNs Console.WriteLine ("========"); FOREAC H (var payment in context. Payments) Console.WriteLine ("Paid {0} to {1}", payment. Amount.tostring (), payment.              Vendor)}43}44 Console.WriteLine ("\npress <enter> to continue ..."); 46 Console.ReadLine(); 

The output of code listing 3-4 is as follows:

1 2 rows Inserted2 Payments3 ========4 Paid $99.97 to Aces Plumbing5 Paid $43.83 to Joe ' s Trash Service

In Listing 3-4, we first delete the previous test data and then use the Executesqlcommand () method in the properties in the DbContext class, and notice how a native SQL statement is passed to this method.

We then create a SQL INSERT statement in the form of a string that contains two parameters @amout and @vendor. They are just a placeholder, and when the SQL statement executes, it is replaced by a specific value.

Next, we created two parameter objects of the DbParameter type, which bind the parameter placeholders with the specific values. In the first insert, I bind the value 99.97 to the placeholder amount and bind the value "Ace plumbing" to the placeholder vendor. Later, we created another record. It is important to note how these two parameter objects are assigned to an array of type DbParameter. In order to execute the SQL statement, we pass the string containing the SQL statement and the parameter array of type DbParameter to Method Executesqlcommand (), Execuesqlcommand returns the number of rows affected by the SQL statement. In our example, each time we call the Executesqlcommand () method, we insert a row of data.

If your SQL statement does not have any arguments, the Executesqlcommand method has another overloaded method that contains only one SQL statement parameter that receives the string form.

The pattern in code listing 3-4 is somewhat the same as the way we query data using the SqlClient object in the ADO. The difference is that we don't need to construct a connection string and explicitly open a database connection. The Entity Framework context object automatically completes this work. Note that there are two versions of the context object: The DbContext context object for Code-first in Entity Framework 5, 6, and 4.x, and the ObjectContext context object in earlier versions.

Keep in mind thatDbContext is just a simple wrapper or "Façade," ( This is French, "appearance mode") it wraps the ObjectContext context object, making the context object more straightforward and easier to use. All features of the ObjectContext are still valid.

There are also different ways to represent SQL commands and parameters. In the ExecuteNonQuery () method in ADO, the command text and parameters are set to the Commands object, but the parameters of the simple type are passed to the Executesqlcommand () method in the Entity Framework.

The attentive reader may have noticed (this is the point) that we do not have a query model. In fact, as we mentioned earlier, we don't need the payment entity shown in 3-3. Method Exceutesqlcommand () uses only the DbContext context object, as well as the connection string.

Best practices

Using parameterized SQL statements and not using parameterized SQL statements is a problem ... Should you use parameterized SQL statements or dynamically create SQL statements in string form? The best practice is to use parameterized SQL statements whenever possible. The reasons are as follows:

1. Parameterized SQL statements can help prevent SQL injection attacks. If you use the input characters from the TextBox control on the user interface, the SQL statements are stitched dynamically. Then you may inadvertently expose yourself to SQL injection statements, which can seriously damage your database and leak sensitive information. When you use parameterized SQL statements, it will help you prevent this from happening.

2. If we look at a parameterized SQL statement as shown in the example, the SQL statement is allowed to be reused. Reuse can make our code more concise and easy to read.

3, many enterprise databases, such as Oracle, DB2, and SQL Server in some cases, they can rely on parameterized queries to reuse the parsed query statements, even if the parameters changed. Through reuse, it improves the performance of the database and reduces the processing process.

4, the parameterized SQ statement can make the code more maintainable and configurable. For example, SQL statements can be made into a configuration file, which allows you to change the application without modifying the code.

3-3 getting objects using native SQL statements

Problem

You want to use native SQL statements to get objects from the database.

Solution Solutions

Suppose you have a model with a student entity type shown in 3-4.

Figure 3-4 a model with a student entity type

You want to return an instance collection of entity type student by executing the native SQL statement. As seen in the previous section, the Executesqlcommand () method in the Entity Framework is somewhat similar to the Excutenonquery method of SqlCommand in the ADO. It executes the given SQL statement, returning the number of rows affected. In order for the Entity Framework to implement untyped data to strongly typed entity transformations, I can use the method SQLQuery ().

As a start, this example relies on Code-first in the Entity Framework. Code Listing 3-5, creates a student entity class.

Code Listing 3-5 Student entity class

public class Student    {public        int StudentID {get; set;}        public string degree {get; set;}        public string FirstName {get; set;}        public string LastName {get; set;}    }

Next, code listing 3-6 creates a context object for Code-first.

Code Listing 3-6 Context object DbContext

1 Public  class Efrecipesentities:dbcontext 2     {3 public         efrecipesentities () 4             : Base ("  ConnectionString ") 5         {6         } 7  8 public         dbset<student> Students {get; set;} 9         protected override void Onmodelcreating (Dbmodelbuilder modelBuilder) One by one         {             modelbuilder.entity<student> (). ToTable ("Chapter3.student");             Onmodelcreating (ModelBuilder);         }15     }

　　

In order to execute the SQL statement and return an instance collection of entity type student, use the pattern in code listing 3-7.

The code listing 3-7 uses Executestorequery () to execute the SQL statement and return the object using the SQLQuery () method) method.

 1 using (var context = new Efrecipesentities ()) 2 {3//delete test data 4 context. Database.executesqlcommand ("Delete from chapter3.student"); 5 6//Insert Data 7 context.                         Students.add (New Student 8 {9 FirstName = "Robert", 10 LastName = "Smith", one degree = "Masters");                         Students.add (new Student14 {FirstName = "Julia", 16 LastName = "Kerns", Degree = "Masters");                         Students.add (new Student20 {FirstName = "Nancy", 22 LastName = "Stiles", Degree = "doctorate");         SaveChanges (); 26}27 28    using (var context = new Efrecipesentities ()), {$ var sql = "Select * FROM Chapter3.stud                         ent WHERE degree = @Major "; var parameters = new Dbparameter[]32 {33 New SqlParameter {parametername = "Major", Value = "Masters"}34};35 var stud Ents = context. database.sqlquery<student> (SQL, Parameters), Console.WriteLine ("Students ..."), PNs F Oreach (var student in students) $ {Console.WriteLine ("{0} {1} is working on a {2} Degree ", student. FirstName, student. LastName, student.              degree)}42}43 Console.WriteLine ("\npress <enter> to continue ..."); 45 Console.ReadLine (); 46}

The output of code listing 3-7 is as follows:

1 Students ... 2 Robert Smith is working on a Masters degree3 Julia kerns be working on a Masters degree

Principle

In code listing 3-7, we added 3 students to the DbContext context and saved to the database using the SaveChanges () method.

To obtain a master's degree, we used the SQLQuery () method, a parameterized SQL statement, and a set of "Masters." The parameter of the value. We enumerate the returned Stuendts set of merged printouts. Note the related context objects are tracked for changes implemented by these values.

Here, in the query statement, "*" is used to denote all the column names, and the Entity Framework matches the returned columns to the appropriate properties. In general, this will work very well. However, when only some of the columns in the query are returned, the Entity Framework throws an exception when instantiating the object. A better approach and best practice is to explicitly enumerate all the columns in your query statement (that is, specify all column names).

If your SQL statement returns the number of Riedo required to instantiate an entity (that is, the number of column values is greater than the number of entity object properties), the Entity Framework ignores more columns. If you think about it, it's not a satisfying behavior. Again, explicitly enumerate the column names you expect to return in the SQL statement, making sure they match the entity type .

The SQLQuery () method has many limitations, and if you are using TPH inheritance mappings, the rows returned by your SQL statement are mapped to different derived types, and the Entity Framework cannot use the authentication column to map rows to the correct derived type. You may get a run-time exception because the row may not contain the value that is required to instantiate the type.

Interestingly, we can use the SQLQuery () method to instantiate a type that is not an entity at all. For example, we create a Studentname class that contains only the last name, and a name of two attribute people. If our SQL statement returns only these two columns, we can get the instance collection of type Studentname using the Sqlquery<studentname> () method and the specified SQL statement.

We are careful to use phrases, SQL statements, rather than query statements, because the SQLQuery () method can accept any SQL statement that returns a rowset. This, of course, contains the query statement, but it also contains the SQL statement that executes the stored procedure.

Entity Framework 6 Recipes Chinese Translation series (three)-----chapter III Querying using SQL statements (GO)