這是我在博客園的第一遍文章，想分享下lambda表達式轉換sql。喜歡EF的便捷與優雅，不喜歡生成的一坨sql。（PS：公司封裝了一套訪問資料庫的方法，所以不確定是不是EF的問題，反正就是一坨密密麻麻的的sql，我有點點處女座小糾結，雖然我是天蝎座）好了，廢話少說。 using System;usi ...
這是我在博客園的第一遍文章，想分享下lambda表達式轉換sql。
喜歡EF的便捷與優雅，不喜歡生成的一坨sql。（PS：公司封裝了一套訪問資料庫的方法，所以不確定是不是EF的問題，反正就是一坨密密麻麻的的sql，我有點點處女座小糾結，雖然我是天蝎座）
好了，廢話少說。
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;
namespace MaiCore
{
/// <summary>
///
/// </summary>
public class LambdaToSqlHelper
{
/// <summary>
/// NodeType枚舉
/// </summary>
private enum EnumNodeType
{
/// <summary>
/// 二元運算符
/// </summary>
[Description("二元運算符")]
BinaryOperator = 1,
/// <summary>
/// 一元運算符
/// </summary>
[Description("一元運算符")]
UndryOperator = 2,
/// <summary>
/// 常量表達式
/// </summary>
[Description("常量表達式")]
Constant = 3,
/// <summary>
/// 成員（變數）
/// </summary>
[Description("成員（變數）")]
MemberAccess = 4,
/// <summary>
/// 函數
/// </summary>
[Description("函數")]
Call = 5,
/// <summary>
/// 未知
/// </summary>
[Description("未知")]
Unknown = -99,
/// <summary>
/// 不支持
/// </summary>
[Description("不支持")]
NotSupported = -98
}
/// <summary>
/// 判斷表達式類型
/// </summary>
/// <param name="exp">lambda表達式</param>
/// <returns></returns>
private static EnumNodeType CheckExpressionType(Expression exp)
{
switch (exp.NodeType)
{
case ExpressionType.AndAlso:
case ExpressionType.OrElse:
case ExpressionType.Equal:
case ExpressionType.GreaterThanOrEqual:
case ExpressionType.LessThanOrEqual:
case ExpressionType.GreaterThan:
case ExpressionType.LessThan:
case ExpressionType.NotEqual:
return EnumNodeType.BinaryOperator;
case ExpressionType.Constant:
return EnumNodeType.Constant;
case ExpressionType.MemberAccess:
return EnumNodeType.MemberAccess;
case ExpressionType.Call:
return EnumNodeType.Call;
case ExpressionType.Not:
case ExpressionType.Convert:
return EnumNodeType.UndryOperator;
default:
return EnumNodeType.Unknown;
}
}
/// <summary>
/// 表達式類型轉換
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
private static string ExpressionTypeCast(ExpressionType type)
{
switch (type)
{
case ExpressionType.And:
case ExpressionType.AndAlso:
return " and ";
case ExpressionType.Equal:
return " = ";
case ExpressionType.GreaterThan:
return " > ";
case ExpressionType.GreaterThanOrEqual:
return " >= ";
case ExpressionType.LessThan:
return " < ";
case ExpressionType.LessThanOrEqual:
return " <= ";
case ExpressionType.NotEqual:
return " <> ";
case ExpressionType.Or:
case ExpressionType.OrElse:
return " or ";
case ExpressionType.Add:
case ExpressionType.AddChecked:
return " + ";
case ExpressionType.Subtract:
case ExpressionType.SubtractChecked:
return " - ";
case ExpressionType.Divide:
return " / ";
case ExpressionType.Multiply:
case ExpressionType.MultiplyChecked:
return " * ";
default:
return null;
}
}
private static string BinarExpressionProvider(Expression exp, List<SqlParaModel> listSqlParaModel)
{
BinaryExpression be = exp as BinaryExpression;
Expression left = be.Left;
Expression right = be.Right;
ExpressionType type = be.NodeType;
string sb = "(";
//先處理左邊
sb += ExpressionRouter(left, listSqlParaModel);
sb += ExpressionTypeCast(type);
//再處理右邊
string sbTmp = ExpressionRouter(right, listSqlParaModel);
if (sbTmp == "null")
{
if (sb.EndsWith(" = "))
sb = sb.Substring(0, sb.Length - 2) + " is null";
else if (sb.EndsWith(" <> "))
sb = sb.Substring(0, sb.Length - 2) + " is not null";
}
else
sb += sbTmp;
return sb += ")";
}
private static string ConstantExpressionProvider(Expression exp, List<SqlParaModel> listSqlParaModel)
{
ConstantExpression ce = exp as ConstantExpression;
if (ce.Value == null)
{
return "null";
}
else if (ce.Value is ValueType)
{
GetSqlParaModel(listSqlParaModel, GetValueType(ce.Value));
return "@para" + listSqlParaModel.Count;
}
else if (ce.Value is string || ce.Value is DateTime || ce.Value is char)
{
GetSqlParaModel(listSqlParaModel, GetValueType(ce.Value));
return "@para" + listSqlParaModel.Count;
}
return "";
}
private static string LambdaExpressionProvider(Expression exp, List<SqlParaModel> listSqlParaModel)
{
LambdaExpression le = exp as LambdaExpression;
return ExpressionRouter(le.Body, listSqlParaModel);
}
private static string MemberExpressionProvider(Expression exp, List<SqlParaModel> listSqlParaModel)
{
if (!exp.ToString().StartsWith("value"))
{
MemberExpression me = exp as MemberExpression;
if (me.Member.Name == "Now")
{
GetSqlParaModel(listSqlParaModel, DateTime.Now);
return "@para" + listSqlParaModel.Count;
}
return me.Member.Name;
}
else
{
var result = Expression.Lambda(exp).Compile().DynamicInvoke();
if (result == null)
{
return "null";
}
else if (result is ValueType)
{
GetSqlParaModel(listSqlParaModel, GetValueType(result));
return "@para" + listSqlParaModel.Count;
}
else if (result is string || result is DateTime || result is char)
{
GetSqlParaModel(listSqlParaModel, GetValueType(result));
return "@para" + listSqlParaModel.Count;
}
else if (result is int[])
{
var rl = result as int[];
StringBuilder sbTmp = new StringBuilder();
foreach (var r in rl)
{
GetSqlParaModel(listSqlParaModel, r.ToString().ToInt32());
sbTmp.Append("@para" + listSqlParaModel.Count + ",");
}
return sbTmp.ToString().Substring(0, sbTmp.ToString().Length - 1);
}
else if (result is string[])
{
var rl = result as string[];
StringBuilder sbTmp = new StringBuilder();
foreach (var r in rl)
{
GetSqlParaModel(listSqlParaModel, r.ToString());
sbTmp.Append("@para" + listSqlParaModel.Count + ",");
}
return sbTmp.ToString().Substring(0, sbTmp.ToString().Length - 1);
}
}
return "";
}
private static string MethodCallExpressionProvider(Expression exp, List<SqlParaModel> listSqlParaModel)
{
MethodCallExpression mce = exp as MethodCallExpression;
if (mce.Method.Name == "Contains")
{
if (mce.Object == null)
{
return string.Format("{0} in ({1})", ExpressionRouter(mce.Arguments[1], listSqlParaModel), ExpressionRouter(mce.Arguments[0], listSqlParaModel));
}
else
{
if (mce.Object.NodeType == ExpressionType.MemberAccess)
{
//w => w.name.Contains("1")
var _name = ExpressionRouter(mce.Object, listSqlParaModel);
var _value = ExpressionRouter(mce.Arguments[0], listSqlParaModel);
var index = _value.RetainNumber().ToInt32() - 1;
listSqlParaModel[index].value = "%{0}%".FormatWith(listSqlParaModel[index].value);
return string.Format("{0} like {1}", _name, _value);
}
}
}
else if (mce.Method.Name == "OrderBy")
{
return string.Format("{0} asc", ExpressionRouter(mce.Arguments[1], listSqlParaModel));
}
else if (mce.Method.Name == "OrderByDescending")
{
return string.Format("{0} desc", ExpressionRouter(mce.Arguments[1], listSqlParaModel));
}
else if (mce.Method.Name == "ThenBy")
{
return string.Format("{0},{1} asc", MethodCallExpressionProvider(mce.Arguments[0], listSqlParaModel), ExpressionRouter(mce.Arguments[1], listSqlParaModel));
}
else if (mce.Method.Name == "ThenByDescending")
{
return string.Format("{0},{1} desc", MethodCallExpressionProvider(mce.Arguments[0], listSqlParaModel), ExpressionRouter(mce.Arguments[1], listSqlParaModel));
}
else if (mce.Method.Name == "Like")
{
return string.Format("({0} like {1})", ExpressionRouter(mce.Arguments[0], listSqlParaModel), ExpressionRouter(mce.Arguments[1], listSqlParaModel).Replace("'", ""));
}
else if (mce.Method.Name == "NotLike")
{
return string.Format("({0} not like '%{1}%')", ExpressionRouter(mce.Arguments[0], listSqlParaModel), ExpressionRouter(mce.Arguments[1], listSqlParaModel).Replace("'", ""));
}
else if (mce.Method.Name == "In")
{
return string.Format("{0} in ({1})", ExpressionRouter(mce.Arguments[0], listSqlParaModel), ExpressionRouter(mce.Arguments[1], listSqlParaModel));
}
else if (mce.Method.Name == "NotIn")
{
return string.Format("{0} not in ({1})", ExpressionRouter(mce.Arguments[0], listSqlParaModel), ExpressionRouter(mce.Arguments[1], listSqlParaModel));
}
return "";
}
private static string NewArrayExpressionProvider(Expression exp, List<SqlParaModel> listSqlParaModel)
{
NewArrayExpression ae = exp as NewArrayExpression;
StringBuilder sbTmp = new StringBuilder();
foreach (Expression ex in ae.Expressions)
{
sbTmp.Append(ExpressionRouter(ex, listSqlParaModel));
sbTmp.Append(",");
}
return sbTmp.ToString(0, sbTmp.Length - 1);
}
private static string ParameterExpressionProvider(Expression exp, List<SqlParaModel> listSqlParaModel)
{
ParameterExpression pe = exp as ParameterExpression;
return pe.Type.Name;
}
private static string UnaryExpressionProvider(Expression exp, List<SqlParaModel> listSqlParaModel)
{
UnaryExpression ue = exp as UnaryExpression;
var result = ExpressionRouter(ue.Operand, listSqlParaModel);
ExpressionType type = exp.NodeType;
if (type == ExpressionType.Not)
{
if (result.Contains(" in "))
{
result = result.Replace(" in ", " not in ");
}
if (result.Contains(" like "))
{
result = result.Replace(" like ", " not like ");
}
}
return result;
}
/// <summary>
/// 路由計算
/// </summary>
/// <param name="exp"></param>
/// <param name="listSqlParaModel"></param>
/// <returns></returns>
private static string ExpressionRouter(Expression exp, List<SqlParaModel> listSqlParaModel)
{
var nodeType = exp.NodeType;
if (exp is BinaryExpression) //表示具有二進位運算符的表達式
{
return BinarExpressionProvider(exp, listSqlParaModel);
}
else if (exp is ConstantExpression) //表示具有常數值的表達式
{
return ConstantExpressionProvider(exp, listSqlParaModel);
}
else if (exp is LambdaExpression) //介紹 lambda 表達式。 它捕獲一個類似於 .NET 方法主體的代碼塊
{
return LambdaExpressionProvider(exp, listSqlParaModel);
}
else if (exp is MemberExpression) //表示訪問欄位或屬性
{
return MemberExpressionProvider(exp, listSqlParaModel);
}
else if (exp is MethodCallExpression) //表示對靜態方法或實例方法的調用
{
return MethodCallExpressionProvider(exp, listSqlParaModel);
}
else if (exp is NewArrayExpression) //表示創建一個新數組，並可能初始化該新數組的元素
{
return NewArrayExpressionProvider(exp, listSqlParaModel);
}
else if (exp is ParameterExpression) //表示一個命名的參數表達式。
{
return ParameterExpressionProvider(exp, listSqlParaModel);
}
else if (exp is UnaryExpression) //表示具有一元運算符的表達式
{
return UnaryExpressionProvider(exp, listSqlParaModel);
}
return null;
}
/// <summary>
/// 值類型轉換
/// </summary>
/// <param name="_value"></param>
/// <returns></returns>
private static object GetValueType(object _value)
{
var _type = _value.GetType().Name;
switch (_type)
{
case "Decimal ": return _value.ToDecimal();
case "Int32": return _value.ToInt32();
case "DateTime": return _value.ToDateTime();
case "String": return _value.ToString();
case "Char":return _value.ToChar();
case "Boolean":return _value.ToBoolean();
default: return _value;
}
}
/// <summary>
/// sql參數
/// </summary>
/// <param name="listSqlParaModel"></param>
/// <param name="val"></param>
private static void GetSqlParaModel(List<SqlParaModel> listSqlParaModel, object val)
{
SqlParaModel p = new SqlParaModel();
p.name = "para" + (listSqlParaModel.Count + 1);
p.value = val;
listSqlParaModel.Add(p);
}
/// <summary>
/// lambda表達式轉換sql
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="where"></param>
/// <param name="listSqlParaModel"></param>
/// <returns></returns>
public static string GetWhereSql<T>(Expression<Func<T, bool>> where, List<SqlParaModel> listSqlParaModel) where T : class
{
string result = string.Empty;
if (where != null)
{
Expression exp = where.Body as Expression;
result = ExpressionRouter(exp, listSqlParaModel);
}
if (result != string.Empty)
{
result = " where " + result;
}
return result;
}
/// <summary>
/// lambda表達式轉換sql
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="orderBy"></param>
/// <returns></returns>
public static string GetOrderBySql<T>(Expression<Func<IQueryable<T>, IOrderedQueryable<T>>> orderBy) where T : class
{
string result = string.Empty;
if (orderBy != null && orderBy.Body is MethodCallExpression)
{
MethodCallExpression exp = orderBy.Body as MethodCallExpression;
List<SqlParaModel> listSqlParaModel = new List<SqlParaModel>();
result = MethodCallExpressionProvider(exp, listSqlParaModel);
}
if (result != string.Empty)
{
result = " order by " + result;
}
return result;
}
/// <summary>
/// lambda表達式轉換sql
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="fields"></param>
/// <returns></returns>
public static string GetQueryField<T>(Expression<Func<T, object>> fields)
{
StringBuilder sbSelectFields = new StringBuilder();
if (fields.Body is NewExpression)
{
NewExpression ne = fields.Body as NewExpression;
for (var i = 0; i < ne.Members.Count; i++)
{
sbSelectFields.Append(ne.Members[i].Name + ",");
}
}
else if (fields.Body is ParameterExpression)
{
sbSelectFields.Append("*");
}
else
{
sbSelectFields.Append("*");
}
if (sbSelectFields.Length > 1)
{
sbSelectFields = sbSelectFields.Remove(sbSelectFields.Length - 1, 1);
}
return sbSelectFields.ToString();
}
}
}
-----------------------------------------------------------------------------------------------
demo：
class Program
{
static void Main(string[] args)
{
//Expression<Func<MyClass, bool>> where = w => w.id == "123456";
Expression<Func<MyClass, bool>> where = w => w.id.Contains("1");
List<SqlParaModel> listSqlParaModel = new List<SqlParaModel>();
var sql = LambdaToSqlHelper.GetWhereSql(where, listSqlParaModel);
}
}
class MyClass
{
public string id;
public string name;
public string desc;
public decimal price;
public int stock;
public bool isShow;
public DateTime createTime;
}
-----------------------------------------------------------------------------------------------
參考：
http://www.cnblogs.com/kakura/p/6108950.html
http://www.cnblogs.com/zhyue/p/5690807.html
歡迎挑出bug或者不成熟的地方。
