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

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

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

Chapter 3 Query

The previous chapter shows modeling methods for common database scenarios. This chapter shows you how to query object data models. Generally, there are three methods:

1. LINQ to Entities;

2. Entity SQL;

3. Native SQL;

We will demonstrate these three methods in this chapter. To help you understand the basic knowledge of object framework queries, this chapter covers common and uncommon scenarios. At the same time, we also showed the new query function of Entity Framework 6.

 

3-1. asynchronous Query

You have a long time-consuming Entity Framework query. When you execute a query, you do not want to interrupt the main thread of the application to run. Before the data is added back, you can perform other operations. At the same time, using LINQ to Entities to query a model is equally important. It is the preferred solution for querying a database model.

Solution

Suppose you have a model shown in 3-1.

Figure 3-1 An AssociateSalary entity representing the assistant's Associate and an AssociateSalary entity representing the assistant's salary history

　　

In this model, we have two entities representing the assistant and their salary history.

In the example, we use the Code-First method to create classes. In the Code listing 3-1, we create these entity classes.

Code List 3-1Associate and AssociateSalary Object Types

 1 public class Associate 2     { 3         public Associate() 4         { 5             AssociateSalaries = new HashSet<AssociateSalary>(); 6         } 7         public int AssociateId { get; set; } 8         public string Name { get; set; } 9         public virtual ICollection<AssociateSalary> AssociateSalaries { get; set; }10     }11     public class AssociateSalary12     {13         public int SalaryId { get; set; }14         public int AssociateId { get; set; }15         public decimal Salary { get; set; }16         public DateTime SalaryDate { get; set; }17         public virtual Associate Associate { get; set; }18     }

 

Next, the Code listing 3-2 uses Code-First to create the DbContentx context object. Note that in the OnModelCreateing method, we will map the SalaryId attribute to the primary key of the AssociateSalary table. When we use Code Firtst,If an attribute is named Id or <Table Name> Id, the object framework assumes that this attribute is the primary key of the corresponding table.. In this case, you need to display the Set primary key.

Code List 3-2Dbcontext context object

