3-2使用原生SQL语句查询
问题
你想在实体框架中使用原生的SQL语句，来更新底层数据存储。
解决方案
假设你有一张如图3-2所示的Payment数据库表,使用实体框架设计器工具创建了一个如图3-2所示的模型。
图3-2 Payment表，包含一个供应商的付款信息
图3-3 包含一个Payment实体的模型
为了在底层的Payment表中执行一句和多句SQL语句，可以使用在DbContext类中的属性Database中的ExecuteSQlCommand()方法。 虽然我们能在模型中查询Payment实体，ExecuteSqlCommand方法能让我们直接查询底层的数据库，放弃实体框架的某些特性，比如变化跟踪。我们需要一个简单的模型对象，它包含一个用于执行SQL命令的上下文对象。
下面代码清单3-4执行一句或多句SQL语句
代码清单3-4 执行查询语句
1 // 删除之前的测试数据
2 using ( var context = new EFRecipesEntities())
3 {
4 context.Database.ExecuteSqlCommand( " delete from chapter3.payment " );
5 }
6 // 插入两行数据
7 using ( var context = new EFRecipesEntities())
8 {
9 var sql = @" insert into Chapter3.Payment(Amount, Vendor)
10 values (@Amount, @Vendor) " ; //这里可以使用@p0这样的参数占位符，ado.net为自动为我们创建参数对象
11 var parameters = new DbParameter[]
12 {
13 new SqlParameter {ParameterName = " Amount " , Value = 99.97M },
14 new SqlParameter {ParameterName = " Vendor " , Value = " Ace Plumbing " }
15 };
16
17 var rowCount = context.Database.ExecuteSqlCommand(sql, parameters);
18
19 parameters = new DbParameter[]
20 {
21 new SqlParameter {ParameterName = " Amount " , Value = 43.83M },
22 new SqlParameter
23 {
24 ParameterName = " Vendor " ,
25 Value = " Joe‘s Trash Service "
26 }
27 };
28
29 rowCount += context.Database.ExecuteSqlCommand(sql, parameters);
30 Console.WriteLine( " {0} rows inserted " , rowCount.ToString());
31 }
32
33 // 获取并显示数据
34 using ( var context = new EFRecipesEntities())
35 {
36 Console.WriteLine( " Payments " );
37 Console.WriteLine( " ======== " );
38 foreach ( var payment in context.Payments)
39 {
40 Console.WriteLine( " Paid {0} to {1} " , payment.Amount.ToString(),
41 payment.Vendor);
42 }
43 }
44
45 Console.WriteLine( " \nPress <enter> to continue... " );
46 Console.ReadLine();
代码清单3-4的输出如下：
1 2 rows inserted 2 Payments 3 ======== 4 Paid $ 99.97 to Ace Plumbing 5 Paid $ 43.83 to Joe ‘ s Trash Service
在代码清单3-4中，我们先删除之前的测试数据，然后使用在DbContext类中的属性Database中的ExecuteSQlCommand()方法，请注意这里是如何把一个原生的SQL语句传给这个方法的。
然后，我们创建一个字符串形式的SQL插入语句，这个语句包含了两个参数@Amout和@Vendor。它们只是一个占位符，当SQL语句执行时，会被具体的值给替换掉。
接下来，我们创建了两个DbParameter类型的参数对象，它们把参数占位符和具体的值绑定在一起。第一个插入，我将值99.97绑定到了占位符Amount上,将值"Ace Plumbing"绑定到占位符Vendor上。随后，我们创建了另一个记录。这里需要注意，这两个参数对象是如何分配到一个DbParameter类型的数组上的。为了执行SQL语句，我们传递包含SQL语句的字符串和DbParameter类型的参数数组给方法ExecuteSqlCommand()，方法ExecueSqlCommand返回SQL语句所影响的行数。在我们的示例中，我们每次调用ExecuteSqlCommand()方法插入一行数据。
如果你的SQL语句没有任何参数，ExecuteSqlCommand方法有另一个重载方法，它只包含一个接收字符串形式的SQL语句参数。
代码清单3-4中的模式跟我们在ADO.NET框架中使用SqlClient对象查询数据的方式有点相同。不同的是，我们不需要构造一个连接字符串和显示打开一个数据库连接。实体框架上下文对象会自动完成这项工作。需要注意的是， 这里有两个版本的上下文对象：实体框架5、6和4.x中用于Code-First的DbContext上下文对象，和早期版本中的ObjectContext上下文对象。
需要记住的是， DbContext只是一个简单的包装器或者 [Façade,]（ 译注：不知道这是什么语言，或许是出版社印刷错误，如果你知道，请留言告之，谢谢！从上下文来看，应该是]外观模式[的意思）它包装了ObjectContext上下文对象，使上下文对象更直接和更易于使用。ObjectContext的所有功能仍然有效。
表示SQL命令和参数的方式也有不同。在ADO.NET中的ExecuteNonQuery()方法中，命令文本和参数被设置到Command对象上，但在实体框架中传递给ExecuteSqlCommand()方法的是简单类型的参数。
细心的读者可能已经注意到（这是重点），我们没有查询模型。事实上，正如我们前面提到的那样，我们不需要如图3-3所示的Payment实体。方法ExceuteSqlCommand()只使用DbContext上下文对象，以及连接字符串。
最佳实践
使用参数化SQL语句和不使用参数化SQL语句是一个问题.....。你应该使用参数化的SQL语句，还是动态创建字符串形式的SQL语句呢？最佳实践是，尽可能地使用参数化的SQL语句。原因如下：
1、参数化SQL语句能帮助阻止SQL注入攻击。如果你使用用户界面上文本框控件中的输入字符，通过动态方式拼接SQL语句。那么你可能无意中就把自己暴露给了SQL注入语句，它会严重地损害你的数据库和泄露敏感信息。当你使用参数化的SQL语句时，它会帮你阻止这种情况的发生。
2、如果我们在示例中所示，参数化SQL语句，允许重用SQL语句。重用能让我们的代码更加简洁且易于阅读。
3、很多企业级数据库，像Oracle、DB2以及SQL Server 在某些情况下，它们能凭借参数化查询，重用被解析过的查询语句，即使是参数发生了改变。通过重用，提升了数据库的性能和降低了处理过程。
4、参数化的SQ语句能让代码更有可维护性和可配置性。例如，SQL语句可以来至配置文件中，这可以让你不修改代码就能改变应用程序。
3-3使用原生SQL语句获取对象
问题
你想使用原生SQL语句从数据库获取对象。
解决方案
假设你有如图3-4所示的一个拥有Student实体类型的模型。
图3-4 一个拥有Student实体类型的模型
你想通过执行原生的SQL语句返回实体类型Student的实例集合。正如在前面小节中看到的那样，实体框架中的ExecuteSqlCommand()方法和ADO.NET中SQLCommand的ExcuteNonQuery方法有点相似。它执行给定的SQL语句，返回受影响的行数。为了让实体框架实现无类型数据到强类型的实体转换，我可以使用方法SqlQuery()。
作为开始，本示例凭借实体框架中的Code-First。代码清单3-5，创建了一个Student实体类。
代码清单3-5 Student 实体类
public class Student
{
public int StudentId { get ; set ; }
public string Degree { get ; set ; }
public string FirstName { get ; set ; }
public string LastName { get ; set ; }
}
接下来，代码清单3-6，创建了用于Code-First的上下文对象。
代码清单3-6 上下文对象DbContext
1 public class EFRecipesEntities : DbContext
2 {
3 public EFRecipesEntities()
4 : base ( " ConnectionString " )
5 {
6 }
7
8 public DbSet<Student> Students { get ; set ; }
9
10 protected override void OnModelCreating(DbModelBuilder modelBuilder)
11 {
12 modelBuilder.Entity<Student>().ToTable( " Chapter3.Student " );
13 base .OnModelCreating(modelBuilder);
14 }
15 }
为了执行SQL语句以及返回实体类型Student的实例集合，使用代码清单3-7的模式。
代码清单3-7 使用ExecuteStoreQuery()（译注：应该是使用SqlQuery()方法）方法执行SQL语句并返回对象。
1 using ( var context = new EFRecipesEntities())
2 {
3 // 删除出测试数据
4 context.Database.ExecuteSqlCommand( " delete from chapter3.student " );
5
6 // 插入数据
7 context.Students.Add( new Student
8 {
9 FirstName = " Robert " ,
10 LastName = " Smith " ,
11 Degree = " Masters "
12 });
13 context.Students.Add( new Student
14 {
15 FirstName = " Julia " ,
16 LastName = " Kerns " ,
17 Degree = " Masters "
18 });
19 context.Students.Add( new Student
20 {
21 FirstName = " Nancy " ,
22 LastName = " Stiles " ,
23 Degree = " Doctorate "
24 });
25 context.SaveChanges();
26 }
27
28 using ( var context = new EFRecipesEntities())
29 {
30 var sql = " select * from Chapter3.Student where Degree = @Major " ;
31 var parameters = new DbParameter[]
32 {
33 new SqlParameter {ParameterName = " Major " , Value = " Masters " }
34 };
35 var students = context.Database.SqlQuery<Student> (sql, parameters);
36 Console.WriteLine( " Students... " );
37 foreach ( var student in students)
38 {
39 Console.WriteLine( " {0} {1} is working on a {2} degree " ,
40 student.FirstName, student.LastName, student.Degree);
41 }
42 }
43
44 Console.WriteLine( " \nPress <enter> to continue... " );
45 Console.ReadLine();
46 }
代码清单3-7的输出如下：
1 Students... 2 Robert Smith is working on a Masters degree 3 Julia Kerns is working on a Masters degree
原理
在代码清单3-7中，我们添加了3个Students到DbContext上下文中并使用SaveChanges()方法保存到数据库。
为了获取正在攻读硕士学位的学生，我们使用了SqlQuery()方法、一个参数化的SQL语句，一个被设置成[Masters.[值的参数。我们枚举返回的Stuendts集合并打印输出。注意相关的上下文对象为这些值实现的变化跟踪。
这里在查询语句中使用[*]表示所有的例名，实体框架会将返回的列匹配到合适的属性上。一般情况下，这会工作得很好。但是，查询中只有部分列返回时，实体框架会在实例化对象时抛出一个异常。一个更好的方法和最佳实践是，在你的查询语句中显式枚举所有列（也就是说，指定所有的列名）。
如果你的SQL语句返回的列多于实例化实体所需数量（也就是说，列值数量多于实体对象属性数量），实体框架会忽略掉多于的列。如果你仔细想想，这不是一个令人满意的行为。再一次重申， 在SQL语句中显式枚举你所期望返回的列名，确保它们与实体类型匹配 。
SqlQuery()方法有很多限制，如果你在使用TPH继承映射，你的SQL语句返回的行要映射到不同的派生类型上，实体框架不能使用鉴别列来将行映射到正确的派生类型。你可能会得到一个运行时异常，因为行中可能不包含正在实例化类型所需的值。
有趣的是，我们可以使用SqlQuery()方法实例化根本就不是实体的类型。例如，我们创建一个StudentName类，它只包含姓，和名两个属性民。如果我们的SQL语句也只返回这两个列，我们可以使用SqlQuery<StudentName>()方法和指定的SQL语句获取类型StudentName的实例集合。
我们很小心地使用短语，SQL语句，而不是查询语句，是因为SqlQuer()方法可以接受任何返回行集合的SQL语句。这当然包含查询语句，但也包含执行存储过程的SQL语句。
实体框架交流QQ群: 458326058，欢迎有兴趣的朋友加入一起交流
谢谢大家的持续关注，我的博客地址：http://www.cnblogs.com/VolcanoCloud/
《Entity Framework 6 Recipes》中文翻译系列 (12) -----第三章 查询之使用SQL语句
标签：
查看更多关于《Entity Framework 6 Recipes》中文翻译系列 (12) -----第三章 查的详细内容...