Entity Framework 6 Recipes Chinese translation series (13), entityframework

Entity Framework 6 Recipes Chinese translation series (13), entityframework

For the original intention of translation and why I chose Entity Framework 6 Recipes, see the beginning of this series.

3-4 use an Entity SQL query model

Problem

　　You want to query your object data model and return a strongly typed object by executing the Entity SQL statement.

Solution

Suppose you have a model shown in Figure 3-5, which contains a Customer entity type. This object type has a Name attribute and an Email attribute. You need to use Entiyt SQL to query this model.

Figure 3-5 contains a model of the Customer entity type

 

The Entity SQL (eSQL) query model is used. Entity SQL is a dialect implemented by SQL in the Entity Framework. This method is exactly used in the Mode 3-8 in the code list. When querying the underlying data storage, you may prefer LINQ-to-Entity. Because LINQ provides many features and a strong programming experience. Entity SQL provides flexibility in creating dynamic queries for underlying data storage through the Entity Data Model.

Code List 3-8.Use Object Services and EntityClient to execute an Entity SQL statement

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 Steven s ", 9 Email = "rstevens@mymail.com" 10}; 11 var cus2 = new Customer12 {13 Name = "Julia Kerns", 14 Email = "julia.kerns@abc.com" 15 }; 16 var cus3 = new Customer17 {18 Name = "Nancy Whitrock", 19 Email = "nrock@myworld.com" 20}; 21 context. MERs. add (cus1); 22 context. MERs. add (cus2); 23 context. MERs. add (cus3); 24 context. saveChanges (); 25} 26 27 // use the object services28 using (var context = new EFRecipesEntities () 29 {30 Console in the ObjectContext object. writeLine ("Querying Customers with eSQL Leveraging Object Services... "); 31 string esql =" select value c from MERs as c "; 32 // convert DbContext to the underlying ObjectContext, because DbContext does not support Entity SQL queries 33 var MERs = (IObjectContextAdapter) context ). objectContext. createQuery <Customer> (esql); 34 foreach (var customer in mers) 35 {36 Console. writeLine ("{0}'s email is: {1}", 37 customer. name, customer. email); 38} 39} 40 41 Console. writeLine (System. environment. newLine); 42 43 // use EntityClient44 using (var conn = new EntityConnection ("name = EFRecipesEntities") 45 {46 Console. writeLine ("MERs Customers with eSQL Leveraging Entity Client... "); 47 var cmd = conn. createCommand (); 48 conn. open (); 49 cmd. commandText = "select value c from EFRecipesEntities. customers as c "; 50 using (var reader = cmd. executeReader (CommandBehavior. sequentialAccess) 51 {52 while (reader. read () 53 {54 Console. writeLine ("{0}'s email is: {1}", 55 reader. getString (1), reader. getString (2); 56} 57} 58} 59 60 Console. writeLine ("\ nPress <enter> to continue... "); 61 Console. readLine (); 62}

Below is the output of code listing 3-8:

Querying Customers with eSQL Leveraging Object Services...Robert Stevens's email is: rstevens@mymail.comJulia Kerns's email is: julia.kerns@abc.comNancy Whitrock's email is: nrock@myworld.comCustomers Customers with eSQL Leveraging Entity Client...Robert Stevens's email is: rstevens@mymail.comJulia Kerns's email is: julia.kerns@abc.comNancy Whitrock's email is: nrock@myworld.com

　　

Principle

In code list 3-8, we first deleted the test data from the database. Then we create three MERs, add them to the context object, and call SaveChanges () to insert the data to the database.

