"Entity Framework 6 Recipes" Chinese Translation series-----Chapter III query using Entity SQL

3-4 using the Entity SQL query model

Problem

　　You want to query your Entity Data model and return strongly typed objects by executing an Entity SQL statement.

Solution Solutions

Suppose you have the model shown in Figure 3-5, which contains a customer entity type. This entity type has a name attribute and an email attribute. You will use ENTIYT SQL to query this model.

Figure 3-5 A model with a customer entity type

Using the Entity SQL (ESQL) query model, Entity SQL is a dialect that SQL implements in the Entity Framework, which is exactly what the pattern in Listing 3-8 is used for. When querying the underlying data store, you might prefer linq-to-entity. Because LINQ offers many features and a strong type of programming experience. Entity SQL provides the flexibility to build dynamic queries for underlying data storage through the physical data model.

code Listing 3-8. Executing an Entity SQL statement using Object Services and EntityClient

 1 using (var context = new Efrecipesentities ()) 2 {3//delete test data 4 context. Database.executesqlcommand ("Delete from Chapter3.customer"); 5//Add new test Data 6 var cus1 = new Customer 7 {8 Name = " Robert Stevens ", 9 Email =" [email protected] "};11 var cus2 = New Customer12 {= "Julia Kerns", e-mail = "[EMAIL&NBSP;PR Otected] "};16 var cus3 = new Customer17 { "Nancy whitrock", Email = "[email protected]"};21 context. Customers.add (CUS1); Customers.add (CUS2); Customers.add (CUS3); SaveChanges (); 25}26 27//Use O in ObjectContext objectsBject services28 using (var context = new Efrecipesentities ()) {Console.Write Line ("Querying Customers with esql leveraging Object Services ..."); string ESQL = "Select value C from c Ustomers as C "; 32//Convert DbContext to underlying ObjectContext because DbContext does not provide support for Entity SQL queries Var Customers = ((iobjectcontextadapter) context). Objectcontext.createquery<customer> (esql); (Var Customer in customers) 35 { Console.WriteLine ("{0} ' email is: {1}", PNS customer.) Name, customer.             Email),}39}40 Console.WriteLine (System.Environment.NewLine); 42 43                 Use EntityClient44 using (var conn = new EntityConnection ("Name=efrecipesentities")) 45 {46 Console.WriteLine ("Customers Customers with ESQL leveraging Entity Client ..."); 47                 var cmd = conn. CreateCommand (); Conn. Open (); CommandText = "Select value c from Efrecipesentities.customers as C", and the using (var reader = cmd). ExecuteReader (commandbehavior.sequentialaccess)) (reader.                                            Read ()) Console.WriteLine ("{0} ' email is: {1}", 55 Reader. GetString (1), reader. GetString (2));}57}58}59 Console.WriteLine ("\npress &lt ;enter> to continue ... "); Console.ReadLine (); 62}

The following is the output of code listing 3-8:

Querying Customers with ESQL leveraging Object Services ... Robert Stevens's email is: [email protected]julia Kerns's email is: [email protected]nancy whitrock ' s e-mail is: [Email pro Tected]customers Customers with ESQL leveraging Entity Client ... Robert Stevens's email is: [email protected]julia Kerns's email is: [email protected]nancy whitrock ' s e-mail is: [Email pro Tected]

　　

Principle

With code Listing 3-8, we initially removed the test data from the previous database. We then created three customers, added it to the context object, and called SaveChanges () to insert the data into the database.

Using customer data from the database, we demonstrated two different ways to get data using Entity SQL. In the first method, we use the CreateQuery () method, which is advertised in the legacy ObjectContext context object and uses it to create a ObjectQuery object. Notice how we convert dbcontext into a objectcontextadapter type, And it gets the underlying ObjectContext type (remember, the newest dbcontext wraps up the old Objetcontext to improve the developer's programming experience). We do this because DbContext does not provide direct support for ESQL queries. Also note that we use placeholder value instead of the customer type, and then pass esql as an argument to the CreateQuery () method. When we enumerate the Customers collection, the query is executed in the database, and we output the result set to the console. Because each element in the collection is an instance of the Customer entity type, we can obtain a strongly typed way to use the properties of each element.

In the second approach, we use the Entityclinet library, which is similar to the other client we use with SqlClient or ADO. First create a database connection, then create a command object and open the database connection. Next, we initialize the command object with the Entity SQL statement that we want to execute. Use the ExecuteReader () method to execute the command and get a entitydatareader that is similar to DbDataReader. Finally, we use the Read () method to enumerate the result set.

Note that in code listing 3-8, the Entity SQL statement uses the value keyword. This keyword is useful when we need to get a complete entity. If our Entity SQL statement projects a subset of columns (that is, we use an Entity SQL expression or create some columns) we do not need to use the value keyword. This means that the DbDataRecord is used directly, as shown in code listing 3-9.

code Listing 3-9. Using object Services and EntityClient projection

 1//Using object ervices, no value keyword 2 using (var context = new Efrecipesentities ()) 3 {4 Console.WriteLine ("Customers ..."); 5 String esql = "Select C.name, C.email from Customers as C"; 6 var records = ((iobjectcontextadapter) context). Objectcontext.createquery<dbdatarecord> (ESQL); 7 foreach (Var record in records) 8 {9 var name = Record[0] As String ; var email = record[1] as string;11 Console.WriteLine ("{0} ' email is: {1}", NA me, email);}13}14 Console.WriteLine (); 15//Use EntityClient, no value key Word-using (var conn = new EntityConnection ("Name=efrecipesentities")) + Consol E.writeline ("Customers ..."); var cmd = conn. CreateCommand (); Conn. Open (); cmd.commandtext = @ "SelecT c.name, C.email FROM22 efrecipesentities.customers as C ", and the using (var reader = cmd). ExecuteReader (commandbehavior.sequentialaccess)) (reader.                         Read ()) Console.WriteLine ("{0} ' email is: {1}", 28 Reader. GetString (0), reader. GetString (1)); 29}30}31}

When you use an entity SQL projection, the returned result set is a DbDataRecord that contains all the columns in the projection. With the value keyword, the singleton object returned by the query is the first element in the DbDataRecord.

3-5 finding master table records from table records in master-slave composite structure relationships

Problem
You have two entities with a one-to-many association (a master-slave composite structure relationship). You want to query all entities that have at least one entity associated with it.

Solution Solutions

Suppose you have a model that has a blog (blogpost) and a comment (Comment) associated with it. Some blogs have many comments, some with little or no comments. This model looks like figure 3-6.

Figure 3-6 a model with a blog (blogpost) and a comment (Comment) associated with it

　　

You want to find all the blogs that have comments, you can use LINQ to Entities or Entity SQL. Follow the pattern shown in Listing 3-10.

 1 using (var context = new Efrecipesentities ()) 2 {3//delete test data 4 context. Database.executesqlcommand ("Delete from chapter3.comment"); 5 context. Database.executesqlcommand ("Delete from Chapter3.blogpost");  6//Add new test Data 7 var post1 = new Blogpost 8 {9 Title =                 "The Joy of LINQ", Ten Description = "101 things you always wanted to know about LINQ" 11 };12 var post2 = new BlogPost13 {in Title = "LINQ as Dinner Conversa tion ", Description =" What wine goes with a LAMBDA expression? " };17 var post3 = new BlogPost18 {Title = "LINQ A                 nd our Children ", Description =" What we need to teach LINQ in High School "21};22 var comment1 = new Comment23 {Comments = "great post, I wish more people would talk about LINQ" 25 };26 var comment2 = new Comment27 {Comments = "you ' re right, We should teach LINQ in high school! " };30 Post1. Comments.add (comment1); Post3. Comments.add (Comment2); Blogposts.add (POST1); Blogposts.add (POST2); Blogposts.add (POST3);                 SaveChanges ();}37-using (var context = new Efrecipesentities ()) 39 {40 Console.WriteLine ("Blog Posts with comments ... (LINQ) "); posts var = from post in context. BlogPosts42 where post.                     Comments.any ()-Select post;44 foreach (Var post in posts) 45 {46 Console.writeliNE ("Blog post: {0}", post.) (Var comment in post). Comments) (Console.WriteLine) ("\t{0}", comment.             Comments);}51}52}53 Console.WriteLine (); 55 56 using (var context = new Efrecipesentities ()). {Console.WriteLine ("Blog Posts with COM ments ... (esql) "), the" blogposts var ESQL = "Select Value p from the as p where exists (p.comments)"; R posts = ((iobjectcontextadapter) context).                     Objectcontext.createquery<blogpost> (esql); posts (Var post in) 62 {63 Console.WriteLine ("Blog post: {0}", post.) (Var comment in post). Comments) (Console.WriteLine) ("\t{0}", comment. Comments); 67}68}69}Console.WriteLine ("\npress <enter> to continue ..."); Console.ReadLine (); 73}

The following is the output of code listing 3-10:

Principle

In Listing 3-10, we first delete the previous test data, then insert the new blog and comment to the database, in order to make sure that the query is correct, we have no comment on one of the blog posts.

In a LINQ query, we determine whether a given blog has comments by using the LINQ extension method any () in the WHERE clause. Finds all blogs with the Any () method returning True. In this usage, we enumerate each commented blog that returns true for the Any () method. And that's exactly what we need: a blog with at least one comment.

In the Entity SQL method, we use the SQL exist () operator in the WHERE clause to determine whether a given blog has comments.

Of course, there are other ways to get the same results. For example, we can use the count () method in the Where clause of a LINQ query to check whether the number of comments is greater than 0. In the entity SQL method, we can use count in the WHERE clause (select value 1 from p.comments ) >0. Both of these methods work fine, but the approach in Listing 3-10 is more concise, and from a performance standpoint, any () and exist () do not need to enumerate the entire collection in the server (meaning that when the first comment is found, the process begins to move to the next blog post). However, count () needs to enumerate the entire collection in the server (meaning that, although a comment has been found, it is still necessary to enumerate each comment).

"Entity Framework 6 Recipes" Chinese Translation series-----Chapter III query using Entity SQL