當(dāng)前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

lamber表达式sql_lambda表达式转换sql

發(fā)布時間：2024/9/27 编程问答 19 豆豆

生活随笔收集整理的這篇文章主要介紹了 lamber表达式sql_lambda表达式转换sql 小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.

usingSystem;usingSystem.Collections.Generic;usingSystem.ComponentModel;usingSystem.Linq;usingSystem.Linq.Expressions;usingSystem.Text;usingSystem.Threading.Tasks;namespaceMaiCore

{///

///

public classLambdaToSqlHelper

{///

///NodeType枚舉///

private enumEnumNodeType

{///

///二元運(yùn)算符///

[Description("二元運(yùn)算符")]

BinaryOperator= 1,///

///一元運(yùn)算符///

[Description("一元運(yùn)算符")]

UndryOperator= 2,///

///常量表達(dá)式///

[Description("常量表達(dá)式")]

Constant= 3,///

///成員(變量)///

[Description("成員(變量)")]

MemberAccess= 4,///

///函數(shù)///

[Description("函數(shù)")]

Call= 5,///

///未知///

[Description("未知")]

Unknown= -99,///

///不支持///

[Description("不支持")]

NotSupported= -98}///

///判斷表達(dá)式類型///

/// lambda表達(dá)式

///

private staticEnumNodeType CheckExpressionType(Expression exp)

{switch(exp.NodeType)

{caseExpressionType.AndAlso:caseExpressionType.OrElse:caseExpressionType.Equal:caseExpressionType.GreaterThanOrEqual:caseExpressionType.LessThanOrEqual:caseExpressionType.GreaterThan:caseExpressionType.LessThan:caseExpressionType.NotEqual:returnEnumNodeType.BinaryOperator;caseExpressionType.Constant:returnEnumNodeType.Constant;caseExpressionType.MemberAccess:returnEnumNodeType.MemberAccess;caseExpressionType.Call:returnEnumNodeType.Call;caseExpressionType.Not:caseExpressionType.Convert:returnEnumNodeType.UndryOperator;default:returnEnumNodeType.Unknown;

}

}///

///表達(dá)式類型轉(zhuǎn)換///

///

private static stringExpressionTypeCast(ExpressionType type)

{switch(type)

{caseExpressionType.And:caseExpressionType.AndAlso:return "and";caseExpressionType.Equal:return "=";caseExpressionType.GreaterThan:return ">";caseExpressionType.GreaterThanOrEqual:return ">=";caseExpressionType.LessThan:return "";caseExpressionType.Or:caseExpressionType.OrElse:return "or";caseExpressionType.Add:caseExpressionType.AddChecked:return "+";caseExpressionType.Subtract:caseExpressionType.SubtractChecked:return "-";caseExpressionType.Divide:return "/";caseExpressionType.Multiply:caseExpressionType.MultiplyChecked:return "*";default:return null;

}

}private static string BinarExpressionProvider(Expression exp, ListlistSqlParaModel)

{

BinaryExpression be= exp asBinaryExpression;

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";

}elsesb+=sbTmp;return sb += ")";

}private static string ConstantExpressionProvider(Expression exp, ListlistSqlParaModel)

{

ConstantExpression ce= exp asConstantExpression;if (ce.Value == null)

{return "null";

}else if (ce.Value isValueType)

{

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, ListlistSqlParaModel)

{

LambdaExpression le= exp asLambdaExpression;returnExpressionRouter(le.Body, listSqlParaModel);

}private static string MemberExpressionProvider(Expression exp, ListlistSqlParaModel)

{if (!exp.ToString().StartsWith("value"))

{

MemberExpression me= exp asMemberExpression;if (me.Member.Name == "Now")

{

GetSqlParaModel(listSqlParaModel, DateTime.Now);return "@para" +listSqlParaModel.Count;

}returnme.Member.Name;

}else{var result =Expression.Lambda(exp).Compile().DynamicInvoke();if (result == null)

{return "null";

}else if (result isValueType)

{

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= newStringBuilder();foreach (var r inrl)

{

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= newStringBuilder();foreach (var r inrl)

{

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, ListlistSqlParaModel)

{

MethodCallExpression mce= exp asMethodCallExpression;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, ListlistSqlParaModel)

{

NewArrayExpression ae= exp asNewArrayExpression;

StringBuilder sbTmp= newStringBuilder();foreach (Expression ex inae.Expressions)

{

sbTmp.Append(ExpressionRouter(ex, listSqlParaModel));

sbTmp.Append(",");

}return sbTmp.ToString(0, sbTmp.Length - 1);

}private static string ParameterExpressionProvider(Expression exp, ListlistSqlParaModel)

{

ParameterExpression pe= exp asParameterExpression;returnpe.Type.Name;

}private static string UnaryExpressionProvider(Expression exp, ListlistSqlParaModel)

{

UnaryExpression ue= exp asUnaryExpression;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");

}

}returnresult;

}///

///路由計(jì)算///

///

private static string ExpressionRouter(Expression exp, ListlistSqlParaModel)

{var nodeType =exp.NodeType;if (exp is BinaryExpression) //表示具有二進(jìn)制運(yùn)算符的表達(dá)式

{returnBinarExpressionProvider(exp, listSqlParaModel);

}else if (exp is ConstantExpression) //表示具有常數(shù)值的表達(dá)式

{returnConstantExpressionProvider(exp, listSqlParaModel);

}else if (exp is LambdaExpression) //介紹 lambda 表達(dá)式。它捕獲一個類似于 .NET 方法主體的代碼塊

{returnLambdaExpressionProvider(exp, listSqlParaModel);

}else if (exp is MemberExpression) //表示訪問字段或?qū)傩?/p>

{returnMemberExpressionProvider(exp, listSqlParaModel);

}else if (exp is MethodCallExpression) //表示對靜態(tài)方法或?qū)嵗椒ǖ恼{(diào)用

{returnMethodCallExpressionProvider(exp, listSqlParaModel);

}else if (exp is NewArrayExpression) //表示創(chuàng)建一個新數(shù)組，并可能初始化該新數(shù)組的元素

{returnNewArrayExpressionProvider(exp, listSqlParaModel);

}else if (exp is ParameterExpression) //表示一個命名的參數(shù)表達(dá)式。

{returnParameterExpressionProvider(exp, listSqlParaModel);

}else if (exp is UnaryExpression) //表示具有一元運(yùn)算符的表達(dá)式

{returnUnaryExpressionProvider(exp, listSqlParaModel);

}return null;

}///

///值類型轉(zhuǎn)換///

///

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;

}

}///

///sql參數(shù)///

///

private static void GetSqlParaModel(List listSqlParaModel, objectval)

{

SqlParaModel p= newSqlParaModel();

p.name= "para" + (listSqlParaModel.Count + 1);

p.value=val;

listSqlParaModel.Add(p);

}///

///lambda表達(dá)式轉(zhuǎn)換sql///

///

public static string GetWhereSql(Expression> where, List listSqlParaModel) where T : class{string result = string.Empty;if (where != null)

{

Expression exp= where.Body asExpression;

result=ExpressionRouter(exp, listSqlParaModel);

}if (result != string.Empty)

{

result= "where" +result;

}returnresult;

}///

///lambda表達(dá)式轉(zhuǎn)換sql///

///

public static string GetOrderBySql(Expression, IOrderedQueryable>> orderBy) where T : class{string result = string.Empty;if (orderBy != null && orderBy.Body isMethodCallExpression)

{

MethodCallExpression exp= orderBy.Body asMethodCallExpression;

List listSqlParaModel = new List();

result=MethodCallExpressionProvider(exp, listSqlParaModel);

}if (result != string.Empty)

{

result= "order by" +result;

}returnresult;

}///

///lambda表達(dá)式轉(zhuǎn)換sql///

///

public static string GetQueryField(Expression>fields)

{

StringBuilder sbSelectFields= newStringBuilder();if (fields.Body isNewExpression)

{

NewExpression ne= fields.Body asNewExpression;for (var i = 0; i < ne.Members.Count; i++)

{

sbSelectFields.Append(ne.Members[i].Name+ ",");

}

}else if (fields.Body isParameterExpression)

{

sbSelectFields.Append("*");

}else{

sbSelectFields.Append("*");

}if (sbSelectFields.Length > 1)

{

sbSelectFields= sbSelectFields.Remove(sbSelectFields.Length - 1, 1);

}returnsbSelectFields.ToString();

}

}-----------------------------------------------------------------------------------------------demo：classProgram

{static void Main(string[] args)

{//Expression> where = w => w.id == "123456";

Expression> where = w => w.id.Contains("1");

List listSqlParaModel = new List();var sql = LambdaToSqlHelper.GetWhereSql(where, listSqlParaModel);

}

}classMyClass

{public stringid;public stringname;public stringdesc;public decimalprice;public intstock;public boolisShow;publicDateTime createTime;

}

總結(jié)

以上是生活随笔為你收集整理的lamber表达式sql_lambda表达式转换sql的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。