志在指尖
用双手敲打未来

Lambda转为SQL

usingSystem;
usingSystem.Collections.Generic;
usingSystem.ComponentModel;
usingSystem.Linq;
usingSystem.Linq.Expressions;
usingSystem.Text;
usingSystem.Threading.Tasks;
namespaceMaiCore
{
///
///
///
publicclassLambdaToSqlHelper
{
///
///NodeType枚举
///
privateenumEnumNodeType
{
///
///二元运算符
///
[Description(“二元运算符”)]
BinaryOperator=1,
///
///一元运算符
///
[Description(“一元运算符”)]
UndryOperator=2,
///
///常量表达式
///
[Description(“常量表达式”)]
Constant=3,
///
///成员(变量)
///
[Description(“成员(变量)”)]
MemberAccess=4,
///
///函数
///
[Description(“函数”)]
Call=5,
///
///不知道
///
[Description(“不知道”)]
Unknown=-99,
///
///不支持
///
[Description(“不支持”)]
NotSupported=-98
}
///
///判断表达式类型
///
///lambda表达式
///
privatestaticEnumNodeTypeCheckExpressionType(Expressionexp)
{
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:C#
returnEnumNodeType.Unknown;
}
}
///
///表达式类型转化
///
///
///
privatestaticstringExpressionTypeCast(ExpressionTypetype)
{
switch(type)
{
caseExpressionType.And:
caseExpressionType.AndAlso:
return”and”;
caseExpressionType.Equal:
return”=”;
caseExpressionType.GreaterThan:
return”>”;
caseExpressionType.GreaterThanOrEqual:
return”>=”;
caseExpressionType.LessThan:
return”<“;
caseExpressionType.LessThanOrEqual:
return”<=”;
caseExpressionType.NotEqual:
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:
returnnull;
}
}
privatestaticstringBinarExpressionProvider(Expressionexp,ListlistSqlParaModel)
{
BinaryExpressionbe=expasBinaryExpression;
Expressionleft=be.Left;
Expressionright=be.Right;
ExpressionTypetype=be.NodeType;
stringsb=”(“;
//先处理左边
sb+=ExpressionRouter(left,listSqlParaModel);
sb+=ExpressionTypeCast(type);
//再处理右边
stringsbTmp=ExpressionRouter(right,listSqlParaModel);
if(sbTmp==”null”)
{
if(sb.EndsWith(“=”))
sb=sb.Substring(0,sb.Length-2)+”isnull”;
elseif(sb.EndsWith(“<>”))
sb=sb.Substring(0,sb.Length-2)+”isnotnull”;
}
else
sb+=sbTmp;
returnsb+=”)”;
}
privatestaticstringConstantExpressionProvider(Expressionexp,ListlistSqlParaModel)
{
ConstantExpressionce=expasConstantExpression;
if(ce.Value==null)
{
return”null”;
}
elseif(ce.ValueisValueType)
{
GetSqlParaModel(listSqlParaModel,GetValueType(ce.Value));
return”@para”+listSqlParaModel.Count;
}
elseif(ce.Valueisstring||ce.ValueisDateTime||ce.Valueischar)
{
GetSqlParaModel(listSqlParaModel,GetValueType(ce.Value));
return”@para”+listSqlParaModel.Count;
}
return””;
}
privatestaticstringLambdaExpressionProvider(Expressionexp,ListlistSqlParaModel)
{
LambdaExpressionle=expasLambdaExpression;
returnExpressionRouter(le.Body,listSqlParaModel);
}
privatestaticstringMemberExpressionProvider(Expressionexp,ListlistSqlParaModel)
{
if(!exp.ToString().StartsWith(“value”))
{
MemberExpressionme=expasMemberExpression;
if(me.Member.Name==”Now”)
{
GetSqlParaModel(listSqlParaModel,DateTime.Now);
return”@para”+listSqlParaModel.Count;
}
returnme.Member.Name;
}
else
{
varresult=Expression.Lambda(exp).Compile().DynamicInvoke();
if(result==null)
{
return”null”;
}
elseif(resultisValueType)
{
GetSqlParaModel(listSqlParaModel,GetValueType(result));
return”@para”+listSqlParaModel.Count;
}
elseif(resultisstring||resultisDateTime||resultischar)
{
GetSqlParaModel(listSqlParaModel,GetValueType(result));
return”@para”+listSqlParaModel.Count;
}
elseif(resultisint[])
{
varrl=resultasint[];
StringBuildersbTmp=newStringBuilder();
foreach(varrinrl)
{
GetSqlParaModel(listSqlParaModel,r.ToString().ToInt32());
sbTmp.Append(“@para”+listSqlParaModel.Count+”,”);
}
returnsbTmp.ToString().Substring(0,sbTmp.ToString().Length-1);
}
elseif(resultisstring[])
{
varrl=resultasstring[];
StringBuildersbTmp=newStringBuilder();
foreach(varrinrl)
{
GetSqlParaModel(listSqlParaModel,r.ToString());
sbTmp.Append(“@para”+listSqlParaModel.Count+”,”);
}
returnsbTmp.ToString().Substring(0,sbTmp.ToString().Length-1);
}
}
return””;
}
privatestaticstringMethodCallExpressionProvider(Expressionexp,ListlistSqlParaModel)
{
MethodCallExpressionmce=expasMethodCallExpression;
if(mce.Method.Name==”Contains”)
{
if(mce.Object==null)
{
returnstring.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);
varindex=_value.RetainNumber().ToInt32()-1;
listSqlParaModel[index].value=”%{0}%”.FormatWith(listSqlParaModel[index].value);
returnstring.Format(“{0}like{1}”,_name,_value);
}
}
}
elseif(mce.Method.Name==”OrderBy”)
{
returnstring.Format(“{0}asc”,ExpressionRouter(mce.Arguments[1],listSqlParaModel));
}
elseif(mce.Method.Name==”OrderByDescending”)
{
returnstring.Format(“{0}desc”,ExpressionRouter(mce.Arguments[1],listSqlParaModel));
}
elseif(mce.Method.Name==”ThenBy”)
{
returnstring.Format(“{0},{1}asc”,MethodCallExpressionProvider(mce.Arguments[0],listSqlParaModel),ExpressionRouter(mce.Arguments[1],listSqlParaModel));
}
elseif(mce.Method.Name==”ThenByDescending”)
{
returnstring.Format(“{0},{1}desc”,MethodCallExpressionProvider(mce.Arguments[0],listSqlParaModel),ExpressionRouter(mce.Arguments[1],listSqlParaModel));
}
elseif(mce.Method.Name==”Like”)
{
returnstring.Format(“({0}like{1})”,ExpressionRouter(mce.Arguments[0],listSqlParaModel),ExpressionRouter(mce.Arguments[1],listSqlParaModel).Replace(“‘”,””));
}
elseif(mce.Method.Name==”NotLike”)
{
returnstring.Format(“({0}notlike’%{1}%’)”,ExpressionRouter(mce.Arguments[0],listSqlParaModel),ExpressionRouter(mce.Arguments[1],listSqlParaModel).Replace(“‘”,””));
}
elseif(mce.Method.Name==”In”)
{
returnstring.Format(“{0}in({1})”,ExpressionRouter(mce.Arguments[0],listSqlParaModel),ExpressionRouter(mce.Arguments[1],listSqlParaModel));
}
elseif(mce.Method.Name==”NotIn”)
{
returnstring.Format(“{0}notin({1})”,ExpressionRouter(mce.Arguments[0],listSqlParaModel),ExpressionRouter(mce.Arguments[1],listSqlParaModel));
}
return””;
}
privatestaticstringNewArrayExpressionProvider(Expressionexp,ListlistSqlParaModel)
{
NewArrayExpressionae=expasNewArrayExpression;
StringBuildersbTmp=newStringBuilder();
foreach(Expressionexinae.Expressions)
{
sbTmp.Append(ExpressionRouter(ex,listSqlParaModel));
sbTmp.Append(“,”);
}
returnsbTmp.ToString(0,sbTmp.Length-1);
}
privatestaticstringParameterExpressionProvider(Expressionexp,ListlistSqlParaModel)
{
ParameterExpressionpe=expasParameterExpression;
returnpe.Type.Name;
}
privatestaticstringUnaryExpressionProvider(Expressionexp,ListlistSqlParaModel)
{
UnaryExpressionue=expasUnaryExpression;
varresult=ExpressionRouter(ue.Operand,listSqlParaModel);
ExpressionTypetype=exp.NodeType;
if(type==ExpressionType.Not)
{
if(result.Contains(“in”))
{
result=result.Replace(“in”,”notin”);
}
if(result.Contains(“like”))
{
result=result.Replace(“like”,”notlike”);
}
}
returnresult;
}
///
///路由计算
///
///
///
///
privatestaticstringExpressionRouter(Expressionexp,ListlistSqlParaModel)
{
varnodeType=exp.NodeType;
if(expisBinaryExpression)//表明具有二进制运算符的表达式
{
returnBinarExpressionProvider(exp,listSqlParaModel);
}
elseif(expisConstantExpression)//表明具有常数值的表达式
{
returnConstantExpressionProvider(exp,listSqlParaModel);
}
elseif(expisLambdaExpression)//介绍lambda表达式。它捕获一个类似于.NET办法主体的代码块
{
returnLambdaExpressionProvider(exp,listSqlParaModel);
}
elseif(expisMemberExpression)//表明拜访字段或属性
{
returnMemberExpressionProvider(exp,listSqlParaModel);
}
elseif(expisMethodCallExpression)//表明对静态办法或实例办法的调用
{
returnMethodCallExpressionProvider(exp,listSqlParaModel);
}
elseif(expisNewArrayExpression)//表明创建一个新数组,并可能初始化该新数组的元素
{
returnNewArrayExpressionProvider(exp,listSqlParaModel);
}
elseif(expisParameterExpression)//表明一个命名的参数表达式。
{
returnParameterExpressionProvider(exp,listSqlParaModel);
}
elseif(expisUnaryExpression)//表明具有一元运算符的表达式
{
returnUnaryExpressionProvider(exp,listSqlParaModel);
}
returnnull;
}
///
///值类型转化
///
///
///
privatestaticobjectGetValueType(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参数
///
///
///
privatestaticvoidGetSqlParaModel(ListlistSqlParaModel,objectval)
{
SqlParaModelp=newSqlParaModel();
p.name=”para”+(listSqlParaModel.Count+1);
p.value=val;
listSqlParaModel.Add(p);
}
///
///lambda表达式转化sql
///
///
///
///
///
publicstaticstringGetWhereSql(Expression>where,ListlistSqlParaModel)whereT:class
{
stringresult=string.Empty;
if(where!=null)
{
Expressionexp=where.BodyasExpression;
result=ExpressionRouter(exp,listSqlParaModel);
}
if(result!=string.Empty)
{
result=”where”+result;
}
returnresult;
}
///
///lambda表达式转化sql
///
///
///
///
publicstaticstringGetOrderBySql(Expression>orderBy)whereT:class
{
stringresult=string.Empty;
if(orderBy!=null&&orderBy.BodyisMethodCallExpression)
{
MethodCallExpressionexp=orderBy.BodyasMethodCallExpression;
ListlistSqlParaModel=newList();
result=MethodCallExpressionProvider(exp,listSqlParaModel);
}
if(result!=string.Empty)
{
result=”orderby”+result;
}
returnresult;
}
///
///lambda表达式转化sql
///
///
///
///
publicstaticstringGetQueryField(Expression>fields)
{
StringBuildersbSelectFields=newStringBuilder();
if(fields.BodyisNewExpression)
{
NewExpressionne=fields.BodyasNewExpression;
for(vari=0;i<ne.Members.Count;i++)
{
sbSelectFields.Append(ne.Members[i].Name+”,”);
}
}
elseif(fields.BodyisParameterExpression)
{
sbSelectFields.Append(“*”);
}
else
{
sbSelectFields.Append(“*”);
}
if(sbSelectFields.Length>1)
{
sbSelectFields=sbSelectFields.Remove(sbSelectFields.Length-1,1);
}
returnsbSelectFields.ToString();
}
}
}

未经允许不得转载:IT技术网站 » Lambda转为SQL
分享到: 更多 (0)

评论 抢沙发

评论前必须登录!

 

志在指尖 用双手敲打未来

登录/注册IT技术大全

热门IT技术

C#基础入门   SQL server数据库   系统SEO学习教程   WordPress小技巧   WordPress插件   脚本与源码下载