翻译的初衷以及为什么选择《Entity Framework 6 Recipes》来学习,请看本系列开篇
前一章,我们展示了常见数据库场景的建模方式,本章将向你展示如何查询实体数据模型,一般来说,有三种方式:
1、LINQ to Entities;
2、Entity SQL;
3、Native SQL;
我们将在本章演示这三种方式,为了帮助你理解实体框架查询的基本知识,本章覆盖了常见和不常见的场景。同时我们也展示了实体框架6新的查询功能。
你一个长耗时的实体框架查询,当执行查询时,你不想打断应用程序主线程运行,在数据返加之前,能让用户做一些别的操作。同时,使用LINQ to Entities来查询模型也一样重要,它是查询数据库模型的首选方案。
假设你有如图3-1所示的模型。
图3-1 模型中,一个代表助理的Associate的实体类型和一个代表助理工资历史的AssociateSalary实体
在这个模型中,我们有两个代表助理和他们工资历史的实体。
作为开始,我们在示例中使用Code-First方法创建类,在代码清单3-1中,创建这些实体类。
代码清单3-1Associate 和 AssociateSalary实体类型
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 AssociateSalary 12 { 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 }
接下来,代码清单3-2使用Code-First创建DbContentx上下文对象,注意在OnModelCreateing方法中,我们显示地将SalaryId属性映射为AssociateSalary表的主键。当我们使用Code Firtst时,如果一个属性的名字是Id或者<表名>Id,实体框架为假定该属性是对应表的主键。别外,像这里这样,要显示设置主键。
代码清单3-2Dbcontext上下文对象
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 //显示分配实体键为AssociateSalary表的主键,以免实体框架使用默认映射约定 17 modelBuilder.Entity<AssociateSalary>().HasKey(x => x.SalaryId); 18 base.OnModelCreating(modelBuilder); 19 } 20 }
代码清单3-3 演示,如何借助新实体框架的异步方法实现异步查询,删除、加载、获取数据。
代码清单3-3.异步处理实体框架查询
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 // 清除原始数据 44 // 异步执行原始SQL语句 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 // 添加测试数据 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 = 39500M, 68 SalaryDate = DateTime.Parse("8/4/09") 69 }; 70 var salary2 = new AssociateSalary 71 { 72 Salary = 41900M, 73 SalaryDate = DateTime.Parse("2/5/10") 74 }; 75 var salary3 = new AssociateSalary 76 { 77 Salary = 33500M, 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 // 异步保存 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 // 借助 ForEachAsync 方法 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 // 借助 ToListAsync 方法 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 }
代码清单3-3输出如下:
Cleaning Up Previous Test Data ========= Adding Test Data ========= Async ForEach Call ========= Here are the salaries for Associate Janis Roberts: 39500.00 Here are the salaries for Associate Kevin Hodges: 41900.00 Here are the salaries for Associate Bill Jordan: 33500.00 Async ToList Call ========= Here are the salaries for Associate Bill Jordan: 33500.00 Here are the salaries for Associate Janis Roberts: 39500.00 Here are the salaries for Associate Kevin Hodges: 41900.00 Async SingleOrDefault Call ========= Here are the salaries for Associate Kevin Hodges: 41900.00
在这个示例中,我们演示了实体框架的两个关键概念的用途:使用LINQ扩展查询模型以及实体框架6中实现的新的异步功能。
对于绝大多数的查询操作,你都需要用到LINQ。这样做会给你带来,智能提示、编译时检查,以及强类型的编程体验。如果你需要在运行时动态构建查询,你可以考虑使用Entity SQL,它能连接查询表达式各个部分的字符串。你将在本节后面看到相关的示例。
开始时,我们先清除之前数据库中的测试数据。请注意我们是如何把Cleanup()操作包装在一个异步方法中的。然后我们生成原始的SQL语句,并调用新的ExecuteSqlCommandAsync()方法。请注意我们是如何凭借.NET framework4.5中的async/await异步模式。这种模式能够不通过显示实例化一个后台线程来实现异步;此外,它释放当前等待数据库操作完成的CLR线程控制权。(译注:也就是不卡住当前线程 ,让它可以继续执行别的操作)。
接下来,我们加载测试数据Associate和AssoicateSalareies。为了执行异步调用,像前面一样,我们将LoadData()操作包装在一个异步方法中,并通过最新增加的SaveChangesAsync()方法在数据库中插入测试数据。
接下为,我们呈现了三种不同的模型查询方式,每种方式都凭借了实体框架中的LINQ扩展,每种方式都凭借await/async模式包含在一个异步方法中。在RunForEachAsyncExample()方法中,由于没有与foreach语句相匹配异步方法,我们使用了ForEachAsync()扩展方法。凭借这个异步方法以及 Inclued()方法,我们能够异步查询和枚举这些对象。
在随后的RunToListAsyncExample()和RunSingeOrDefaultAsyncExample()查询中,我们凭借ToList()和SingleOrDefault()方法的新的异步方法来实现。
实体框架现在公布了大量的异步操作方法。它们的命名约定是,在已存在的api名称中加上后缀Asyn,使其相对简单地在添加或者获取数据时实现异步。
实体框架交流QQ群: 458326058,欢迎有兴趣的朋友加入一起交流
谢谢大家的持续关注,我的博客地址:http://www.cnblogs.com/VolcanoCloud/