（转）.NET面试题系列[14] - LINQ to SQL与I

2019-03-08 本文已影响0人 aslbutton

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]是表达式本身，于是我们通过递归继续解析这个表达式：

image

我们可以根据每次抛出的异常得知我们下一个表达式的种类是什么。通过异常发现，下一个表达式是一个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不能实现的功能。

image

当然，例子只是最最基本的情况，如果表达式树变得复杂，生成出的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表达式时，表达式树是这样的：

image

所以我们在解析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，程序就会输出正确的结果。

这次表达式树是这样的：

image

当然，这个扩展的代码质量已经非常差了，各种凑数。不过，我在这里就仅以此为例，解释下如何扩展并为表达式树解析增加更多的功能，使之可以应付更多类型的表达式。

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则会在编译时就会发生错误，而且只需要更改对象属性的类型就不会发生问题。

缺点：