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

Source: Internet
Author: User

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

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

3-12 paging and filtering

Problem

  You want to use paging and filtering to create a query.

Solution

Suppose you have the model 3-13, and the model has a Custormer entity type.

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

 

You have an application that displays Customer Information Based on filtering conditions. Your company has many customers (maybe millions !), To ensure a user experience that responds as much as possible, you want to display only a certain number of customers on each page. Create a query that takes the customer into consideration and returns a controllable Number of result sets by page. See Figure 3-26.

Code List 3-26.A query that contains filtering and paging

1 using (var context = new EFRecipesEntities () 2 {3 // Delete the previous data 4 context. database. executeSqlCommand ("delete from chapter3.customer"); 5 // Add new test data 6 context. MERs. add (new Customer 7 {8 Name = "Robert ts, Jill", 9 Email = "jroberts@abc.com" 10}); 11 context. MERs. add (new Customer12 {13 Name = "Robert tson, Alice", 14 Email = "arob@gmail.com" 15}); 16 context. MERs. add (new Customer17 {18 Name = "Rogers, Steven", 19 Email = "srogers@termite.com" 20}); 21 context. MERs. add (new Customer22 {23 Name = "Roe, Allen", 24 Email = "allenr@umc.com" 25}); 26 context. MERs. add (new Customer27 {28 Name = "Jones, Chris", 29 Email = "cjones@ibp.com" 30}); 31 context. saveChanges (); 32} 33 34 35 using (var context = new EFRecipesEntities () 36 {37 string match = "Ro"; 38 int pageIndex = 0; 39 int pageSize = 3; 40 41 var MERs = context. MERs. where (c => c. name. startsWith (match) 42 // var customers = context. MERs. where (c => c. name. contains (match) 43. orderBy (c => c. name) 44. skip (pageIndex * pageSize) 45. take (pageSize); 46 Console. writeLine ("Customers Ro *"); 47 foreach (var customer in mers) 48 {49 Console. writeLine ("{0} [email: {1}]", customer. name, customer. email); 50} 51} 52 53 using (var context = new EFRecipesEntities () 54 {55 string match = "Ro %"; 56 int pageIndex = 0; 57 int pageSize = 3; 58 59 var esql = @ "select value c from MERs as c 60 where c. name like @ Name61 order by c. name62 skip @ Skip limit @ Limit "; 63 Console. writeLine ("\ nCustomers Ro *"); 64 var customers = (IObjectContextAdapter) context ). objectContext. createQuery <Customer> (esql, new [] 65 {66 new ObjectParameter ("Name", match), 67 new ObjectParameter ("Skip", pageIndex * pageSize ), 68 new ObjectParameter ("Limit", pageSize) 69}); 70 foreach (var customer in mers) 71 {72 Console. writeLine ("{0} [email: {1}]", customer. name, customer. email); 73} 74} 75 76 Console. writeLine ("\ nPress <enter> to continue... "); 77 Console. readLine ();

The output of code list 3-26 is as follows:

Customers Ro*Roberts, Jill [email: jroberts@abc.com]Robertson, Alice [email: arob@gmail.com]Roe, Allen [email: allenr@umc.com]Customers Ro*Roberts, Jill [email: jroberts@abc.com]Robertson, Alice [email: arob@gmail.com]Roe, Allen [email: allenr@umc.com]

 

Principle

In the code list 3-26, we show different methods for this problem. In the first method, we used the LINQ to Entities extension method to create a LINQ query. We use the Where () method to filter the result set. The prefix is "Ro. Because the extension method StartsWith () is used in lambda expressions (). We do not need to use the SQL wildcard expression "Ro % ".

After filtering, we use the OrderBy () method to sort the result set. The sorted result set is obtained through the method Skip. We use the Skip () method to Skip the PageIndex page. The size of each page is PageSize. use the Take () method to obtain a restricted result set (Note: Get the number of records of the specified page size from the result set). We only need to obtain a page of the result set.

Note: In the code block, we created a complete query using the LINQ extension method, instead of the SQL query expression we saw earlier. The Skip () and Take () methods are only published in the extension method, not the query syntax.

In the second method, we construct a complete parameterized Entity SQL expression. This may be the most familiar method, but it is using strings and executable code (C #) to indicate the inherent Mismatch Risk between the two query methods.

 

 

3-13 group by date

Problem

  You have an object that contains the DateTime type attribute. You want to group the instance of the object through the Date part of the DateTime type attribute.

Solution

Suppose you have a model 3-14. The model has a registry entity type, which contains a DateTime type attribute.

Figure 3-14 the model has a registry object type that contains an attribute of the DateTime type.

 

This example uses the Code-First method. In Code list 3-27, we create some entities.

Code List 3-27.Registration entity type

1 public class Registration2 {3     public int RegistrationId { get; set; }4     public string StudentName { get; set; }5     public DateTime? RegistrationDate { get; set; }6 }

Next, the context object is created in Code listing 3-28, which is the entry to access the object framework function using the Code-First method.

Code List 3-28.Context object

 1 public class EFRecipesEntities : DbContext 2 { 3     public EFRecipesEntities() 4         : base("ConnectionString") {} 5     public DbSet<Registration> Registrations { get; set; } 6     protected override void OnModelCreating(DbModelBuilder modelBuilder) 7     { 8         modelBuilder.Entity<Registration>().ToTable("Chapter3.Registration"); 9         base.OnModelCreating(modelBuilder);10     }11 }        

  We use the Date part of the RegistrationDate attribute to group all registrations. You may group by RegistrationDate in LINQ. date. although it can be compiled, you will still get a runtime error. The error description Date cannot be converted to SQL. To use the Date part of the RegistrationDate attribute for grouping, see Code List 3-29.

Code List 3-29.Use the Date part of the DateTime type attribute to group instances.

1 using (var context = new EFRecipesEntities () 2 {3 // Delete the previous test data 4 context. database. executeSqlCommand ("delete from chapter3.registration"); 5 // Add new test data 6 context. registrations. add (new Registration 7 {8 StudentName = "Jill Rogers", 9 RegistrationDate = DateTime. parse ("12/03/2009 9:30 pm") 10}); 11 context. registrations. add (new Registration12 {13 StudentName = "Steven Combs", 14 RegistrationDate = DateTime. parse ("12/03/2009 10:45 am") 15}); 16 context. registrations. add (new Registration17 {18 StudentName = "Robin Rosen", 19 RegistrationDate = DateTime. parse ("12/04/2009 :18 am") 20}); 21 context. registrations. add (new Registration22 {23 StudentName = "Allen Smith", 24 RegistrationDate = DateTime. parse ("12/04/2009 :31 pm") 25}); 26 context. saveChanges (); 27} 28 29 using (var context = new EFRecipesEntities () 30 {31 var groups = from r in context. registrations32 // use the built-in TruncateTime function to extract the Date Part 33 group r by DbFunctions. truncateTime (r. registrationDate) 34 into g35 select g; 36 foreach (var element in groups) 37 {38 Console. writeLine ("\ nRegistrations for {0}", 39 (DateTime) element. key ). toShortDateString (); 40 foreach (var registration in element) 41 {42 Console. writeLine ("\ t {0}", registration. studentName); 43} 44} 45} 46 47 Console. writeLine ("\ nPress <enter> to continue... "); 48 Console. readLine ();

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

Registrations for 12/3/2009Jill RogersSteven CombsRegistrations for 12/4/2009Robin RosenAllen Smit

 

Principle

The Group Key for the registrations group is to use the Truncate () function to extract the Date part of the RegistrationDate attribute. This is a built-in function of the object framework, which is included in the DbFunctions class. It extracts only the Date part of DateTime.The built-in DbFunctions class contains a large number of formatting, aggregation, string operations, date and digital services. It is in the namespace System. Data. Entity. The legacy class EntityFunctios is used in versions earlier than ef6. it can still be used in EF6, but you will get a compilation warning. It is recommended that you use the closest DbFunctions class.We will continue to discuss it in chapter 11.

 

 

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/

 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.