1 public class EFRecipesEntities: DbContext 2 {3 public EFRecipesEntities () 4: base ("ConnectionString") 5 {6} 7 8 public DbSet <Associate> Associates {get; set ;} 9 public DbSet <AssociateSalary> AssociateSalaries {get; set;} 10 11 protected override void OnModelCreating (DbModelBuilder modelBuilder) 12 {13 modelBuilder. entity <Associate> (). toTable ("Chapter3.Associate"); 14 modelBuilder. entity <AssociateSalary> (). toTable ("Chapter3.AssociateSalary"); 15 16 // The allocated object key is the primary key of the AssociateSalary table to avoid the Entity Framework using the default ing Convention 17 modelBuilder. entity <AssociateSalary> (). hasKey (x => x. salaryId); 18 base. onModelCreating (modelBuilder); 19} 20}

 

Code List 3-3 demonstrates how to use the Asynchronous Method of the new object framework to Implement Asynchronous queries, delete, load, and obtain data.

 

Code List 3-3.Asynchronous processing of object framework queries

 

1 private static void Main () 2 {3 var asyncTask = EF6AsyncDemo (); 4 5 foreach (var c in BusyChars () 6 {7 if (asyncTask. isCompleted) 8 {9 break; 10} 11 Console. write (c); 12 Console. cursorLeft = 0; 13 Thread. sleep (100); 14} 15 Console. writeLine ("\ nPress <enter> to continue... "); 16 Console. readLine (); 17} 18 19 private static IEnumerable <char> BusyChars () 20 {21 while (true) 22 {23 yield return '\\'; 24 yield return '|'; 25 yield return '/'; 26 yield return '-'; 27} 28} 29 30 private static async Task EF6AsyncDemo () 31 {32 await Cleanup (); 33 await LoadData (); 34 await RunForEachAsyncExample (); 35 await RunToListAsyncExampe (); 36 await RunSingleOrDefaultAsyncExampe (); 37} 38 39 private static async Task Cleanup () 40 {41 using (var context = new EFRecipesEntities ()) 42 {43 // clear Raw Data 44 // asynchronously execute the original SQL statement 45 Console. writeLine ("Cleaning Up Previous Test Data"); 46 Console. writeLine ("===========\ n"); 47 48 await context. database. executeSqlCommandAsync ("delete from chapter3.AssociateSalary"); 49 await context. database. executeSqlCommandAsync ("delete from chapter3.Associate"); 50 await Task. delay (5000); 51} 52} 53 54 private static async Task LoadData () 55 {56 using (var context = new EFRecipesEntities ()) 57 {58 // Add test data 59 Console. writeLine ("Adding Test Data"); 60 Console. writeLine ("===========\ n"); 61 62 var assoc1 = new Associate {Name = "Janis Roberts "}; 63 var assoc2 = new Associate {Name = "Kevin Hodges"}; 64 var assoc3 = new Associate {Name = "Bill Jordan "}; 65 var salary1 = new AssociateSalary 66 {67 Salary = 39500 M, 68 SalaryDate = DateTime. parse ("8/4/09") 69}; 70 var salary2 = new AssociateSalary 71 {72 Salary = 41900 M, 73 SalaryDate = DateTime. parse ("2/5/10") 74}; 75 var salary3 = new AssociateSalary 76 {77 Salary = 33500 M, 78 SalaryDate = DateTime. parse ("10/08/09") 79}; 80 assoc1.AssociateSalaries. add (salary1); 81 assoc2.AssociateSalaries. add (salary2); 82 assoc3.AssociateSalaries. add (salary3); 83 context. associates. add (assoc1); 84 context. associates. add (assoc2); 85 context. associates. add (assoc3); 86 87 // asynchronously save 88 await context. saveChangesAsync (); 89 await Task. delay( 5000); 90} 91} 92 93 private static async Task RunForEachAsyncExample () 94 {95 using (var context = new EFRecipesEntities () 96 {97 Console. writeLine ("Async ForEach Call"); 98 Console. writeLine ("=========="); 99 100 // use the ForEachAsync method 101 await context. associates. include (x => x. associateSalaries ). forEachAsync (x => 102 {103 Console. writeLine ("Here are the salaries for Associate {0}:", x. name); 104 105 foreach (var salary in x. associateSalaries) 106 {107 Console. writeLine ("\ t {0}", salary. salary); 108} 109}); 110 await Task. delay (5000); 111} 112} 113 114 private static async Task RunToListAsyncExampe () 115 {116 using (var context = new EFRecipesEntities () 117 {118 Console. writeLine ("\ n \ nAsync ToList Call"); 119 Console. writeLine ("=========="); 120 121 // use the ToListAsync method 122 var associates = await context. associates. include (x => x. associateSalaries ). orderBy (x => x. name ). toListAsync (); 123 124 foreach (var associate in associates) 125 {126 Console. writeLine ("Here are the salaries for Associate {0}:", associate. name); 127 foreach (var salaryInfo in associate. associateSalaries) 128 {129 Console. writeLine ("\ t {0}", salaryInfo. salary); 130} 131} 132 await Task. delay (5000); 133} 134} 135 136 private static async Task RunSingleOrDefaultAsyncExampe () 137 {138 using (var context = new EFRecipesEntities () 139 {140 Console. writeLine ("\ n \ nAsync SingleOrDefault Call"); 141 Console. writeLine ("=========="); 142 143 var associate = await context. associates.144 Include (x => x. associateSalaries ). 145 OrderBy (x => x. name ). 146 FirstOrDefaultAsync (y => y. name = "Kevin Hodges"); 147 148 Console. writeLine ("Here are the salaries for Associate {0}:", associate. name); 149 foreach (var salaryInfo in associate. associateSalaries) 150 {151 Console. writeLine ("\ t {0}", salaryInfo. salary); 152} 153 await Task. delay (5000); 154} 155}

 

The output from code list 3-3 is as follows:

Cleaning Up Previous Test Data=========Adding Test Data=========Async ForEach Call=========Here are the salaries for Associate Janis Roberts:39500.00Here are the salaries for Associate Kevin Hodges:41900.00Here are the salaries for Associate Bill Jordan:33500.00Async ToList Call=========Here are the salaries for Associate Bill Jordan:33500.00Here are the salaries for Associate Janis Roberts:39500.00Here are the salaries for Associate Kevin Hodges:41900.00Async SingleOrDefault Call=========Here are the salaries for Associate Kevin Hodges:41900.00

 

Principle

In this example, we demonstrate the usage of two key concepts of the object framework: Extended query model using LINQ and new asynchronous functions implemented in Entity Framework 6.

For the vast majority of query operations, you need to use LINQ. This will bring you smart prompts, compile-time checks, and a strong programming experience. If you need to dynamically construct a query at runtime, you can consider using Entity SQL, which can connect the strings of each part of the query expression. You will see related examples later in this section.

At the beginning, we first clear the test data in the previous database. Note how we wrap the Cleanup () operation in an Asynchronous Method. Then we generate the original SQL statement and call the new ExecuteSqlCommandAsync () method. Note how we use async/await asynchronous mode in. NET framework4.5. This mode can be implemented asynchronously without displaying and instantiating a background thread. In addition, it releases control of the CLR thread waiting for the database operation to complete. This means that the current thread is not stuck, so that it can continue to execute other operations ).

Next, we load the test data Associate and AssoicateSalareies. To execute asynchronous calls, we wrap the LoadData () operation in an Asynchronous Method and insert test data into the database using the newly added SaveChangesAsync () method.

Next, we present three different model query methods, each of which relies on the LINQ extension in the object framework, and each of which is included in an Asynchronous Method Using the await/async mode. In the RunForEachAsyncExample () method, the ForEachAsync () extension method is used because it does not match the Asynchronous Method of the foreach statement. With this Asynchronous Method and the received () method, we can asynchronously query and enumerate these objects.

In subsequent RunToListAsyncExample () and RunSingeOrDefaultAsyncExample () queries, we use the new asynchronous methods of ToList () and SingleOrDefault () methods.

The Entity Framework has published a large number of asynchronous operation methods. Their Naming Convention is to add the suffix Asyn to an existing api name so that it is relatively simple to implement Asynchronization when adding or retrieving data.

 

 

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/