Using customer data in the database, we demonstrate two different methods of getting data using Entity SQL. In the first method, we use the CreateQuery () method, which is published in the legacy ObjectContext context object and used to create an ObjectQuery object. Note: How do we convert DbContext to an ObjectContextAdapter type and get the underlying ObjectContext type through it (remember that the latest DbContext encapsulates the old Objetcontext, to improve the developer's programming experience ). This is because DbContext does not directly support eSQL queries. At the same time, we should also note that we use placeholder value to replace the Customer type, and then pass esql as a parameter to the CreateQuery () method. When we enumerate the MERs set, the query is executed in the database, and the result set is output to the console. Because each element in the set is an instance of the Customer entity type, we can obtain a strongly typed way to use the attributes of each element.

In the second method, we use the EntityClinet library, which is similar to other clients provided by SqlClient or ADO. NET. Create a database connection, create a command object, and open the database connection. Next, we use the Entity SQL statement to execute to initialize the command object. Execute command using ExecuteReader () and obtain an EntityDataReader, which is similar to DbDataReader. Finally, we use the Read () method to enumerate the result set.

Note: In code listing 3-8, the value keyword used by the Entity SQL statement. This keyword is useful when we need to obtain the complete entity. If our Entity SQL statement projects a subset of a column (that is, we use Entity SQL expressions to use or create some columns), we do not need to use the value keyword. This means that DbDataRecord is directly used as demonstrated in code listing 3-9.

Code List 3-9.Use Object Services and EntityClient projection

1 // use 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 MERs 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; 10 var email = record [1] as string; 11 Console. writeLine ("{0}'s email is: {1}", name, email); 12} 13} 14 Console. writeLine (); 15 // use EntityClient, without the value keyword 16 using (var conn = new EntityConnection ("name = EFRecipesEntities") 17 {18 Console. writeLine ("Customers... "); 19 var cmd = conn. createCommand (); 20 conn. open (); 21 cmd. commandText = @ "select c. name, C. email from22 EFRecipesEntities. customers as c "; 23 using (var reader = cmd. executeReader (CommandBehavior. sequentialAccess) 24 {25 while (reader. read () 26 {27 Console. writeLine ("{0}'s email is: {1}", 28 reader. getString (0), reader. getString (1); 29} 30} 31}

 

When you use Entity SQL projection, the returned result set is a DbDataRecord containing all columns in the projection. Using the value keyword, a separate object returned by the query is the first element in DbDataRecord.

 

3-5 search for master table records with slave table records in the master-slave Composite Structure

Problem
You have two one-to-multiple Associations (master-slave composite relationships. You need to query all objects that have at least one object associated with it.

Solution

Suppose you have a model with a blog (BlogPost) and a Comment associated with it. Some blogs have many comments, and some have few or no comments. This model looks like Figure 3-6.

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

　　

To find out all blogs with comments, you can use LINQ to Entities or Entity SQL. Follow the mode demonstrated in code 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 ", 10 Description = "101 things you always wanted to know about LINQ" 11}; 12 var post2 = new BlogPost13 {14 Title = "LINQ as D Inner Conversation ", 15 Description =" What wine goes with a Lambda expression? "16}; 17 var post3 = new BlogPost18 {19 Title =" LINQ and our Children ", 20 Description =" Why we need to teach LINQ in High School "21 }; 22 var comment1 = new Comment23 {24 Comments = "Great post, I wish more people wowould talk about LINQ" 25 }; 26 var comment2 = new Comment27 {28 Comments = "You're right, we showould teach LINQ in high school! "29}; 30 post1.Comments. add (comment1); 31 post3.Comments. add (comment2); 32 context. blogPosts. add (post1); 33 context. blogPosts. add (post2); 34 context. blogPosts. add (post3); 35 context. saveChanges (); 36} 37 38 using (var context = new EFRecipesEntities () 39 {40 Console. writeLine ("Blog Posts with comments... (LINQ) "); 41 var posts = from post in context. blogPosts42 where post. comments. any () 43 select post; 44 foreach (var post in posts) 45 {46 Console. writeLine ("Blog Post: {0}", post. title); 47 foreach (var comment in post. comments) 48 {49 Console. writeLine ("\ t {0}", comment. comments); 50} 51} 52} 53 54 Console. writeLine (); 55 56 using (var context = new EFRecipesEntities () 57 {58 Console. writeLine ("Blog Posts with comments... (eSQL) "); 59 var esql =" select value p from BlogPosts as p where exists (p. comments) "; 60 var posts = (IObjectContextAdapter) context ). objectContext. createQuery <BlogPost> (esql); 61 foreach (var post in posts) 62 {63 Console. writeLine ("Blog Post: {0}", post. title); 64 foreach (var comment in post. comments) 65 {66 Console. writeLine ("\ t {0}", comment. comments); 67} 68} 69} 70 71 Console. writeLine ("\ nPress <enter> to continue... "); 72 Console. readLine (); 73}

Below is the output of code listing 3-10:

 1 Blog Posts with comments...(LINQ) 2 Blog Post: The Joy of LINQ 3 Great post, I wish more people would talk about LINQ 4 Blog Post: LINQ and our Children 5 You're right, we should teach LINQ in high school! 6 Blog Posts with comments...(ESQL) 7 Blog Post: The Joy of LINQ 8 Great post, I wish more people would talk about LINQ 9 Blog Post: LINQ and our Children10 You're right, we should teach LINQ in high school! 

 

Principle

In code list 3-10, we first Delete the previous test data, and then insert a new blog and comment to the database. To ensure that the query is correct, we leave a blog with no comment.

In a LINQ query, we use the LINQ Extension Method Any () in the where clause to determine whether a given blog has Any comments. Find all blogs whose Any () method returns true. In this usage, we enumerate every blog with comments whose Any () method returns true. Furthermore, this is exactly what we need: a blog that contains at least one comment.

In the Entity SQL method, we use the SQL exist () operator in the where clause to determine whether a blog has any 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 the LINQ query to check whether the number of comments is greater than 0. in the Entity SQL method, we can use count (select value 1 from p. comments)> 0. Both methods can run normally, but the methods in code listing 3-10 are more concise. From the performance perspective, Any () and Exist () you do not need to enumerate the entire set on the server (that is, when the first comment is found, the processing starts to be transferred to the next blog ). However, Count () needs to enumerate the entire set on the server (meaning that every comment should be enumerated even though a comment has been found ).

 

Entity Framework exchange QQ group: 458326058. You are welcome to join us.

Thank you for your continued attention, my blog address: http://www.cnblogs.com/VolcanoCloud/