使用LINQ生成Where的SQL语句

发布时间：2020-12-16 08:56:02 所属栏目：asp.Net 来源：网络整理

导读：?实例 1-使用实例-单个语句： int totalCount = 0 ; List int alist = new List int { 5001536 , 2 ,1)">3 }; List User_info userInfoList = UserCenterBus.Select_WebSiteBaseUser_info( 1 ,1)">10 ,User_info._USER_INFO_,User_info._ID_ + " DESC " , ou

?实例1-使用实例-单个语句：

int totalCount = 0;
            List<int> alist = new List<int> { 5001536,2,1)">3 };
            List<User_info> userInfoList = UserCenterBus.Select_WebSiteBase<User_info>(1,1)">10,User_info._USER_INFO_,User_info._ID_ + " DESC",out totalCount,m => alist.Contains(m.ID));
            base.Response.Write(JsonHelper.ConvertJsonToStr(userInfoList));

View Code

/// <summary>
        /// 自定义SQL分页查询_WebSite库_LINQ用于自定义分页SQL和非INT类型变量值传输（防止非INT类型值SQL注入）
        </summary>
        <typeparam name="T">返回类型</typeparam>
        <param name="pageIndex">页码</param>
        <param name="pageSize">页大小<param name="fromTableSql">select * from {0} where {1} order by {2}：填写{0}<param name="orderByTableFieldSql">select * from {0} where {1} order by {2}：填写{2}<param name="totalCount">总条数<param name="whereLinq">关于T的linq语句==>生成可DbParameter[]防SQL注入参数数组<returns></returns>
        public static List<T> Select_WebSiteBase<T>(int pageIndex,1)">int pageSize,1)">string fromTableSql,1)">string orderByTableFieldSql,1)">out int totalCount,Expression<Func<T,1)">bool>> whereLinq)
        {
            DB.MySql.WebSite.Entity.WherePart wherePart = DB.MySql.WebSite.Entity.WhereBuilder.Instance_MySql.ToSql<T>(whereLinq);
            List<DbParameter> dbParameterList = new List<DbParameter>();
            if (wherePart.Parameters != null && wherePart.Parameters.Count > )
            {
                foreach (var paramter in wherePart.Parameters)
                {
                    dbParameterList.Add(new MySqlParameter(paramter.Key,paramter.Value));
                }
            }
            string pageSql = string.Format(@"SELECT * FROM {0} WHERE {1} ORDER BY {2} LIMIT {3},{4};1) * pageSize,pageSize);
            string totalCountSql = SELECT COUNT(*) FROM {0} WHERE {1};",wherePart.Sql);
            List<T> tList = DB.MySql.WebSite.BLL.BLLGeneric.Select<T>(CommandType.Text,pageSql + totalCountSql,1)">out totalCount,dbParameterList.ToArray());
            dbParameterList.Clear();
            dbParameterList = null;
            return tList;
        }

View Code

?实例2-使用实例-多个语句：

WhereBuilder whereBuilder = new WhereBuilder('`');
            Expression<Func<Order_detail,1)">bool>> expression = ;
            expression = orderDetail => orderDetail.OrderDetailState == state;
            WherePart columnValue = whereBuilder.ToSql<Order_detail>(expression);
            expression = orderDetail => orderDetail.OrderID == orderId;
            WherePart where = whereBuilder.ToSql<Order_detail>(expression);

            List<MySqlParameter> paramList = new List<MySqlParameter>(columnValue.Parameters.Count + where.Parameters.Count);
            foreach (KeyValuePair<string,1)">object> pair  columnValue.Parameters)
            {
                paramList.Add(new MySqlParameter(@" + pair.Key,pair.Value));
            }
            in .Parameters)
            {
                paramList.Add(where.Sql,paramList.ToArray()) == ;
            Console.WriteLine(result);

View Code

使用LINQ生成Where的SQL语句：

参考资料：

http://ryanohs.com/2016/04/generating-sql-from-expression-trees-part-2/#more-394

http://stackoverflow.com/a/2616980/291955

主代码：

using System;
 System.Collections;
 System.Collections.Generic;
 System.Linq;
 System.Linq.Expressions;
 System.Reflection;
 System.Runtime.CompilerServices;
 System.Text;

namespace ConsoleApplication2
{
    <summary>
     Generating SQL from expression trees,Part 2
    /// http://ryanohs.com/2016/04/generating-sql-from-expression-trees-part-2/#more-394
    </summary>
    class WhereBuilder
    {
        private readonly char _columnBeginChar = [;
        char _columnEndChar = ]private System.Collections.ObjectModel.ReadOnlyCollection<ParameterExpression> expressParameterNameCollection;
        //mysql使用"`"包裹数据
        public WhereBuilder(char columnChar = )
        {
            this._columnBeginChar = this._columnEndChar = columnChar;
        }

        public WhereBuilder(char columnBeginChar = '[',char columnEndChar = ']')
        {
            this._columnBeginChar = columnBeginChar;
            this._columnEndChar = columnEndChar;
        }

         LINQ转SQL
        <typeparam name="T"></typeparam>
        <param name="expression"></param>
        public WherePart ToSql<T>(Expression<Func<T,1)"> expression)
        {
            var i = 1if (expression.Parameters.Count > this.expressParameterNameCollection = expression.Parameters;
            }
            return Recurse(ref i,expression.Body,isUnary: true);
        }

        <param name="i">种子值public WherePart ToSql<T>(ref int i,1)"><param name="isUnary"></param>
        <param name="prefix"></param>
        <param name="postfix"></param>
        private WherePart Recurse(bool isUnary = false,1)">string prefix = null,1)">string postfix = 运算符表达式
            if (expression is UnaryExpression)
            {
                var unary = (UnaryExpression)expression;
                
                bool isContainsParameterExpress = false;
                this.IsContainsParameterExpress(unary.Operand,1)">ref isContainsParameterExpress);
                
                if (isContainsParameterExpress == )
                {
                    示例：m.birthday=DateTime.Now
                    if (unary.NodeType == ExpressionType.Convert)
                    {
                        var value = GetValue(expression);
                        if (value is string)
                        {
                            value = prefix + (string)value + postfix;
                        }
                        return WherePart.IsParameter(i++else
                    {
                        示例：m.Birthday>'2018-10-31'
                        return WherePart.Concat(NodeTypeToString(unary.NodeType),Recurse());
                    }
                }
                
                {
                    示例：Convert(m.Type,Int64);舍弃含有变量属性的数据转换，保留表达式部分生成sql。（原本计划这里使用cast等sql的类型转换，后来尝试了不进行转换也是可以的，所以暂时放弃转换）
                     i,unary.Operand);
                }
            }
             BinaryExpression)
            {
                var body = (BinaryExpression)expression;
                return WherePart.Concat(Recurse(常量值表达式
            示例右侧表达式：m.ID=123;
             ConstantExpression)
            {
                var constant = (ConstantExpression)expression;
                 constant.Value;
                int WherePart.IsSql(value.ToString());
                }
                )
                {
                    value = prefix + ( postfix;
                }
                bool && isUnary)
                {
                    return WherePart.Concat(WherePart.IsParameter(i++,value),1)">=1));
                }
                成员表达式
             MemberExpression)
            {
                var member = (MemberExpression)expression;
                var memberExpress = member.Expression;
                this.IsContainsParameterExpress(member,1)"> isContainsParameterExpress);
                if (member.Member is PropertyInfo && isContainsParameterExpress)
                {
                    var property = (PropertyInfo)member.Member;
                    var colName = _tableDef.GetColumnNameFor(property.Name);
                    var colName = property.Name;
                    if (isUnary && member.Type == typeof(bool))
                    {
                        ));
                    }
                    return WherePart.IsSql({0}{1}{2}this._columnBeginChar,colName,1)">this._columnEndChar));
                }
                is FieldInfo || !isContainsParameterExpress)
                {
                     GetValue(member);
                    )
                    {
                        value = prefix + ( postfix;
                    }
                    throw new Exception($Expression does not refer to a property or field: {expression});
            }
            方法表达式
             MethodCallExpression)
            {
                var methodCall = (MethodCallExpression)expression;
                属性表达式中的参数表达式是否是表达式参数集合中的实例（或者表达式中包含的其他表达式中的参数表达式）
                this.IsContainsParameterExpress(methodCall,1)">if (isContainsParameterExpress)
                {
                     LIKE queries:
                    if (methodCall.Method == string).GetMethod(Containsnew[] { ) }))
                    {
                        LIKE0],prefix: %StartsWithEndsWith IN queries:
                    if (methodCall.Method.Name == )
                    {
                        Expression collection;
                        Expression property;
                        if (methodCall.Method.IsDefined(typeof(ExtensionAttribute)) && methodCall.Arguments.Count == 2)
                        {
                            collection = methodCall.Arguments[];
                            property = methodCall.Arguments[];
                        }
                        else if (!methodCall.Method.IsDefined()
                        {
                            collection = methodCall.Object;
                            property = methodCall.Arguments[
                        {
                            new Exception(Unsupported method call:  methodCall.Method.Name);
                        }
                        var values = (IEnumerable)GetValue(collection);
                        IN GetValue(expression);
                     methodCall.Method.Name);
            }
            New表达式
             NewExpression)
            {
                 (NewExpression)expression;
                 GetValue(member);
                Unsupported expression:  expression.GetType().Name);
        }
         判断表达式内部是否含有变量M
        <param name="expression">表达式void IsContainsParameterExpress(Expression expression,1)"> result)
        {
            if (this.expressParameterNameCollection != null && this.expressParameterNameCollection.Count > 0 && expression !=  MemberExpression)
                {
                    .expressParameterNameCollection.Contains(((MemberExpression)expression).Expression))
                    {
                        result = ;
                    }
                }
                 MethodCallExpression)
                {
                    MethodCallExpression methodCallExpression = (MethodCallExpression)expression;

                    if (methodCallExpression.Object != )
                    {
                        if (methodCallExpression.Object  MethodCallExpression)
                        {
                            判断示例1：m.ID.ToString().Contains("123")
                            this.IsContainsParameterExpress(methodCallExpression.Object,1)"> result);
                        }
                         MemberExpression)
                        {
                            判断示例2：m.ID.Contains(123)
                            MemberExpression MemberExpression = (MemberExpression)methodCallExpression.Object;
                            if (MemberExpression.Expression != .expressParameterNameCollection.Contains(MemberExpression.Expression))
                            {
                                result = ;
                            }
                        }
                    }
                    判断示例3： int[] ids=new ids[]{1,2,3};  ids.Contains(m.ID)
                    if (result == false && methodCallExpression.Arguments != null && methodCallExpression.Arguments.Count > foreach (Expression express  methodCallExpression.Arguments)
                        {
                            if (express is MemberExpression || express  MethodCallExpression)
                            {
                                this.IsContainsParameterExpress(express,1)"> result);
                            }
                            .expressParameterNameCollection.Contains(express))
                            {
                                result = ;
                                break;
                            }
                        }
                    }
                }
            }
        }

        static object GetValue(Expression member)
        {
             source: http://stackoverflow.com/a/2616980/291955
            var objectMember = Expression.Convert(member,1)">));
            var getterLambda = Expression.Lambda<Func<object>>(objectMember);
            var getter = getterLambda.Compile();
             getter();
        }

         NodeTypeToString(ExpressionType nodeType)
        {
            switch (nodeType)
            {
                case ExpressionType.Add:
                    return + ExpressionType.And:
                    & ExpressionType.AndAlso:
                    AND ExpressionType.Divide:
                    / ExpressionType.Equal:
                     ExpressionType.ExclusiveOr:
                    ^ ExpressionType.GreaterThan:
                    > ExpressionType.GreaterThanOrEqual:
                    >= ExpressionType.LessThan:
                    < ExpressionType.LessThanOrEqual:
                    <= ExpressionType.Modulo:
                     ExpressionType.Multiply:
                    * ExpressionType.Negate:
                    - ExpressionType.Not:
                    NOT ExpressionType.NotEqual:
                    <> ExpressionType.Or:
                    | ExpressionType.OrElse:
                    OR ExpressionType.Subtract:
                    ;
            }
            Unsupported node type: {nodeType});
        }
    }

     WherePart
    {
         含有参数变量的SQL语句
        </summary>
        string Sql { get; set; }
         SQL语句中的参数变量
        public Dictionary<object> Parameters { set; } = new Dictionary<object>();

        static WherePart IsSql( sql)
        {
            return  WherePart()
            {
                Parameters = (),Sql = sql
            };
        }

        static WherePart IsParameter(int count,1)"> value)
        {
             WherePart()
            {
                Parameters = { { count.ToString(),value } },Sql = $@{count}
            };
        }

        static WherePart IsCollection( countStart,IEnumerable values)
        {
            var parameters = ();
            var sql = new StringBuilder((var value  values)
            {
                parameters.Add((countStart).ToString(),value);
                sql.Append($@{countStart},);
                countStart++if (sql.Length == )
            {
                sql.Append(null,1)">);
            }
            sql[sql.Length - 1] = ) parameters,1)"> sql.ToString()
            };
        }

        static WherePart Concat( @operator,WherePart operand)
        {
             operand.Parameters,1)">({@operator} {operand.Sql})static WherePart Concat(WherePart left,WherePart right)
        {
             WherePart()
            {
                Parameters = left.Parameters.Union(right.Parameters).ToDictionary(kvp => kvp.Key,kvp => kvp.Value),1)">({left.Sql} {@operator} {right.Sql})
            };
        }
    }
}

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!