实现自己的Linq to Sql

之前写过一篇《统一的仓储接口》，为了方便使用不同的仓储。在我们的项目中使用的是EF4.0，但是这个版本的EF有一些性能问题没有解决，又不想升级到EF6，具体EF6有没有解决暂时不清楚。我们的项目之前运行的都不错，突然一天数据库服务器CPU 100%，IIS服务器CPU又正常，过几个小时之后又恢复正常，每个星期一早上都这样，可以肯定就是用户同时操作并发过多造成，查找之后，是一个表的数据被锁住。报错was deadlocked on lock，解决办法就是查询sql上加上 with nolock，但是EF不支持，但有不想放弃linq to sql的优势，无奈只能自己实现，方案是之前已有功能并发不多的地方保持不变，依然使用EF，在并发多的地方使用自己实现的linq to sql。

目前并没有完整实现linq to sql，只是实现了单表的情况，对于有关联引用由于时间限制并未实现。

实现仓储，需要实现两个对象，[IObjectContext、IOrderedQueryable]

上篇说到的上下文对象IObjectContext

public interface IObjectContext:IDisposable
    {
        string Name { get; set; }
        IDbConnection CreateConnection();
        bool SaveChanges();
    }

考虑到项目中有些特殊情况需要使用sql，所以增加了CreateConnection方法返回一个IDbConnection，当然在逼不得已的情况下使用。

DbContext的实现比较简单，重点管理一下连接池，配置，大概如下，具体的实现由于篇幅省略，源码会在篇尾附上。

