.NET面试题系列[14] - LINQ to SQL与IQueryable
.NET面试题系列目录
名言警句
"理解IQueryable的最简单方式就是，把它看作一个查询，在执行的时候，将会生成结果序列。" - Jon Skeet
LINQ to Object和LINQ to SQL有何区别？
LINQ to SQL可以将查询表达式转换为SQL语句，然后在数据库中执行。相比LINQ to Object，则是将查询表达式直接转化为Enumerable的一系列方法，最终在C#内部执行。LINQ to Object的数据源总是实现IEnumerable<T>（所以不如叫做LINQ to IEnumerable<T>），相对的，LINQ to SQL的数据源总是实现IQueryable<T>并使用Queryable的扩展方法。
将查询表达式转换为SQL语句并不保证一定可以成功。
IQueryable
理解IQueryable的最简单方式就是，把它看作一个查询，在执行的时候，将会生成结果序列。
IQueryable是一个继承了IEnumerable接口的另一个接口。
Queryable是一个静态类型，它集合了许多扩展方法，扩展的目标是IQueryable和IEnumerable。它令IQueryable和IEnumerable一样，拥有强大的查询能力。
AsQueryable方法将IEnumerable<T>转换为IQueryable<T>。
var seq = Enumerable.Range(0, 9).ToList(); IEnumerable<int> seq2 = seq.Where(o => o > 5); IQueryable<int> seq3 = seq.Where(o => o > 4).AsQueryable();
模拟接口实现一个简单的LINQ to SQL
下面试图实现一个非常简单的查询提供器（即LINQ to xxx），其可以将简单的where lambda表达式转换为SQL，功能非常有限。在LINQ to SQL中lambda表达式首先被转化为表达式树，然后再转换为SQL语句。
我们试图实现一个可以将where这个lambda表达式翻译为SQL语句的查询提供器。
准备工作
首先在本地建立一个数据库，然后建立一个简单的表。之后，再插入若干测试数据。用于测试的实体为：
public class Staff { public int Id { get; set; } public string Name { get; set; } public string Sex { get; set; } }
由于VS版本是逆天的2010，且没有EF，我采用了比较原始的方法，即建立一个mdf格式的本地数据库。大家可以使用EF或其他方式。
public class DbHelper : IDisposable { private SqlConnection _conn; public bool Connect() { _conn = new SqlConnection { ConnectionString = "Data Source=.\\SQLEXPRESS;" + "AttachDbFilename=Your DB Path" + "Integrated Security=True;Connect Timeout=30;User Instance=True" }; _conn.Open(); return true; } public void ExecuteSql(string sql) { SqlCommand cmd = new SqlCommand(sql, _conn); cmd.ExecuteNonQuery(); } public List<Staff> GetEmployees(string sql) { List<Staff> employees = new List<Staff>(); SqlCommand cmd = new SqlCommand(sql, _conn); SqlDataReader sdr = cmd.ExecuteReader(); while (sdr.Read()) { employees.Add(new Staff{ Id = sdr.GetInt32(0), Name = sdr.GetString(1), Sex = sdr.GetString(2) }); } return employees; } public void Dispose() { _conn.Close(); _conn = null; } }
这个非常简陋的DbHelper拥有连接数据库，简单执行sql语句（不需要返回值，用于DDL或delete语句）和通过执行Sql语句，返回若干实体的功能（用于select语句）。
public static List<Staff> Employees; static void Main(string[] args) { using (DbHelper db = new DbHelper()) { db.Connect(); //db.ExecuteSql("CREATE TABLE Staff ( Id int, Name nvarchar(10), Sex nvarchar(1))"); db.ExecuteSql("DELETE FROM Staff"); db.ExecuteSql("INSERT INTO Staff VALUES (1, 'Frank','M')"); db.ExecuteSql("INSERT INTO Staff VALUES (2, 'Mary','F')"); Employees = db.GetEmployees("SELECT * FROM Staff"); } Console.ReadKey(); }
在主函数中我们执行建表（只有第一次才需要），删除记录，并插入两行新纪录的工作。最后，我们选出新纪录并存在List中，这样我们的准备工作就做完了。我们的目标是解析where表达式，将其转换为SQL，然后调用ExecuteSql方法返回数据，和通过直接调用where进行比较。
实现IQueryable<T>
首先我们自建一个类别FrankQueryable，继承IQueryable<T>。因为IQueryable<T>继承了IEnumerable<T>，所以我们一样要实现GetEnumerator方法。只有当表达式需要被计算时，才会调用GetEnumerator方法（例如纯Select就不会）。另外，IQueryable<T>还有三个属性：
- Expression：这个很好理解，就是要处理的表达式
- Type
- IQueryProvider：你自己的IQueryProvider。在构造函数中，需要传入自己的IQueryProvider实现自己的逻辑。
public class FrankQueryable<T> : IQueryable<T> { public IEnumerator<T> GetEnumerator() { throw new NotImplementedException(); } IEnumerator IEnumerable.GetEnumerator() { return GetEnumerator(); } public Expression Expression { get; private set; } public Type ElementType { get; private set; } public IQueryProvider Provider { get; private set; } public FrankQueryable() { } }
我们需要实现构造函数和GetEnumerator方法。
实现IQueryProvider
构建一个自己的查询提供器需要继承IQueryable<T>。查询提供器将会做如下事情：
- 调用CreateQuery建立一个查询，但不计算。只在需要的时候才进行计算。
- 如果需要执行表达式的计算（例如调用了ToList），此时调用GetEnumerator，触发Execute的执行，从而计算表达式。我们需要把自己的逻辑写在Execute方法中。并在GetEnumerator中进行调用。
我们要自己写一个简单的查询提供器，所以我们要写一个IQueryProvider，然后在构造函数中传入。我们再次新建一个类型，继承IQueryProvider，此时我们又需要实现四个方法。其中非泛型版本的两个方法可以暂时不用理会。
public class FrankQueryProvider : IQueryProvider { public IQueryable CreateQuery(Expression expression) { throw new NotImplementedException(); } public IQueryable<TElement> CreateQuery<TElement>(Expression expression) { throw new NotImplementedException(); } public object Execute(Expression expression) { throw new NotImplementedException(); } public TResult Execute<TResult>(Expression expression) { throw new NotImplementedException(); } }
此时FrankQueryable类型的构造函数可以将属性赋成适合的值，它变成这样了：
public FrankQueryable(Expression expression, FrankQueryProvider provider) { Expression = expression; ElementType = typeof(T); Provider = provider; }
其中CreateQuery方法的实现很简单。
public IQueryable<TElement> CreateQuery<TElement>(Expression expression) { Console.WriteLine("Going to CreateQuery"); return new FrankQueryable<TElement>(this, expression); }
然后，我们可以实现FrankQueryable的GetEnumerator方法，它的目的在于呼叫其配套的provider中的Execute方法，从而令我们自己的逻辑得以执行（我们已经在构造函数中传入了自己的provider）：
public IEnumerator<T> GetEnumerator() { Console.WriteLine("Begin to iterate."); var result = Provider.Execute<List<T>>(Expression); foreach (var item in result) { Console.WriteLine(item); yield return item; } }
另外为方便起见，我们加入一个无参数的构造函数，其会先调用有参的构造函数，然后再执行它自己，将表达式设为一个默认值：
public FrankQueryable() : this(new FrankQueryProvider(), null) { //this is T Expression = Expression.Constant(this); }
最后就是FrankQueryProvider的Execute方法了，它的实现需要我们自己手动解析表达式。所以我们可以建立一个ExpressionTreeToSql类，并在Execute方法中进行调用。
public TResult Execute<TResult>(Expression expression) { string sql = ""; //通过某种方式获得sql（谜之代码） //ExpressionTreeToSql Console.WriteLine(sql); using (DbHelper db = new DbHelper()) { db.Connect(); dynamic ret = db.GetEmployees(sql); return (TResult) ret; } }
假设我们获得了正确的SQL语句，那么接下来的事情当然就是连接数据库获得结果了。这个已经是现成的了，那么当然最后也是最关键的一步就是解析表达式获得SQL语句了。
注意，CreateQuery每次都产生新的表达式对象，不管相同的表达式是否已经存在，这构成了对表达式进行缓存的动机。
测试IQueryable的运行流程
在进行解析之前，假设我们先把SQL语句写死，那么我们将会获得正确的输出：
public TResult Execute<TResult>(Expression expression) { string sql = "select * from staff where Name = 'Frank'"; Console.WriteLine(sql); using (DbHelper db = new DbHelper()) { db.Connect(); dynamic ret = db.GetEmployees(sql); return (TResult) ret; } }
主程序：
static void Main(string[] args) { using (DbHelper db = new DbHelper()) { db.Connect(); //db.ExecuteSql("CREATE TABLE Staff ( Id int, Name nvarchar(10), Sex nvarchar(1))"); db.ExecuteSql("DELETE FROM Staff"); db.ExecuteSql("INSERT INTO Staff VALUES (1, 'Frank','M')"); db.ExecuteSql("INSERT INTO Staff VALUES (2, 'Mary','F')"); Employees = db.GetEmployees("SELECT * FROM Staff"); } var aa = new FrankQueryable<Staff>(); //Try to translate lambda expression (where) var bb = aa.Where(t => t.Name == "Frank"); Console.WriteLine("Going to compute the expression."); var cc = bb.ToList(); Console.WriteLine("cc has {0} members.", cc.Count); Console.WriteLine("Id is {0}, and sex is {1}", cc[0].Id, cc[0].Sex); Console.ReadKey(); }
此时我们发现，程序的行为将按照我们的查询提供器来走，而不是默认的IQueryable。（默认的提供器不会打印任何东西）我们的打印结果是：
Going to CreateQuery Going to compute the expression. Begin to iterate. select * from staff where Name = 'Frank' FrankORM.Staff cc has 1 members. Id is 1, and sex is M
当程序运行到
var bb = aa.Where(t => t.Name == "Frank");
这里时，会先调用泛型的CreateQuery方法（因为aa对象的类型是FrankQueryable<T>所以我们会进入自己的查询提供器，而Where是Queryable的扩展方法所以FrankQueryable自动拥有），然后输出Going to CreateQuery。然后，因为此时并不计算表达式，所以不会紧接着就进入Execute方法。之后主程序继续运行，打印Going to compute the expression.
之后，在主程序的下一行，由于我们调用了ToList方法，此时必须要计算表达式了，故程序开始进行迭代，调用GetEnumerator方法，打印Begin to iterate，然后调用Execute方法，仍然是使用我们自己的查询提供器的逻辑，执行SQL，输出正确的值。
通过这次测试，我们了解到了整个IQueryable的工作流程。由于Queryable那一大堆扩展方法，我们可以轻而易举的获得强大的查询能力。那么现在当然就是把SQL解析出来，填上整个流程最后的一块拼图。
我们将解析方法放入ExpressionTreeToSql类中，并将其命名为VisitExpression。这个类是自己写ORM必不可少的，有时也通称为ExpressionVisitor类。
解析Where lambda表达式：第一步
我们的输入是一个lambda表达式，它是长这样的：
var bb = aa.Where(t => t.Name == "Frank");
我们的目标则是这样的：
Select * from Staff where Name = ‘Frank’
其中Staff，Name和Frank是我们需要从外界获得的，其他则都是语法固定搭配。所以我们需要一个解析表达式的方法，它接受一个表达式作为输入，然后输出一个字符串。通过表达式我们可以获得Name和Frank这两个值。而我们还需要知道目标实体类的类型名称Staff，所以我们的解析方法还需要接受一个泛型T。
另外，由于我们的解析方法很有可能是递归的(因为要解析表达式树)，我们的输出还需要用ref加以修饰。所以这个解析方法的签名为：
public static void VisitExpression<T>(T enumerable, Expression expression, ref string sql)
获得Select * from Staff这一步是比较容易的：
public static string GenerateSelectHeader<T>(T type) { var typeName = type.GetType().Name.Replace("\"", ""); return string.Format("select * from {0} ", typeName); }
我们的解析方法首先要加上：
public static void VisitExpression<T>(T enumerable, Expression expression, ref string sql) { if (sql == String.Empty) sql = GenerateSelectHeader(enumerable); }
当然这里我们也默认设定是选取实体所有的列了。如果是选取一部分，则还需要解析select表达式。
回到Execute方法，现在谜之代码也就浮出水面了，它不过是：
ExpressionTreeToSql.VisitExpression(new Staff(), expression, ref sql);
解析Where lambda表达式：第二步
解析的第二步就是where这个表达式了。首先我们要知道它的NodeType（即类型，Type是表达式最终计算结果值的类型）。通过设置断点，我们看到类型是Call类型，所以我们需要将表达式转为MethodCallExpression（否则我们将无法获得任何细节内容，这对于所有类型的表达式都一样）。
现在我们获得了where这个方法名。
switch (expression.NodeType) { case ExpressionType.Call: MethodCallExpression method = expression as MethodCallExpression; if (method != null) { sql += method.Method.Name; } break; default: throw new NotSupportedException(string.Format("This kind of expression is not supported, {0}", expression.NodeType)); }
现在我们可以运行程序了，当然，结果sql是错误的，我们的解析还没结束，通过设置断点检查表达式的各个变量，我们发现Argument[1]是表达式本身，于是我们通过递归继续解析这个表达式：
我们可以根据每次抛出的异常得知我们下一个表达式的种类是什么。通过异常发现，下一个表达式是一个Quote类型的表达式。它对应的表达式类型是Unary（即一元表达式）。一元表达式中唯一有用的东西就是Operand，于是我们继续解析：
case ExpressionType.Quote: UnaryExpression expUnary = expression as UnaryExpression; if (expUnary != null) { VisitExpression(enumerable, expUnary.Operand, ref sql); } break;
下一个表达式：t=>t.Name==”Frank”，显然是一个lambda表达式。它有用的地方就是它的Body（t.Name==”Frank”）：
case ExpressionType.Lambda: LambdaExpression expLambda = expression as LambdaExpression; if (expLambda != null) { VisitExpression(enumerable, expLambda.Body, ref sql); } break;
最后，我们终于来到了终点。这回是一个Equal类型的表达式，它的左边是t.Name，右边则是“Frank”，都是我们需要的值：
case ExpressionType.Equal: BinaryExpression expBinary = expression as BinaryExpression; if (expBinary != null) { var left = expBinary.Left; var right = expBinary.Right; sql += " " + left.ToString().Split('.')[1] + " = '" + right.ToString().Replace("\"", "") + "'"; } break;
将这些case合起来，一个简陋的LINQ to SQL解释器就做好了。此时我们将写死的SQL去掉，程序应当得到正确的输出：
public TResult Execute<TResult>(Expression expression) { string sql = ""; ExpressionTreeToSql.VisitExpression(new Staff(), expression, ref sql); Console.WriteLine(sql); using (DbHelper db = new DbHelper()) { db.Connect(); dynamic ret = db.GetEmployees(sql); return (TResult) ret; } }
可以看到，where lambda表达式被转化为一个复杂的表达式树。通过手动解析表达式树，我们可以植入自己的逻辑，从而实现LINQ to SQL不能实现的功能。
当然，例子只是最最基本的情况，如果表达式树变得复杂，生成出的sql很可能是错的。
进行简单的扩展
我们来看看下面这个情况，我们增加一个where表达式：
using (DbHelper db = new DbHelper()) { db.Connect(); //db.ExecuteSql("CREATE TABLE Staff ( Id int, Name nvarchar(10), Sex nvarchar(1))"); db.ExecuteSql("DELETE FROM Staff"); db.ExecuteSql("INSERT INTO Staff VALUES (1, 'Frank','M')"); db.ExecuteSql("INSERT INTO Staff VALUES (2, 'Mary','F')"); db.ExecuteSql("INSERT INTO Staff VALUES (1, 'Roy','M')"); Employees = db.GetEmployees("SELECT * FROM Staff"); } var test = Employees.Where(t => t.Sex == "M").Where(t => t.Name == "Frank"); var aa = new FrankQueryable<Staff>(); //Try to translate lambda expression (where) var bb = aa.Where(t => t.Sex == "M") .Where(t => t.Name == "Frank");
此时我们用IQueryable<T>可以得出正确的结果（test只有1笔输出），但使用自己的查询提供器，获得的SQL却是错误的（第一个Sex = M不见了）。我们发现，问题出在我们解析MethodCallExpression那里。
当只有一个where表达式时，表达式树是这样的：
所以我们在解析MethodCallExpression时，直接跳过了argument[0]（实际上它是一个常量表达式），而现在我们似乎不能跳过它了，因为现在的表达式树中，argument[0]是：{value(FrankORM.FrankQueryable`1[FrankORM.Staff]).Where(t => (t.Sex == "M"))}
它包含了有用的信息，所以我们不能跳过它了，我们要解析所有的argument，并使用and进行连接：
case ExpressionType.Call: MethodCallExpression exp = expression as MethodCallExpression; if (exp != null) { if(!sql.Contains(exp.Method.Name)) sql += exp.Method.Name; foreach (var arg in exp.Arguments) { VisitExpression(enumerable, arg, ref sql); } sql += " and "; } break;
此时再运行程序，发生异常。系统提示我们没有关于constant表达式的解析，对于constant表达式，我们什么都不用做。
case ExpressionType.Constant: break;
使用上面的代码，再解析一次，我们就得到了一条看上去比较正确的SQL：
select * from Staff Where Sex = 'M' and Name = 'Frank' Sex = 'M' and Name = 'Frank' and
结尾and多出现了一次，这是因为我们每次解析都在最后加上了and。简单的去掉and，程序就会输出正确的结果。
这次表达式树是这样的：
当然，这个扩展的代码质量已经非常差了，各种凑数。不过，我在这里就仅以此为例，解释下如何扩展并为表达式树解析增加更多的功能，使之可以应付更多类型的表达式。
IQueryable与 IEnumerable的异同？
首先IQueryable<T>是解析一棵树，IEnumerable<T>则是使用委托。前者的手动实现上面已经讲解了（最基本的情况），而后者你完全可以用泛型委托来实现。
IQueryable<T>继承自IEnumerable<T>，所以对于数据遍历来说，它们没有区别。两者都具有延迟执行的效果。但是IQueryable的优势是它有表达式树，所有对于IQueryable<T>的过滤，排序等操作，都会先缓存到表达式树中，只有当真正发生遍历的时候，才会将表达式树由IQueryProvider执行获取数据操作。
而使用IEnumerable<T>，所有对于IEnumerable<T>的过滤，排序等操作，都是在内存中发生的。也就是说数据已经从数据库中获取到了内存中，在内存中进行过滤和排序操作。
当数据源不在本地时，因为IEnumerable<T>查询必须在本地执行，所以执行查询前我们必须把所有的数据加载到本地。而且大部分时候，加载的数据有大量的数据是我们不需要的无效数据，但是我们却不得不传输更多的数据，做更多的无用功。而IQueryable<T>却总能只提供你所需要的数据，大大减少了传输的数据量。
IQueryable总结
- 理解IQueryable的最简单方式就是，把它看作一个查询，在执行的时候，将会生成结果序列。
- 继承IQueryable<T>意味着获得强大的查询能力，这是因为自动获得了Queryable的一大堆扩展方法。
- 当对一个IQueryable<T>的查询进行解析时，首先会访问IQueryable<T>的QueryProvider，然后访问CreateQuery<T>方法，并将输入的查询表达式传入，构建查询。
- 一个查询进行执行，就是开始遍历IQueryable的过程，其会调用Execute方法并传递表达式树。
- 不是所有的表达式树都可以翻译成SQL。例如ToUpper就不行。
- 自己写一个ORM意味着要自己写一个QueryProvider，自定义Execute方法来解析表达式树。所以，你必须要有一个解析表达式树的类，通常大家都叫它ExpressionVisitor。
- 通常使用递归的方式解析表达式树，这是因为表达式树的任意结点（包括叶结点）都是表达式树。
- CreateQuery每次都产生新的表达式对象，不管相同的表达式是否已经存在，这构成了对表达式进行缓存的动机。
ORM和经典的Datatable的优劣比较
好处：
- 提供面向对象和强类型，惯用OO语言的程序员会很快上手。
- 隐藏了数据访问细节，使得干掉整个DAL成为可能。在三层架构中BL要去调用DAL来获得数据，而现在BL可以直接通过lambda表达式等各种方式获得数据，不再需要DAL。
- 将程序员从对SQL语句的拼接（尤其是insert）中解放出来，它既容易错，又很难发现错误。现在插入的对象都是强类型的，就犹如插入一个List一样。
- 以相同的语法操作各种不同的数据库（例如oracle, SQL server等）
- 与经典的DataReader相比，当数据表的某栏的数据类型发生改变时，DataReader就会发生错误（传统的方式是使用DataReader.Read方法一行行读取数据，然后通过GetString，GetInt32等方法获得每一列的数据）。而且错误在运行时才会发生。ORM则会在编译时就会发生错误，而且只需要更改对象属性的类型就不会发生问题。
缺点：
- 有些复杂的SQL或者SQL内置的方法不能通过ORM翻译。
- 自动产生的SQL语句有时的性能较低，这跟产生的机理有关。对于不熟悉ORM的程序员，可能会导致编写出的程序性能低劣。
- 难以替代Store procedure。
ORM的核心是DbContext。它可以看成是一个数据库的副本，我们只需要访问它的方法就可以实现对数据库的CRUD。
扩展阅读
表达式树上手指南：
http://www.cnblogs.com/Ninputer/archive/2009/09/08/expression_tree3.html
对表达式树缓存以进一步提高性能：
http://blog.zhaojie.me/2009/03/expression-cache-1.html
自己实现的LINQ TO 博客园：
http://www.cnblogs.com/jesse2013/p/expressiontree-part1.html
带有GIF的IQueryable讲解：
http://www.cnblogs.com/zhaopei/p/5792623.html
