使用LINQ生成Where的SQL语句

程序员文章站 2022-11-15 22:17:43

使用实例： int totalCount = 0; List alist = new List { 5001536, 2, 3 }; List userInfoList = UserCenterBus.Select_WebSiteBase

使用实例：

int totalCount = 0;
            List<int> alist = new List<int> { 5001536, 2, 3 };
            List<User_info> userInfoList = UserCenterBus.Select_WebSiteBase<User_info>(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>
        /// <param name="fromTableSql">select * from {0} where {1} order by {2}：填写{0}</param>
        /// <param name="orderByTableFieldSql">select * from {0} where {1} order by {2}：填写{2}</param>
        /// <param name="totalCount">总条数</param>
        /// <param name="whereLinq">关于T的linq语句==>生成可DbParameter[]防SQL注入参数数组</param>
        /// <returns></returns>
        public static List<T> Select_WebSiteBase<T>(int pageIndex, int pageSize, string fromTableSql, string orderByTableFieldSql, out int totalCount, Expression<Func<T, bool>> whereLinq)
        {
            DB.MySql.WebSite.Entity.WherePart wherePart = DB.MySql.WebSite.Entity.WhereBuilder.Instance_MySql.ToSql<T>(whereLinq);
            List<DbParameter> dbParameterList = new List<DbParameter>(0);
            if (wherePart.Parameters != null && wherePart.Parameters.Count > 0)
            {
                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};", fromTableSql, wherePart.Sql, orderByTableFieldSql, (pageIndex - 1) * pageSize, pageSize);
            string totalCountSql = string.Format(@"SELECT COUNT(*) FROM {0} WHERE {1};", fromTableSql, wherePart.Sql);
            List<T> tList = DB.MySql.WebSite.BLL.BLLGeneric.Select<T>(CommandType.Text, pageSql + totalCountSql, out totalCount, dbParameterList.ToArray());
            dbParameterList.Clear();
            dbParameterList = null;
            return tList;
        }

View Code

使用LINQ生成Where的SQL语句：

参考资料：

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

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

主代码：

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

namespace a
{
    /// <summary>
    /// Generating SQL from expression trees, Part 2
    /// http://ryanohs.com/2016/04/generating-sql-from-expression-trees-part-2/#more-394
    /// </summary>
    public class WhereBuilder
    {
        private readonly char _columnBeginChar = '[';
        private readonly char _columnEndChar = ']';
        private System.Collections.ObjectModel.ReadOnlyCollection<ParameterExpression> expressParameterNameColl;
        public static WhereBuilder Instance_MySql = new WhereBuilder('`');

        public WhereBuilder(char columnChar = '`')
        {
            this._columnBeginChar = this._columnEndChar = columnChar;
        }

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

        public WherePart ToSql<T>(Expression<Func<T, bool>> expression)
        {
            var i = 1;
            if (expression.Parameters.Count > 0)
            {
                this.expressParameterNameColl = expression.Parameters;
            }
            return Recurse(ref i, expression.Body, isUnary: true);
        }

        private WherePart Recurse(ref int i, Expression expression, bool isUnary = false, string prefix = null, string postfix = null)
        {
            if (expression is UnaryExpression)
            {
                var unary = (UnaryExpression)expression;
                return WherePart.Concat(NodeTypeToString(unary.NodeType), Recurse(ref i, unary.Operand, true));
            }
            if (expression is BinaryExpression)
            {
                var body = (BinaryExpression)expression;
                return WherePart.Concat(Recurse(ref i, body.Left), NodeTypeToString(body.NodeType), Recurse(ref i, body.Right));
            }
            if (expression is ConstantExpression)
            {
                var constant = (ConstantExpression)expression;
                var value = constant.Value;
                if (value is int)
                {
                    return WherePart.IsSql(value.ToString());
                }
                if (value is string)
                {
                    value = prefix + (string)value + postfix;
                }
                if (value is bool && isUnary)
                {
                    return WherePart.Concat(WherePart.IsParameter(i++, value), "=", WherePart.IsSql("1"));
                }
                return WherePart.IsParameter(i++, value);
            }
            if (expression is MemberExpression)
            {
                var member = (MemberExpression)expression;
                var memberExpress = member.Expression;
                if (member.Member is PropertyInfo && this.IsContainsParameterExpress(member))
                {
                    var property = (PropertyInfo)member.Member;
                    //var colName = _tableDef.GetColumnNameFor(property.Name);
                    var colName = property.Name;
                    if (isUnary && member.Type == typeof(bool))
                    {
                        return WherePart.Concat(Recurse(ref i, expression), "=", WherePart.IsParameter(i++, true));
                    }
                    return WherePart.IsSql(string.Format("{0}{1}{2}", this._columnBeginChar, colName, this._columnEndChar));
                }
                if (member.Member is FieldInfo || !this.IsContainsParameterExpress(member))
                {
                    var value = GetValue(member);
                    if (value is string)
                    {
                        value = prefix + (string)value + postfix;
                    }
                    return WherePart.IsParameter(i++, value);
                }
                throw new Exception($"Expression does not refer to a property or field: {expression}");
            }
            if (expression is MethodCallExpression)
            {
                var methodCall = (MethodCallExpression)expression;
                //方法表达式需要验证调用对象是否是属性表达式&&属性表达式中的参数表达式是否是表达式参数集合中的实例（或者表达式中包含的其他表达式中的参数表达式）
                if (methodCall.Object is MemberExpression && this.IsContainsParameterExpress(methodCall))
                {
                    // LIKE queries:
                    if (methodCall.Method == typeof(string).GetMethod("Contains", new[] { typeof(string) }))
                    {
                        return WherePart.Concat(Recurse(ref i, methodCall.Object), "LIKE", Recurse(ref i, methodCall.Arguments[0], prefix: "%", postfix: "%"));
                    }
                    if (methodCall.Method == typeof(string).GetMethod("StartsWith", new[] { typeof(string) }))
                    {
                        return WherePart.Concat(Recurse(ref i, methodCall.Object), "LIKE", Recurse(ref i, methodCall.Arguments[0], postfix: "%"));
                    }
                    if (methodCall.Method == typeof(string).GetMethod("EndsWith", new[] { typeof(string) }))
                    {
                        return WherePart.Concat(Recurse(ref i, methodCall.Object), "LIKE", Recurse(ref i, methodCall.Arguments[0], prefix: "%"));
                    }
                    // IN queries:
                    if (methodCall.Method.Name == "Contains")
                    {
                        Expression collection;
                        Expression property;
                        if (methodCall.Method.IsDefined(typeof(ExtensionAttribute)) && methodCall.Arguments.Count == 2)
                        {
                            collection = methodCall.Arguments[0];
                            property = methodCall.Arguments[1];
                        }
                        else if (!methodCall.Method.IsDefined(typeof(ExtensionAttribute)) && methodCall.Arguments.Count == 1)
                        {
                            collection = methodCall.Object;
                            property = methodCall.Arguments[0];
                        }
                        else
                        {
                            throw new Exception("Unsupported method call: " + methodCall.Method.Name);
                        }
                        var values = (IEnumerable)GetValue(collection);
                        return WherePart.Concat(Recurse(ref i, property), "IN", WherePart.IsCollection(ref i, values));
                    }
                }
                else
                {
                    var value = GetValue(expression);
                    if (value is string)
                    {
                        value = prefix + (string)value + postfix;
                    }
                    return WherePart.IsParameter(i++, value);
                }

                throw new Exception("Unsupported method call: " + methodCall.Method.Name);
            }
            if (expression is NewExpression)
            {
                var member = (NewExpression)expression;
                var value = GetValue(member);
                if (value is string)
                {
                    value = prefix + (string)value + postfix;
                }
                return WherePart.IsParameter(i++, value);
            }
            throw new Exception("Unsupported expression: " + expression.GetType().Name);
        }

        private bool IsContainsParameterExpress(Expression expression)
        {
            bool result = false;
            if (this.expressParameterNameColl != null && this.expressParameterNameColl.Count > 0 && expression != null)
            {
                if (expression is MemberExpression)
                {
                    if (this.expressParameterNameColl.Contains(((MemberExpression)expression).Expression))
                    {
                        result = true;
                    }
                }
                else if (expression is MethodCallExpression)
                {
                    MethodCallExpression methodCallExpression = (MethodCallExpression)expression;
                    if (methodCallExpression.Object != null && methodCallExpression.Object is MemberExpression)
                    {
                        MemberExpression MemberExpression = (MemberExpression)methodCallExpression.Object;
                        if (MemberExpression.Expression != null && this.expressParameterNameColl.Contains(MemberExpression.Expression))
                        {
                            result = true;
                        }
                    }
                    if (methodCallExpression.Arguments != null && methodCallExpression.Arguments.Count > 0 && methodCallExpression.Arguments[0] is MemberExpression)
                    {
                        MemberExpression memberExpression = (MemberExpression)methodCallExpression.Arguments[0];
                        if (memberExpression.Expression != null && this.expressParameterNameColl.Contains(memberExpression.Expression))
                        {
                            result = true;
                        }
                    }
                }
            }
            return result;
        }

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

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

    public class WherePart
    {
        public string Sql { get; set; }
        public Dictionary<string, object> Parameters { get; set; } = new Dictionary<string, object>();

        public static WherePart IsSql(string sql)
        {
            return new WherePart()
            {
                Parameters = new Dictionary<string, object>(),
                Sql = sql
            };
        }

        public static WherePart IsParameter(int count, object value)
        {
            return new WherePart()
            {
                Parameters = { { count.ToString(), value } },
                Sql = $"@{count}"
            };
        }

        public static WherePart IsCollection(ref int countStart, IEnumerable values)
        {
            var parameters = new Dictionary<string, object>();
            var sql = new StringBuilder("(");
            foreach (var value in values)
            {
                parameters.Add((countStart).ToString(), value);
                sql.Append($"@{countStart},");
                countStart++;
            }
            if (sql.Length == 1)
            {
                sql.Append("null,");
            }
            sql[sql.Length - 1] = ')';
            return new WherePart()
            {
                Parameters = parameters,
                Sql = sql.ToString()
            };
        }

        public static WherePart Concat(string @operator, WherePart operand)
        {
            return new WherePart()
            {
                Parameters = operand.Parameters,
                Sql = $"({@operator} {operand.Sql})"
            };
        }

        public static WherePart Concat(WherePart left, string @operator, WherePart right)
        {
            return new WherePart()
            {
                Parameters = left.Parameters.Union(right.Parameters).ToDictionary(kvp => kvp.Key, kvp => kvp.Value),
                Sql = $"({left.Sql} {@operator} {right.Sql})"
            };
        }
    }
}

上一篇：办法虽然不错，但黑框似乎不太对吧

下一篇： Python django框架笔记（二）：创建应用和django 管理

使用LINQ生成Where的SQL语句

SQL2005查询表结构的SQL语句使用分享

浅谈sql语句中GROUP BY 和 HAVING的使用方法

SQL语句中OR和AND的混合使用的小技巧

两种查看EFCore生成Sql语句的方法

php SQL之where语句生成器

SQLSERVER 2005中使用sql语句对xml文件和其数据的进行操作(很全面)

iOS开发中使用SQL语句操作数据库的基本用法指南

PHP+Mysql实现多关键字与多字段生成SQL语句的函数

详解WordPress开发中过滤属性以及Sql语句的函数使用

djang常用查询SQL语句的使用代码