public class DbContext : IDisposable, IObjectContext
    {
        public EntityStateManager Manager { get; private set; }
        public string DbType { get; private set; }
        public string ConnectionString { get; set; }
        public int PoolSize { get; set; }
        public bool AllowUpdateWithNoExp { get; set; }
        public bool AllowDeleteWithNoExp { get; set; }
 
        List<System.Data.IDbConnection> connPool = new List<System.Data.IDbConnection>();
 
        public DbContext();
 
        public DbContext(string connectionString, string dbType);
 
        public DbContext(string connectionString, string dbType, bool allowUpdateWithNoExp, bool allowDeleteWithNoExp);
 
        void InitContext(string connectionString, string dbType, int poolSize, bool allowUpdateWithNoExp, bool allowDeleteWithNoExp);
 
        public EntitySet<T> GetEntitySet<T>(string tableName = null, bool noLock = false, bool noTracking = false);
 
        public int SubmitChange();
 
        void IDisposable.Dispose();
 
        string IObjectContext.Name { get; set; }
 
        bool IObjectContext.SaveChanges();
 
        public System.Data.IDbConnection CreateConnection();
 
        System.Data.IDbConnection IObjectContext.CreateConnection();

另一个对象则是仓储EntitySet<T>，大概代码如下

public class EntitySet<T> : IOrderedQueryable<T>
    {
        public DbContext Context { get; private set; }
        string TableName { get; set; }
        bool NoLock { get; set; }
        bool NoTracking { get; set; }
 
        DbQueryProvider MyProvider { get; set; }
        Expression MyExpression { get; set; }
 
        public EntitySet(string tableName, bool noLock, bool noTracking, DbContext context);
 
        public EntitySet(string tableName, bool noLock, bool noTracking, Expression expression, DbContext context);
 
        void InitEntitySet(string tableName, bool noLock, bool noTracking, Expression expression, DbContext context);
 
        public Expression Expression
        {
            get { return this.MyExpression; }
        }
 
        public Type ElementType
        {
            get { return typeof(T); }
        }
 
        public IQueryProvider Provider
        {
            get { return this.MyProvider; }
        }
 
        public IEnumerator<T> GetEnumerator();
 
        IEnumerator IEnumerable.GetEnumerator();
 
        void MyProvider_OnExecuted(object sender, EventArgs e);
 
        void TrackEntity(object result);
 
        void notifyT_PropertyChanged(object sender, PropertyChangedEventArgs e);
 
        public void Add(T item, bool INSERT_IDENTITY = false);
 
        public void Update(Expression<Func<T, bool>> exp, object obj);
 
        public void Remove(T item);
 
        public void Remove(Expression<Func<T, bool>> exp);

在仓储里面我增加了我需要的东西，比如Add可以插入标识，Update可以根据表达式更新对象，而不需要把所需要的对象先取出来，修改再保存，数据量大的时候EF性能有问题，Remove也同样如此，为了防止误操作，所以在之前DbContext中增加了配置，是否允许无条件删除、更新数据。另外一个重点也就是增加nolock支持，当然这个在生成sql的时候加上就行，非常简单。

说到这里其实只不过是个大概，这里面的操作无非就是四种CRUD。我设计了四个Command来解决InsertCommand、UpdateCommand、SelectCommand、DeleteCommand，他们都继承EntityCommand只要实现一个方法public abstract int Execute(IDbConnection conn,IDbTransaction tran);

到这里其实InsertCommand、UpdateCommand、DeleteCommand实现都非常简单，因为有了实现SelectCommand的基础代码，解析Expression就简单的多了，可以说解析Expression才是整个的关键。

解析的代码没有那么多复杂的东西，我个人的原则就是尽量简单，不为了追求设计而增加多余的东西，虽然我对设计模式也很痴迷。

上图就是解析的全部代码，这其中代码其实并不重要，重要的是解决的思路，这里面有两个重要的对象QueryExpressionClosure(查询表达式闭包)、QueryCommnClosure(查询通用闭包)。

我们知道IQueryable其实就是expression tree，我记得以前很早的时候有人实现解析表达式的时候，是一边解析一边生产sql，这样的做法非常不科学，会造成很多不必要的sql闭包，

一个简单的查询：比如q.Where(c=>c.Age>20).OrderBy(c=>c.Id);就会生成最少三个闭包大概sql是这样 select xxx from (select * from (select xxx from tablex) as t1 where t1.Age>20) as t2 order by t2.Id。但是正常来说应该是select xxx from tablex where Age>20 order by Id。

所以正确的应该是先将表达式树解析成表达式闭包，在将表达式闭包解析成sql。

如何将表达式解析成表达式闭包？

稍微分析一下就可以看出，当遇到take、SKIP、sum、max、min、average、any、contains、distinct、first、firstordefault、longcount、count的时候就是另外一个sql闭包了，

大概代码如下：

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

protected override Expression VisitMethodCall(MethodCallExpression m)
        {
            var methodName = m.Method.Name.ToLower();
            if (this.CurrQueryExpressionClosure == null && this.QueryExpressionClosures.Count == 0 && Utility.ToListMethods.Contains(methodName))
            {
                this.CurrQueryExpressionClosure = new QueryExpressionClosure();
                this.CurrQueryExpressionClosure.MethodName = "tolist";
                this.QueryExpressionClosures.Add(this.CurrQueryExpressionClosure);
            }
 
            switch (methodName)
            {
                case "orderbydescending":
                case "orderby":
                case "thenby":
                case "thenbydescending":
                    {
                        if (!Utility.IgnoreOrderByMethods.Contains(this.CurrQueryExpressionClosure.MethodName))
                        {
                            this.CurrQueryExpressionClosure.OrderByExpressions.Add(m);
                        }
                        break;
                    }
                case "groupby":
                    {
                        this.CurrQueryExpressionClosure.GroupByExpressions.Add(m.Arguments[1]);
                        break;
                    }
                case "where":
                    {
                        var l = ((m.Arguments[1] as UnaryExpression).Operand as LambdaExpression);
                        this.CurrQueryExpressionClosure.WhereExpressions.Add(l);
                        break;
                    }
                case "take":
                    {
                        if (this.CurrQueryExpressionClosure.MethodName != "skip")
                        {
                            this.CurrQueryExpressionClosure = new QueryExpressionClosure();
                            this.QueryExpressionClosures.Add(this.CurrQueryExpressionClosure);
                            this.CurrQueryExpressionClosure.MethodName = methodName;
                        }
                        this.CurrQueryExpressionClosure.Take = System.Convert.ToInt32((m.Arguments[1] as ConstantExpression).Value);
                        break;
                    }
                case "skip":
                    {
                        if (this.CurrQueryExpressionClosure.MethodName != "take")
                        {
                            this.CurrQueryExpressionClosure = new QueryExpressionClosure();
                            this.QueryExpressionClosures.Add(this.CurrQueryExpressionClosure);
                            this.CurrQueryExpressionClosure.MethodName = methodName;
                        }
                        this.CurrQueryExpressionClosure.Skip += System.Convert.ToInt32((m.Arguments[1] as ConstantExpression).Value);
                        break;
                    }
                case "sum":
                case "max":
                case "min":
                case "average":
                    {
                        this.CurrQueryExpressionClosure = new QueryExpressionClosure();
                        this.CurrQueryExpressionClosure.MethodName = methodName;
                        this.QueryExpressionClosures.Add(this.CurrQueryExpressionClosure);
                        if (m.Arguments.Count > 1)
                        {
                            this.CurrQueryExpressionClosure.EvalNumericExpression = m.Arguments[1];
                        }
                        break;
                    }
                case "any":
                    {
                        this.CurrQueryExpressionClosure = new QueryExpressionClosure();
                        this.CurrQueryExpressionClosure.MethodName = methodName;
                        this.QueryExpressionClosures.Add(this.CurrQueryExpressionClosure);
                        if (m.Arguments.Count > 1)
                        {
                            var l = ((m.Arguments[1] as UnaryExpression).Operand as LambdaExpression);
                            this.CurrQueryExpressionClosure.WhereExpressions.Add(l);
                        }
                        break;
                    }
                case "contains":
                    {
                        this.CurrQueryExpressionClosure = new QueryExpressionClosure();
                        this.CurrQueryExpressionClosure.MethodName = methodName;
                        this.QueryExpressionClosures.Add(this.CurrQueryExpressionClosure);
                        if (m.Arguments.Count > 1)
                        {
                            this.CurrQueryExpressionClosure.WhereExpressions.Add(m.Arguments[1]);
                        }
                        break;
                    }
                case "distinct":
                    {
                        this.CurrQueryExpressionClosure = new QueryExpressionClosure();
                        this.CurrQueryExpressionClosure.MethodName = methodName;
                        this.QueryExpressionClosures.Add(this.CurrQueryExpressionClosure);
                        break;
                    }
                case "select":
                    {
                        var lambdaExp = ((m.Arguments[1] as UnaryExpression).Operand as LambdaExpression);
                        this.CurrQueryExpressionClosure.QuerySelectExpressions.Add(lambdaExp);
                        break;
                    }
                case "first":
                case "firstordefault":
                    {
                        this.CurrQueryExpressionClosure = new QueryExpressionClosure();
                        this.CurrQueryExpressionClosure.MethodName = methodName;
                        this.CurrQueryExpressionClosure.Take = 1;
                        this.QueryExpressionClosures.Add(this.CurrQueryExpressionClosure);
                        if (m.Arguments.Count > 1)
                        {
                            this.CurrQueryExpressionClosure.WhereExpressions.Add(m.Arguments[1]);
                        }
                        break;
                    }
                case "longcount":
                case "count":
                    {
                        this.CurrQueryExpressionClosure = new QueryExpressionClosure();
                        this.CurrQueryExpressionClosure.MethodName = methodName;
                        this.QueryExpressionClosures.Add(this.CurrQueryExpressionClosure);
                        if (m.Arguments.Count > 1)
                        {
                            this.CurrQueryExpressionClosure.WhereExpressions.Add(m.Arguments[1]);
                        }
                        break;
                    }
            }
            return base.VisitMethodCall(m);
        }

有了表达式闭包之后，这个时候理解起来就清晰多了，就可以通过一个ParserContext梳理一遍表达式闭包，生成一个通用闭包，并且得到需要的信息。

通用闭包大概：

public class QueryCommnClosure
    {
        public int Take { get; set; }
        public int Skip { get; set; }
        public bool NoLock { get; set; }
        public string MethodName { get; set; }
        public string TableName { get; set; }
        public List<string> SelectColumns { get; set; }
        //public List<SelectTypeConstructor> SelectTypes { get; set; }
        public Dictionary<string, string> OrderBys { get; set; }
        public List<string> GroupBys { get; set; }
        public List<string> WhereSQLs { get; set; }
        public string EvalNumericSQL { get; set; }
        public string TableAlias { get; set; }
 
        public QueryCommnClosure()
        {
            this.SelectColumns = new List<string>();
            //this.SelectTypes = new List<SelectTypeConstructor>();
            this.OrderBys = new Dictionary<string, string>();
            this.GroupBys = new List<string>();
            this.WhereSQLs = new List<string>();
        }
 
        public void Generate(ParserContext context)
        {
            ...篇幅限制省略
        }
    }