使用LINQ生成Where的SQL语句
2024-10-11 17:16:59
实例1-使用实例-单个语句：
int totalCount = ;
List<int> alist = new List<int> { , , };
List<User_info> userInfoList = UserCenterBus.Select_WebSiteBase<User_info>(, , User_info._USER_INFO_, User_info._ID_ + " DESC", out totalCount, m => alist.Contains(m.ID));
base.Response.Write(JsonHelper.ConvertJsonToStr(userInfoList));
/// <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>();
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};", fromTableSql, wherePart.Sql, orderByTableFieldSql, (pageIndex - ) * 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;
}
实例2-使用实例-多个语句：
WhereBuilder whereBuilder = new WhereBuilder('`');
Expression<Func<Order_detail, bool>> expression = null;
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, object> pair in columnValue.Parameters)
{
paramList.Add(new MySqlParameter("@" + pair.Key, pair.Value));
}
foreach (KeyValuePair<string, object> pair in where.Parameters)
{
paramList.Add(new MySqlParameter("@" + pair.Key, pair.Value));
}
result = UpdateWhere(columnValue.Sql, where.Sql, paramList.ToArray()) == ;
Console.WriteLine(result);
使用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;
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 ConsoleApplication2
{
/// <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> expressParameterNameCollection; public WhereBuilder(char columnChar = '`')
{
this._columnBeginChar = this._columnEndChar = columnChar;
} //public WhereBuilder(char columnBeginChar = '[', char columnEndChar = ']')
//{
// this._columnBeginChar = columnBeginChar;
// this._columnEndChar = columnEndChar;
//} /// <summary>
/// LINQ转SQL
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="expression"></param>
/// <returns></returns>
public WherePart ToSql<T>(Expression<Func<T, bool>> expression)
{
var i = ;
if (expression.Parameters.Count > )
{
this.expressParameterNameCollection = expression.Parameters;
}
return Recurse(ref i, expression.Body, isUnary: true);
} /// <summary>
/// LINQ转SQL
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="i">种子值</param>
/// <param name="expression"></param>
/// <returns></returns>
public WherePart ToSql<T>(ref int i, Expression<Func<T, bool>> expression)
{
if (expression.Parameters.Count > )
{
this.expressParameterNameCollection = expression.Parameters;
}
return Recurse(ref i, expression.Body, isUnary: true);
} /// <summary>
/// LINQ转SQL
/// </summary>
/// <param name="i">种子值</param>
/// <param name="expression"></param>
/// <param name="isUnary"></param>
/// <param name="prefix"></param>
/// <param name="postfix"></param>
/// <returns></returns>
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;
//示例：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++, value);
}
else
{
//示例：m.Birthday>'2018-10-31'
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));
}
//常量值表达式
//示例右侧表达式：m.ID=123;
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(""));
}
return WherePart.IsParameter(i++, value);
}
//成员表达式
if (expression is MemberExpression)
{
var member = (MemberExpression)expression;
var memberExpress = member.Expression;
bool isContainsParameterExpress = false;
this.IsContainsParameterExpress(member, ref 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.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 || !isContainsParameterExpress)
{
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;
//属性表达式中的参数表达式是否是表达式参数集合中的实例（或者表达式中包含的其他表达式中的参数表达式）
bool isContainsParameterExpress = false;
this.IsContainsParameterExpress(methodCall, ref isContainsParameterExpress);
if (isContainsParameterExpress)
{
// 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[], 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[], 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[], prefix: "%"));
}
// IN queries:
if (methodCall.Method.Name == "Contains")
{
Expression collection;
Expression property;
if (methodCall.Method.IsDefined(typeof(ExtensionAttribute)) && methodCall.Arguments.Count == )
{
collection = methodCall.Arguments[];
property = methodCall.Arguments[];
}
else if (!methodCall.Method.IsDefined(typeof(ExtensionAttribute)) && methodCall.Arguments.Count == )
{
collection = methodCall.Object;
property = methodCall.Arguments[];
}
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);
}
//New表达式
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);
}
/// <summary>
/// 判断表达式内部是否含有变量M
/// </summary>
/// <param name="expression">表达式</param>
/// <returns></returns>
private void IsContainsParameterExpress(Expression expression, ref bool result)
{
if (this.expressParameterNameCollection != null && this.expressParameterNameCollection.Count > && expression != null)
{
if (expression is MemberExpression)
{
if (this.expressParameterNameCollection.Contains(((MemberExpression)expression).Expression))
{
result = true;
}
}
else if (expression is MethodCallExpression)
{
MethodCallExpression methodCallExpression = (MethodCallExpression)expression; if (methodCallExpression.Object != null)
{
if (methodCallExpression.Object is MethodCallExpression)
{
//判断示例1：m.ID.ToString().Contains("123")
this.IsContainsParameterExpress(methodCallExpression.Object, ref result);
}
else if (methodCallExpression.Object is MemberExpression)
{
//判断示例2：m.ID.Contains(123)
MemberExpression MemberExpression = (MemberExpression)methodCallExpression.Object;
if (MemberExpression.Expression != null && this.expressParameterNameCollection.Contains(MemberExpression.Expression))
{
result = true;
}
}
}
//判断示例3： int[] ids=new ids[]{1,2,3}; ids.Contains(m.ID)
if (result == false && methodCallExpression.Arguments != null && methodCallExpression.Arguments.Count > )
{
foreach (Expression express in methodCallExpression.Arguments)
{
if (express is MemberExpression || express is MethodCallExpression)
{
this.IsContainsParameterExpress(express, ref result);
}
else if (this.expressParameterNameCollection.Contains(express))
{
result = true;
break;
}
}
}
}
}
} private static object GetValue(Expression member)
{
// source: http://stackoverflow.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
{
/// <summary>
/// 含有参数变量的SQL语句
/// </summary>
public string Sql { get; set; }
/// <summary>
/// SQL语句中的参数变量
/// </summary>
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 == )
{
sql.Append("null,");
}
sql[sql.Length - ] = ')';
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})"
};
}
}
}
最新文章
- 原生js操作dom备忘
- 地图编辑器V3
- 软件工程个人作业-Week2
- Redis - 作为 LRU 缓存
- 使用QTP测试Web对象
- NRF51822之GPIOTE使用
- swing LayoutManager 和多态
- OEM - emctl resetTZ agent 设置时区
- IEnumerable,ICollection,IList,List区别
- SQL Server 2008 数据库误删除数据的恢复
- VMware虚拟机中调整Linux分区大小——使用gparted
- PHPStrom上传文件报502错误原因
- Markdown 11 种基础语法
- 某网站看到的某神的Symfony_使用心得
- python中数字类型与处理工具
- JS的简单用法
- Request.getparameternames 获取form表单里面所有的请求参数 。 返回一个Enumeration类型的枚举.
- C#解压文件，Excel操作
- pdf.js使用总结#如何在网页读取并显示PDF格式文档
- 用VC进行64位编程