public override string ExpressionLambdaToSqlOther(Expression exp, ExpTSC tsc)
{
Func <Expression, string> getExp = exparg => ExpressionLambdaToSql(exparg, tsc);
switch (exp.NodeType)
{
case ExpressionType.Convert:
var operandExp = (exp as UnaryExpression)?.Operand;
var gentype = exp.Type.NullableTypeOrThis();
if (gentype != operandExp.Type.NullableTypeOrThis())
{
switch (gentype.ToString())
{
case "System.Boolean": return(_utils.Adapter.LambdaConvert_ToBoolean(operandExp.Type, getExp(operandExp)));
case "System.Byte": return(_utils.Adapter.LambdaConvert_ToByte(operandExp.Type, getExp(operandExp)));
case "System.Char": return(_utils.Adapter.LambdaConvert_ToChar(operandExp.Type, getExp(operandExp)));
case "System.DateTime": return(_utils.Adapter.LambdaConvert_ToDateTime(operandExp.Type, getExp(operandExp)));
case "System.Decimal": return(_utils.Adapter.LambdaConvert_ToDecimal(operandExp.Type, getExp(operandExp)));
case "System.Double": return(_utils.Adapter.LambdaConvert_ToDouble(operandExp.Type, getExp(operandExp)));
case "System.Int16": return(_utils.Adapter.LambdaConvert_ToInt16(operandExp.Type, getExp(operandExp)));
case "System.Int32": return(_utils.Adapter.LambdaConvert_ToInt32(operandExp.Type, getExp(operandExp)));
case "System.Int64": return(_utils.Adapter.LambdaConvert_ToInt64(operandExp.Type, getExp(operandExp)));
case "System.SByte": return(_utils.Adapter.LambdaConvert_ToSByte(operandExp.Type, getExp(operandExp)));
case "System.Single": return(_utils.Adapter.LambdaConvert_ToSingle(operandExp.Type, getExp(operandExp)));
case "System.String": return(_utils.Adapter.LambdaConvert_ToString(operandExp.Type, getExp(operandExp)));
case "System.UInt16": return(_utils.Adapter.LambdaConvert_ToUInt16(operandExp.Type, getExp(operandExp)));
case "System.UInt32": return(_utils.Adapter.LambdaConvert_ToUInt32(operandExp.Type, getExp(operandExp)));
case "System.UInt64": return(_utils.Adapter.LambdaConvert_ToUInt64(operandExp.Type, getExp(operandExp)));
case "System.Guid": return(_utils.Adapter.LambdaConvert_ToGuid(operandExp.Type, getExp(operandExp)));
}
}
break;
case ExpressionType.Call:
var callExp = exp as MethodCallExpression;
switch (callExp.Method.Name)
{
case "Parse":
case "TryParse":
switch (callExp.Method.DeclaringType.NullableTypeOrThis().ToString())
{
case "System.Boolean": return(_utils.Adapter.LambdaConvert_ToBoolean(callExp.Method.DeclaringType, getExp(callExp.Arguments[0])));
case "System.Byte": return(_utils.Adapter.LambdaConvert_ToByte(callExp.Method.DeclaringType, getExp(callExp.Arguments[0])));
case "System.Char": return(_utils.Adapter.LambdaConvert_ToChar(callExp.Method.DeclaringType, getExp(callExp.Arguments[0])));
case "System.DateTime": return(_utils.Adapter.LambdaConvert_ToDateTime(callExp.Method.DeclaringType, getExp(callExp.Arguments[0])));
case "System.Decimal": return(_utils.Adapter.LambdaConvert_ToDecimal(callExp.Method.DeclaringType, getExp(callExp.Arguments[0])));
case "System.Double": return(_utils.Adapter.LambdaConvert_ToDouble(callExp.Method.DeclaringType, getExp(callExp.Arguments[0])));
case "System.Int16": return(_utils.Adapter.LambdaConvert_ToInt16(callExp.Method.DeclaringType, getExp(callExp.Arguments[0])));
case "System.Int32": return(_utils.Adapter.LambdaConvert_ToInt32(callExp.Method.DeclaringType, getExp(callExp.Arguments[0])));
case "System.Int64": return(_utils.Adapter.LambdaConvert_ToInt64(callExp.Method.DeclaringType, getExp(callExp.Arguments[0])));
case "System.SByte": return(_utils.Adapter.LambdaConvert_ToSByte(callExp.Method.DeclaringType, getExp(callExp.Arguments[0])));
case "System.Single": return(_utils.Adapter.LambdaConvert_ToSingle(callExp.Method.DeclaringType, getExp(callExp.Arguments[0])));
case "System.UInt16": return(_utils.Adapter.LambdaConvert_ToUInt16(callExp.Method.DeclaringType, getExp(callExp.Arguments[0])));
case "System.UInt32": return(_utils.Adapter.LambdaConvert_ToUInt32(callExp.Method.DeclaringType, getExp(callExp.Arguments[0])));
case "System.UInt64": return(_utils.Adapter.LambdaConvert_ToUInt64(callExp.Method.DeclaringType, getExp(callExp.Arguments[0])));
case "System.Guid": return(_utils.Adapter.LambdaConvert_ToGuid(callExp.Method.DeclaringType, getExp(callExp.Arguments[0])));
}
break;
case "NewGuid":
switch (callExp.Method.DeclaringType.NullableTypeOrThis().ToString())
{
case "System.Guid": return(_utils.Adapter.LambdaGuid_NewGuid);
}
break;
case "Next":
if (callExp.Object?.Type == typeof(Random))
{
return(_utils.Adapter.LambdaRandom_Next);
}
break;
case "NextDouble":
if (callExp.Object?.Type == typeof(Random))
{
return(_utils.Adapter.LambdaRandom_NextDouble);
}
break;
case "Random":
if (callExp.Method.DeclaringType.IsNumberType())
{
return(_utils.Adapter.LambdaRandom_NextDouble);
}
break;
case "ToString":
if (callExp.Object != null)
{
return(callExp.Arguments.Count == 0 ? _utils.Adapter.LambdaConvert_ToString(callExp.Object.Type, getExp(callExp.Object)) : null);
}
break;
}
var objExp = callExp.Object;
var objType = objExp?.Type;
if (objType?.FullName == "System.Byte[]")
{
return(null);
}
var argIndex = 0;
if (objType == null && callExp.Method.DeclaringType == typeof(Enumerable))
{
objExp = callExp.Arguments.FirstOrDefault();
objType = objExp?.Type;
argIndex++;
}
if (objType == null)
{
objType = callExp.Method.DeclaringType;
}
if (objType != null || objType.IsArrayOrList())
{
tsc?.SetMapTypeTmp(null);
var args1 = getExp(callExp.Arguments[argIndex]);
var oldMapType = tsc?.SetMapTypeReturnOld(tsc?.mapTypeTmp);
var left = objExp == null ? null : getExp(objExp);
tsc.SetMapTypeReturnOld(oldMapType);
switch (callExp.Method.Name)
{
case "Contains":
//判断 in
return($"({args1}) in {left}");
}
}
break;
case ExpressionType.NewArrayInit:
var arrExp = exp as NewArrayExpression;
var arrSb = new StringBuilder();
arrSb.Append("(");
for (var a = 0; a < arrExp.Expressions.Count; a++)
{
if (a > 0)
{
arrSb.Append(",");
}
arrSb.Append(getExp(arrExp.Expressions[a]));
}
if (arrSb.Length == 1)
{
arrSb.Append("NULL");
}
return(arrSb.Append(")").ToString());
case ExpressionType.ListInit:
var listExp = exp as ListInitExpression;
var listSb = new StringBuilder();
listSb.Append("(");
for (var a = 0; a < listExp.Initializers.Count; a++)
{
if (listExp.Initializers[a].Arguments.Any() == false)
{
continue;
}
if (a > 0)
{
listSb.Append(",");
}
listSb.Append(getExp(listExp.Initializers[a].Arguments.FirstOrDefault()));
}
if (listSb.Length == 1)
{
listSb.Append("NULL");
}
return(listSb.Append(")").ToString());
case ExpressionType.New:
var newExp = exp as NewExpression;
if (typeof(IList).IsAssignableFrom(newExp.Type))
{
if (newExp.Arguments.Count == 0)
{
return("(NULL)");
}
if (typeof(IEnumerable).IsAssignableFrom(newExp.Arguments[0].Type) == false)
{
return("(NULL)");
}
return(getExp(newExp.Arguments[0]));
}
return(null);
}
return(null);
}
public override string ExpressionLambdaToSqlOther(Expression exp, ExpTSC tsc)
{
Func <Expression, string> getExp = exparg => ExpressionLambdaToSql(exparg, tsc);
switch (exp.NodeType)
{
case ExpressionType.Convert:
var operandExp = (exp as UnaryExpression)?.Operand;
var gentype = exp.Type.NullableTypeOrThis();
if (gentype != operandExp.Type.NullableTypeOrThis())
{
switch (exp.Type.NullableTypeOrThis().ToString())
{
//case "System.Boolean": return $"({getExp(operandExp)} not in ('0','false'))";
case "System.Byte": return($"cast({getExp(operandExp)} as number)");
case "System.Char": return($"substr(to_char({getExp(operandExp)}), 1, 1)");
case "System.DateTime": return($"to_timestamp({getExp(operandExp)},'YYYY-MM-DD HH24:MI:SS.FF6')");
case "System.Decimal": return($"cast({getExp(operandExp)} as number)");
case "System.Double": return($"cast({getExp(operandExp)} as number)");
case "System.Int16":
case "System.Int32":
case "System.Int64":
case "System.SByte": return($"cast({getExp(operandExp)} as number)");
case "System.Single": return($"cast({getExp(operandExp)} as number)");
case "System.String": return($"to_char({getExp(operandExp)})");
case "System.UInt16":
case "System.UInt32":
case "System.UInt64": return($"cast({getExp(operandExp)} as number)");
case "System.Guid":
if (tsc.mapType == typeof(byte[]))
{
return($"hextoraw({getExp(operandExp)})");
}
return($"to_char({getExp(operandExp)})");
}
}
break;
case ExpressionType.Call:
var callExp = exp as MethodCallExpression;
switch (callExp.Method.Name)
{
case "Parse":
case "TryParse":
switch (callExp.Method.DeclaringType.NullableTypeOrThis().ToString())
{
//case "System.Boolean": return $"({getExp(callExp.Arguments[0])} not in ('0','false'))";
case "System.Byte": return($"cast({getExp(callExp.Arguments[0])} as number)");
case "System.Char": return($"substr(to_char({getExp(callExp.Arguments[0])}), 1, 1)");
case "System.DateTime": return($"to_timestamp({getExp(callExp.Arguments[0])},'YYYY-MM-DD HH24:MI:SS.FF6')");
case "System.Decimal": return($"cast({getExp(callExp.Arguments[0])} as number)");
case "System.Double": return($"cast({getExp(callExp.Arguments[0])} as number)");
case "System.Int16":
case "System.Int32":
case "System.Int64":
case "System.SByte": return($"cast({getExp(callExp.Arguments[0])} as number)");
case "System.Single": return($"cast({getExp(callExp.Arguments[0])} as number)");
case "System.UInt16":
case "System.UInt32":
case "System.UInt64": return($"cast({getExp(callExp.Arguments[0])} as number)");
case "System.Guid":
if (tsc.mapType == typeof(byte[]))
{
return($"hextoraw({getExp(callExp.Arguments[0])})");
}
return($"to_char({getExp(callExp.Arguments[0])})");
}
return(null);
case "NewGuid":
return(null);
case "Next":
if (callExp.Object?.Type == typeof(Random))
{
return("cast(dbms_random.value*1000000000 as number)");
}
return(null);
case "NextDouble":
if (callExp.Object?.Type == typeof(Random))
{
return("dbms_random.value");
}
return(null);
case "Random":
if (callExp.Method.DeclaringType.IsNumberType())
{
return("dbms_random.value");
}
return(null);
case "ToString":
if (callExp.Object != null)
{
return(callExp.Arguments.Count == 0 ? $"to_char({getExp(callExp.Object)})" : null);
}
return(null);
}
var objExp = callExp.Object;
var objType = objExp?.Type;
if (objType?.FullName == "System.Byte[]")
{
return(null);
}
var argIndex = 0;
if (objType == null && callExp.Method.DeclaringType == typeof(Enumerable))
{
objExp = callExp.Arguments.FirstOrDefault();
objType = objExp?.Type;
argIndex++;
}
if (objType == null)
{
objType = callExp.Method.DeclaringType;
}
if (objType != null || objType.IsArrayOrList())
{
if (argIndex >= callExp.Arguments.Count)
{
break;
}
tsc.SetMapColumnTmp(null);
var args1 = getExp(callExp.Arguments[argIndex]);
var oldMapType = tsc.SetMapTypeReturnOld(tsc.mapTypeTmp);
var oldDbParams = tsc.SetDbParamsReturnOld(null);
var left = objExp == null ? null : getExp(objExp);
tsc.SetMapColumnTmp(null).SetMapTypeReturnOld(oldMapType);
tsc.SetDbParamsReturnOld(oldDbParams);
switch (callExp.Method.Name)
{
case "Contains":
//判断 in //在各大 Provider AdoProvider 中已约定，500元素分割, 3空格\r\n4空格
return($"(({args1}) in {left.Replace(", \r\n \r\n", $") \r\n OR ({args1}) in (")})");
}
}
break;
case ExpressionType.NewArrayInit:
var arrExp = exp as NewArrayExpression;
var arrSb = new StringBuilder();
arrSb.Append("(");
for (var a = 0; a < arrExp.Expressions.Count; a++)
{
if (a > 0)
{
arrSb.Append(",");
}
arrSb.Append(getExp(arrExp.Expressions[a]));
}
if (arrSb.Length == 1)
{
arrSb.Append("NULL");
}
return(arrSb.Append(")").ToString());
case ExpressionType.ListInit:
var listExp = exp as ListInitExpression;
var listSb = new StringBuilder();
listSb.Append("(");
for (var a = 0; a < listExp.Initializers.Count; a++)
{
if (listExp.Initializers[a].Arguments.Any() == false)
{
continue;
}
if (a > 0)
{
listSb.Append(",");
}
listSb.Append(getExp(listExp.Initializers[a].Arguments.FirstOrDefault()));
}
if (listSb.Length == 1)
{
listSb.Append("NULL");
}
return(listSb.Append(")").ToString());
case ExpressionType.New:
var newExp = exp as NewExpression;
if (typeof(IList).IsAssignableFrom(newExp.Type))
{
if (newExp.Arguments.Count == 0)
{
return("(NULL)");
}
if (typeof(IEnumerable).IsAssignableFrom(newExp.Arguments[0].Type) == false)
{
return("(NULL)");
}
return(getExp(newExp.Arguments[0]));
}
return(null);
}
return(null);
}
public override string ExpressionLambdaToSqlOther(Expression exp, ExpTSC tsc)
{
Func <Expression, string> getExp = exparg => ExpressionLambdaToSql(exparg, tsc);
switch (exp.NodeType)
{
case ExpressionType.ArrayLength:
var arrOper = (exp as UnaryExpression)?.Operand;
var arrOperExp = getExp(arrOper);
if (arrOperExp.StartsWith("(") || arrOperExp.EndsWith(")"))
{
return($"array_length(array[{arrOperExp.TrimStart('(').TrimEnd(')')}],1)");
}
if (arrOper.Type == typeof(byte[]))
{
return($"octet_length({getExp(arrOper)})");
}
return($"case when {arrOperExp} is null then 0 else array_length({arrOperExp},1) end");
case ExpressionType.Convert:
var operandExp = (exp as UnaryExpression)?.Operand;
var gentype = exp.Type.NullableTypeOrThis();
if (gentype != operandExp.Type.NullableTypeOrThis())
{
switch (exp.Type.NullableTypeOrThis().ToString())
{
case "System.Boolean": return($"(({getExp(operandExp)})::varchar not in ('0','false','f','no'))");
case "System.Byte": return($"({getExp(operandExp)})::int2");
case "System.Char": return($"substr(({getExp(operandExp)})::char, 1, 1)");
case "System.DateTime": return($"({getExp(operandExp)})::timestamp");
case "System.Decimal": return($"({getExp(operandExp)})::numeric");
case "System.Double": return($"({getExp(operandExp)})::float8");
case "System.Int16": return($"({getExp(operandExp)})::int2");
case "System.Int32": return($"({getExp(operandExp)})::int4");
case "System.Int64": return($"({getExp(operandExp)})::int8");
case "System.SByte": return($"({getExp(operandExp)})::int2");
case "System.Single": return($"({getExp(operandExp)})::float4");
case "System.String": return($"({getExp(operandExp)})::text");
case "System.UInt16": return($"({getExp(operandExp)})::int2");
case "System.UInt32": return($"({getExp(operandExp)})::int4");
case "System.UInt64": return($"({getExp(operandExp)})::int8");
case "System.Guid": return($"({getExp(operandExp)})::uuid");
}
}
break;
case ExpressionType.Call:
var callExp = exp as MethodCallExpression;
switch (callExp.Method.Name)
{
case "Parse":
case "TryParse":
switch (callExp.Method.DeclaringType.NullableTypeOrThis().ToString())
{
case "System.Boolean": return($"(({getExp(callExp.Arguments[0])})::varchar not in ('0','false','f','no'))");
case "System.Byte": return($"({getExp(callExp.Arguments[0])})::int2");
case "System.Char": return($"substr(({getExp(callExp.Arguments[0])})::char, 1, 1)");
case "System.DateTime": return($"({getExp(callExp.Arguments[0])})::timestamp");
case "System.Decimal": return($"({getExp(callExp.Arguments[0])})::numeric");
case "System.Double": return($"({getExp(callExp.Arguments[0])})::float8");
case "System.Int16": return($"({getExp(callExp.Arguments[0])})::int2");
case "System.Int32": return($"({getExp(callExp.Arguments[0])})::int4");
case "System.Int64": return($"({getExp(callExp.Arguments[0])})::int8");
case "System.SByte": return($"({getExp(callExp.Arguments[0])})::int2");
case "System.Single": return($"({getExp(callExp.Arguments[0])})::float4");
case "System.UInt16": return($"({getExp(callExp.Arguments[0])})::int2");
case "System.UInt32": return($"({getExp(callExp.Arguments[0])})::int4");
case "System.UInt64": return($"({getExp(callExp.Arguments[0])})::int8");
case "System.Guid": return($"({getExp(callExp.Arguments[0])})::uuid");
}
break;
case "NewGuid":
return(null);
case "Next":
if (callExp.Object?.Type == typeof(Random))
{
return("(random()*1000000000)::int4");
}
return(null);
case "NextDouble":
if (callExp.Object?.Type == typeof(Random))
{
return("random()");
}
return(null);
case "Random":
if (callExp.Method.DeclaringType.IsNumberType())
{
return("random()");
}
return(null);
case "ToString":
if (callExp.Object != null)
{
return(callExp.Arguments.Count == 0 ? $"({getExp(callExp.Object)})::text" : null);
}
return(null);
}
var objExp = callExp.Object;
var objType = objExp?.Type;
if (objType?.FullName == "System.Byte[]")
{
return(null);
}
var argIndex = 0;
if (objType == null && callExp.Method.DeclaringType == typeof(Enumerable))
{
objExp = callExp.Arguments.FirstOrDefault();
objType = objExp?.Type;
argIndex++;
if (objType == typeof(string))
{
switch (callExp.Method.Name)
{
case "First":
case "FirstOrDefault":
return($"substr({getExp(callExp.Arguments[0])}, 1, 1)");
}
}
}
if (objType == null)
{
objType = callExp.Method.DeclaringType;
}
if (objType != null || objType.IsArrayOrList())
{
string left = null;
if (objType == typeof(Dictionary <string, string>))
{
left = objExp == null ? null : getExp(objExp);
switch (callExp.Method.Name)
{
case "get_Item": return($"{left}->{getExp(callExp.Arguments[argIndex])}");
case "Contains":
var right = getExp(callExp.Arguments[argIndex]);
return($"({left} @> ({right}))");
case "ContainsKey": return($"({left} ? {getExp(callExp.Arguments[argIndex])})");
case "Concat": return($"({left} || {getExp(callExp.Arguments[argIndex])})");
case "GetLength":
case "GetLongLength":
case "Count": return($"case when {left} is null then 0 else array_length(akeys({left}),1) end");
case "Keys": return($"akeys({left})");
case "Values": return($"avals({left})");
}
}
switch (callExp.Method.Name)
{
case "Any":
left = objExp == null ? null : getExp(objExp);
if (left.StartsWith("(") || left.EndsWith(")"))
{
left = $"array[{left.TrimStart('(').TrimEnd(')')}]";
}
return($"(case when {left} is null then 0 else array_length({left},1) end > 0)");
case "Contains":
tsc.SetMapColumnTmp(null);
var args1 = getExp(callExp.Arguments[argIndex]);
var oldMapType = tsc.SetMapTypeReturnOld(tsc.mapTypeTmp);
//var oldDbParams = tsc.SetDbParamsReturnOld(null); #900 UseGenerateCommandParameterWithLambda(true) 子查询 bug
left = objExp == null ? null : getExp(objExp);
tsc.SetMapColumnTmp(null).SetMapTypeReturnOld(oldMapType);
//tsc.SetDbParamsReturnOld(oldDbParams);
//判断 in 或 array @> array
if (left.StartsWith("array[") || left.EndsWith("]"))
{
return($"({args1}) in ({left.Substring(6, left.Length - 7)})");
}
if (left.StartsWith("(") || left.EndsWith(")")) //在各大 Provider AdoProvider 中已约定，500元素分割, 3空格\r\n4空格
{
return($"(({args1}) in {left.Replace(", \r\n \r\n", $") \r\n OR ({args1}) in (")})");
}
if (args1.StartsWith("(") || args1.EndsWith(")"))
{
args1 = $"array[{args1.TrimStart('(').TrimEnd(')')}]";
}
args1 = $"array[{args1}]";
if (objExp != null)
{
var dbinfo = _common._orm.CodeFirst.GetDbInfo(objExp.Type);
if (dbinfo != null)
{
args1 = $"{args1}::{dbinfo.dbtype}";
}
}
return($"({left} @> {args1})");
case "Concat":
left = objExp == null ? null : getExp(objExp);
if (left.StartsWith("(") || left.EndsWith(")"))
{
left = $"array[{left.TrimStart('(').TrimEnd(')')}]";
}
var right2 = getExp(callExp.Arguments[argIndex]);
if (right2.StartsWith("(") || right2.EndsWith(")"))
{
right2 = $"array[{right2.TrimStart('(').TrimEnd(')')}]";
}
return($"({left} || {right2})");
case "GetLength":
case "GetLongLength":
case "Length":
case "Count":
left = objExp == null ? null : getExp(objExp);
if (left.StartsWith("(") || left.EndsWith(")"))
{
left = $"array[{left.TrimStart('(').TrimEnd(')')}]";
}
return($"case when {left} is null then 0 else array_length({left},1) end");
}
}
break;
case ExpressionType.MemberAccess:
var memExp = exp as MemberExpression;
var memParentExp = memExp.Expression?.Type;
if (memParentExp?.FullName == "System.Byte[]")
{
return(null);
}
if (memParentExp != null)
{
if (memParentExp.IsArray == true)
{
var left = getExp(memExp.Expression);
if (left.StartsWith("(") || left.EndsWith(")"))
{
left = $"array[{left.TrimStart('(').TrimEnd(')')}]";
}
switch (memExp.Member.Name)
{
case "Length":
case "Count": return($"case when {left} is null then 0 else array_length({left},1) end");
}
}
}
break;
case ExpressionType.NewArrayInit:
var arrExp = exp as NewArrayExpression;
var arrSb = new StringBuilder();
arrSb.Append("array[");
for (var a = 0; a < arrExp.Expressions.Count; a++)
{
if (a > 0)
{
arrSb.Append(",");
}
arrSb.Append(getExp(arrExp.Expressions[a]));
}
if (arrSb.Length == 1)
{
arrSb.Append("NULL");
}
return(arrSb.Append("]").ToString());
case ExpressionType.ListInit:
var listExp = exp as ListInitExpression;
var listSb = new StringBuilder();
listSb.Append("(");
for (var a = 0; a < listExp.Initializers.Count; a++)
{
if (listExp.Initializers[a].Arguments.Any() == false)
{
continue;
}
if (a > 0)
{
listSb.Append(",");
}
listSb.Append(getExp(listExp.Initializers[a].Arguments.FirstOrDefault()));
}
if (listSb.Length == 1)
{
listSb.Append("NULL");
}
return(listSb.Append(")").ToString());
case ExpressionType.New:
var newExp = exp as NewExpression;
if (typeof(IList).IsAssignableFrom(newExp.Type))
{
if (newExp.Arguments.Count == 0)
{
return("(NULL)");
}
if (typeof(IEnumerable).IsAssignableFrom(newExp.Arguments[0].Type) == false)
{
return("(NULL)");
}
return(getExp(newExp.Arguments[0]));
}
return(null);
}
return(null);
}
public override string ExpressionLambdaToSqlOther(Expression exp, ExpTSC tsc)
{
Func <Expression, string> getExp = exparg => ExpressionLambdaToSql(exparg, tsc);
switch (exp.NodeType)
{
case ExpressionType.ArrayLength:
var arrOper = (exp as UnaryExpression)?.Operand;
if (arrOper.Type == typeof(byte[]))
{
return($"octet_length({getExp(arrOper)})");
}
break;
case ExpressionType.Convert:
var operandExp = (exp as UnaryExpression)?.Operand;
var gentype = exp.Type.NullableTypeOrThis();
if (gentype != operandExp.Type.NullableTypeOrThis())
{
switch (gentype.ToString())
{
case "System.Boolean": return($"({getExp(operandExp)} not in ('0','false'))");
case "System.Byte": return($"cast({getExp(operandExp)} as unsigned)");
case "System.Char": return($"substr(cast({getExp(operandExp)} as char), 1, 1)");
case "System.DateTime": return(ExpressionConstDateTime(operandExp) ?? $"cast({getExp(operandExp)} as datetime)");
case "System.Decimal": return($"cast({getExp(operandExp)} as decimal(36,18))");
case "System.Double": return($"cast({getExp(operandExp)} as decimal(32,16))");
case "System.Int16":
case "System.Int32":
case "System.Int64":
case "System.SByte": return($"cast({getExp(operandExp)} as signed)");
case "System.Single": return($"cast({getExp(operandExp)} as decimal(14,7))");
case "System.String": return($"cast({getExp(operandExp)} as char)");
case "System.UInt16":
case "System.UInt32":
case "System.UInt64": return($"cast({getExp(operandExp)} as unsigned)");
case "System.Guid": return($"substr(cast({getExp(operandExp)} as char), 1, 36)");
}
}
break;
case ExpressionType.Call:
var callExp = exp as MethodCallExpression;
switch (callExp.Method.Name)
{
case "Parse":
case "TryParse":
switch (callExp.Method.DeclaringType.NullableTypeOrThis().ToString())
{
case "System.Boolean": return($"({getExp(callExp.Arguments[0])} not in ('0','false'))");
case "System.Byte": return($"cast({getExp(callExp.Arguments[0])} as unsigned)");
case "System.Char": return($"substr(cast({getExp(callExp.Arguments[0])} as char), 1, 1)");
case "System.DateTime": return(ExpressionConstDateTime(callExp.Arguments[0]) ?? $"cast({getExp(callExp.Arguments[0])} as datetime)");
case "System.Decimal": return($"cast({getExp(callExp.Arguments[0])} as decimal(36,18))");
case "System.Double": return($"cast({getExp(callExp.Arguments[0])} as decimal(32,16))");
case "System.Int16":
case "System.Int32":
case "System.Int64":
case "System.SByte": return($"cast({getExp(callExp.Arguments[0])} as signed)");
case "System.Single": return($"cast({getExp(callExp.Arguments[0])} as decimal(14,7))");
case "System.UInt16":
case "System.UInt32":
case "System.UInt64": return($"cast({getExp(callExp.Arguments[0])} as unsigned)");
case "System.Guid": return($"substr(cast({getExp(callExp.Arguments[0])} as char), 1, 36)");
}
return(null);
case "NewGuid":
return(null);
case "Next":
if (callExp.Object?.Type == typeof(Random))
{
return("cast(rand()*1000000000 as signed)");
}
return(null);
case "NextDouble":
if (callExp.Object?.Type == typeof(Random))
{
return("rand()");
}
return(null);
case "Random":
if (callExp.Method.DeclaringType.IsNumberType())
{
return("rand()");
}
return(null);
case "ToString":
if (callExp.Object != null)
{
return(callExp.Arguments.Count == 0 ? $"cast({getExp(callExp.Object)} as char)" : null);
}
return(null);
}
var objExp = callExp.Object;
var objType = objExp?.Type;
if (objType?.FullName == "System.Byte[]")
{
return(null);
}
var argIndex = 0;
if (objType == null && callExp.Method.DeclaringType == typeof(Enumerable))
{
objExp = callExp.Arguments.FirstOrDefault();
objType = objExp?.Type;
argIndex++;
if (objType == typeof(string))
{
switch (callExp.Method.Name)
{
case "First":
case "FirstOrDefault":
return($"substr({getExp(callExp.Arguments[0])}, 1, 1)");
}
}
}
if (objType == null)
{
objType = callExp.Method.DeclaringType;
}
if (objType != null || objType.IsArrayOrList())
{
if (argIndex >= callExp.Arguments.Count)
{
break;
}
tsc.SetMapColumnTmp(null);
var args1 = getExp(callExp.Arguments[argIndex]);
var oldMapType = tsc.SetMapTypeReturnOld(tsc.mapTypeTmp);
var oldDbParams = objExp?.NodeType == ExpressionType.MemberAccess ? tsc.SetDbParamsReturnOld(null) : null; //#900 UseGenerateCommandParameterWithLambda(true) 子查询 bug、以及 #1173 参数化 bug
tsc.isNotSetMapColumnTmp = true;
var left = objExp == null ? null : getExp(objExp);
tsc.isNotSetMapColumnTmp = false;
tsc.SetMapColumnTmp(null).SetMapTypeReturnOld(oldMapType);
if (oldDbParams != null)
{
tsc.SetDbParamsReturnOld(oldDbParams);
}
switch (callExp.Method.Name)
{
case "Contains":
//判断 in //在各大 Provider AdoProvider 中已约定，500元素分割, 3空格\r\n4空格
return($"(({args1}) in {left.Replace(", \r\n \r\n", $") \r\n OR ({args1}) in (")})");
}
}
break;
case ExpressionType.NewArrayInit:
var arrExp = exp as NewArrayExpression;
var arrSb = new StringBuilder();
arrSb.Append("(");
for (var a = 0; a < arrExp.Expressions.Count; a++)
{
if (a > 0)
{
arrSb.Append(",");
}
if (a % 500 == 499)
{
arrSb.Append(" \r\n \r\n"); //500元素分割, 3空格\r\n4空格
}
arrSb.Append(getExp(arrExp.Expressions[a]));
}
if (arrSb.Length == 1)
{
arrSb.Append("NULL");
}
return(arrSb.Append(")").ToString());
case ExpressionType.ListInit:
var listExp = exp as ListInitExpression;
var listSb = new StringBuilder();
listSb.Append("(");
for (var a = 0; a < listExp.Initializers.Count; a++)
{
if (listExp.Initializers[a].Arguments.Any() == false)
{
continue;
}
if (a > 0)
{
listSb.Append(",");
}
listSb.Append(getExp(listExp.Initializers[a].Arguments.FirstOrDefault()));
}
if (listSb.Length == 1)
{
listSb.Append("NULL");
}
return(listSb.Append(")").ToString());
case ExpressionType.New:
var newExp = exp as NewExpression;
if (typeof(IList).IsAssignableFrom(newExp.Type))
{
if (newExp.Arguments.Count == 0)
{
return("(NULL)");
}
if (typeof(IEnumerable).IsAssignableFrom(newExp.Arguments[0].Type) == false)
{
return("(NULL)");
}
return(getExp(newExp.Arguments[0]));
}
return(null);
}
return(null);
}
public override string ExpressionLambdaToSqlOther(Expression exp, ExpTSC tsc)
{
Func <Expression, string> getExp = exparg => ExpressionLambdaToSql(exparg, tsc);
switch (exp.NodeType)
{
case ExpressionType.Convert:
var operandExp = (exp as UnaryExpression)?.Operand;
var gentype = exp.Type.NullableTypeOrThis();
if (gentype != operandExp.Type.NullableTypeOrThis())
{
var retBefore = getExp(operandExp);
var retAfter = MsAccessUtils.GetCastSql(retBefore, gentype);
if (retBefore != retAfter)
{
return(retAfter);
}
}
break;
case ExpressionType.Call:
var callExp = exp as MethodCallExpression;
switch (callExp.Method.Name)
{
case "Parse":
case "TryParse":
var retBefore = getExp(callExp.Arguments[0]);
var retAfter = MsAccessUtils.GetCastSql(retBefore, callExp.Method.DeclaringType.NullableTypeOrThis());
if (retBefore != retAfter)
{
return(retAfter);
}
return(null);
case "NewGuid":
switch (callExp.Method.DeclaringType.NullableTypeOrThis().ToString())
{
case "System.Guid": return($"newid()");
}
return(null);
case "Next":
if (callExp.Object?.Type == typeof(Random))
{
return("rnd*1000000000000000");
}
return(null);
case "NextDouble":
if (callExp.Object?.Type == typeof(Random))
{
return("rnd");
}
return(null);
case "Random":
if (callExp.Method.DeclaringType.IsNumberType())
{
return("rnd");
}
return(null);
case "ToString":
if (callExp.Object != null)
{
return(callExp.Arguments.Count == 0 ? MsAccessUtils.GetCastSql(getExp(callExp.Object), typeof(string)) : null);
}
return(null);
}
var objExp = callExp.Object;
var objType = objExp?.Type;
if (objType?.FullName == "System.Byte[]")
{
return(null);
}
var argIndex = 0;
if (objType == null && callExp.Method.DeclaringType == typeof(Enumerable))
{
objExp = callExp.Arguments.FirstOrDefault();
objType = objExp?.Type;
argIndex++;
if (objType == typeof(string))
{
switch (callExp.Method.Name)
{
case "First":
case "FirstOrDefault":
return($"left({getExp(callExp.Arguments[0])}, 1)");
}
}
}
if (objType == null)
{
objType = callExp.Method.DeclaringType;
}
if (objType != null || objType.IsArrayOrList())
{
if (argIndex >= callExp.Arguments.Count)
{
break;
}
tsc.SetMapColumnTmp(null);
var args1 = getExp(callExp.Arguments[argIndex]);
var oldMapType = tsc.SetMapTypeReturnOld(tsc.mapTypeTmp);
var oldDbParams = tsc.SetDbParamsReturnOld(null);
var left = objExp == null ? null : getExp(objExp);
tsc.SetMapColumnTmp(null).SetMapTypeReturnOld(oldMapType);
tsc.SetDbParamsReturnOld(oldDbParams);
switch (callExp.Method.Name)
{
case "Contains":
//判断 in //在各大 Provider AdoProvider 中已约定，500元素分割, 3空格\r\n4空格
return($"(({args1}) in {left.Replace(", \r\n \r\n", $") \r\n OR ({args1}) in (")})");
}
}
break;
case ExpressionType.NewArrayInit:
var arrExp = exp as NewArrayExpression;
var arrSb = new StringBuilder();
arrSb.Append("(");
for (var a = 0; a < arrExp.Expressions.Count; a++)
{
if (a > 0)
{
arrSb.Append(",");
}
arrSb.Append(getExp(arrExp.Expressions[a]));
}
if (arrSb.Length == 1)
{
arrSb.Append("NULL");
}
return(arrSb.Append(")").ToString());
case ExpressionType.ListInit:
var listExp = exp as ListInitExpression;
var listSb = new StringBuilder();
listSb.Append("(");
for (var a = 0; a < listExp.Initializers.Count; a++)
{
if (listExp.Initializers[a].Arguments.Any() == false)
{
continue;
}
if (a > 0)
{
listSb.Append(",");
}
listSb.Append(getExp(listExp.Initializers[a].Arguments.FirstOrDefault()));
}
if (listSb.Length == 1)
{
listSb.Append("NULL");
}
return(listSb.Append(")").ToString());
case ExpressionType.New:
var newExp = exp as NewExpression;
if (typeof(IList).IsAssignableFrom(newExp.Type))
{
if (newExp.Arguments.Count == 0)
{
return("(NULL)");
}
if (typeof(IEnumerable).IsAssignableFrom(newExp.Arguments[0].Type) == false)
{
return("(NULL)");
}
return(getExp(newExp.Arguments[0]));
}
return(null);
}
return(null);
}
public override string ExpressionLambdaToSqlOther(Expression exp, ExpTSC tsc)
{
Func <Expression, string> getExp = exparg => ExpressionLambdaToSql(exparg, tsc);
switch (exp.NodeType)
{
case ExpressionType.Convert:
var operandExp = (exp as UnaryExpression)?.Operand;
var gentype = exp.Type.NullableTypeOrThis();
if (gentype != operandExp.Type.NullableTypeOrThis())
{
switch (exp.Type.NullableTypeOrThis().ToString())
{
case "System.Boolean": return($"(({getExp(operandExp)})::varchar not in ('0','false','f','no'))");
case "System.Byte": return($"({getExp(operandExp)})::int2");
case "System.Char": return($"substr(({getExp(operandExp)})::char, 1, 1)");
case "System.DateTime": return($"({getExp(operandExp)})::timestamp");
case "System.Decimal": return($"({getExp(operandExp)})::numeric");
case "System.Double": return($"({getExp(operandExp)})::float8");
case "System.Int16": return($"({getExp(operandExp)})::int2");
case "System.Int32": return($"({getExp(operandExp)})::int4");
case "System.Int64": return($"({getExp(operandExp)})::int8");
case "System.SByte": return($"({getExp(operandExp)})::int2");
case "System.Single": return($"({getExp(operandExp)})::float4");
case "System.String": return($"({getExp(operandExp)})::varchar");
case "System.UInt16": return($"({getExp(operandExp)})::int2");
case "System.UInt32": return($"({getExp(operandExp)})::int4");
case "System.UInt64": return($"({getExp(operandExp)})::int8");
case "System.Guid": return($"({getExp(operandExp)})::uuid");
}
}
break;
case ExpressionType.ArrayLength:
var arrOperExp = getExp((exp as UnaryExpression).Operand);
if (arrOperExp.StartsWith("(") || arrOperExp.EndsWith(")"))
{
return($"array_length(array[{arrOperExp.TrimStart('(').TrimEnd(')')}],1)");
}
return($"case when {arrOperExp} is null then 0 else array_length({arrOperExp},1) end");
case ExpressionType.Call:
var callExp = exp as MethodCallExpression;
switch (callExp.Method.Name)
{
case "Parse":
case "TryParse":
switch (callExp.Method.DeclaringType.NullableTypeOrThis().ToString())
{
case "System.Boolean": return($"(({getExp(callExp.Arguments[0])})::varchar not in ('0','false','f','no'))");
case "System.Byte": return($"({getExp(callExp.Arguments[0])})::int2");
case "System.Char": return($"substr(({getExp(callExp.Arguments[0])})::char, 1, 1)");
case "System.DateTime": return($"({getExp(callExp.Arguments[0])})::timestamp");
case "System.Decimal": return($"({getExp(callExp.Arguments[0])})::numeric");
case "System.Double": return($"({getExp(callExp.Arguments[0])})::float8");
case "System.Int16": return($"({getExp(callExp.Arguments[0])})::int2");
case "System.Int32": return($"({getExp(callExp.Arguments[0])})::int4");
case "System.Int64": return($"({getExp(callExp.Arguments[0])})::int8");
case "System.SByte": return($"({getExp(callExp.Arguments[0])})::int2");
case "System.Single": return($"({getExp(callExp.Arguments[0])})::float4");
case "System.UInt16": return($"({getExp(callExp.Arguments[0])})::int2");
case "System.UInt32": return($"({getExp(callExp.Arguments[0])})::int4");
case "System.UInt64": return($"({getExp(callExp.Arguments[0])})::int8");
case "System.Guid": return($"({getExp(callExp.Arguments[0])})::uuid");
}
break;
case "NewGuid":
break;
case "Next":
if (callExp.Object?.Type == typeof(Random))
{
return("(random()*1000000000)::int4");
}
break;
case "NextDouble":
if (callExp.Object?.Type == typeof(Random))
{
return("random()");
}
break;
case "Random":
if (callExp.Method.DeclaringType.IsNumberType())
{
return("random()");
}
break;
case "ToString":
if (callExp.Object != null)
{
return(callExp.Arguments.Count == 0 ? $"({getExp(callExp.Object)})::varchar" : null);
}
break;
}
var objExp = callExp.Object;
var objType = objExp?.Type;
if (objType?.FullName == "System.Byte[]")
{
return(null);
}
var argIndex = 0;
if (objType == null && callExp.Method.DeclaringType == typeof(Enumerable))
{
objExp = callExp.Arguments.FirstOrDefault();
objType = objExp?.Type;
argIndex++;
}
if (objType == null)
{
objType = callExp.Method.DeclaringType;
}
if (objType != null || objType.IsArrayOrList())
{
string left = null;
switch (objType.FullName)
{
case "Newtonsoft.Json.Linq.JToken":
case "Newtonsoft.Json.Linq.JObject":
case "Newtonsoft.Json.Linq.JArray":
left = objExp == null ? null : getExp(objExp);
switch (callExp.Method.Name)
{
case "Any": return($"(jsonb_array_length(coalesce({left},'[]')) > 0)");
case "Contains":
var json = getExp(callExp.Arguments[argIndex]);
if (objType == typeof(JArray))
{
return($"(coalesce({left},'[]') ? ({json})::varchar)");
}
if (json.StartsWith("'") && json.EndsWith("'"))
{
return($"(coalesce({left},'{{}}') @> {_common.FormatSql("{0}", JToken.Parse(json.Trim('\'')))})");
}
return($"(coalesce({left},'{{}}') @> ({json})::jsonb)");
case "ContainsKey": return($"(coalesce({left},'{{}}') ? {getExp(callExp.Arguments[argIndex])})");
case "Concat":
var right2 = getExp(callExp.Arguments[argIndex]);
return($"(coalesce({left},'{{}}') || {right2})");
case "LongCount":
case "Count": return($"jsonb_array_length(coalesce({left},'[]'))");
case "Parse":
var json2 = getExp(callExp.Arguments[argIndex]);
if (json2.StartsWith("'") && json2.EndsWith("'"))
{
return(_common.FormatSql("{0}", JToken.Parse(json2.Trim('\''))));
}
return($"({json2})::jsonb");
}
break;
}
if (objType.FullName == typeof(Dictionary <string, string>).FullName)
{
left = objExp == null ? null : getExp(objExp);
switch (callExp.Method.Name)
{
case "Contains":
var right = getExp(callExp.Arguments[argIndex]);
return($"({left} @> ({right}))");
case "ContainsKey": return($"({left} ? {getExp(callExp.Arguments[argIndex])})");
case "Concat": return($"({left} || {getExp(callExp.Arguments[argIndex])})");
case "GetLength":
case "GetLongLength":
case "Count": return($"case when {left} is null then 0 else array_length(akeys({left}),1) end");
case "Keys": return($"akeys({left})");
case "Values": return($"avals({left})");
}
}
switch (callExp.Method.Name)
{
case "Any":
left = objExp == null ? null : getExp(objExp);
if (left.StartsWith("(") || left.EndsWith(")"))
{
left = $"array[{left.TrimStart('(').TrimEnd(')')}]";
}
return($"(case when {left} is null then 0 else array_length({left},1) end > 0)");
case "Contains":
tsc?.SetMapTypeTmp(null);
var args1 = getExp(callExp.Arguments[argIndex]);
var oldMapType = tsc?.SetMapTypeReturnOld(tsc?.mapTypeTmp);
left = objExp == null ? null : getExp(objExp);
tsc.SetMapTypeReturnOld(oldMapType);
//判断 in 或 array @> array
if (left.StartsWith("array[") || left.EndsWith("]"))
{
return($"{args1} in ({left.Substring(6, left.Length - 7)})");
}
if (left.StartsWith("(") || left.EndsWith(")"))
{
return($"{args1} in {left}");
}
if (args1.StartsWith("(") || args1.EndsWith(")"))
{
args1 = $"array[{args1.TrimStart('(').TrimEnd(')')}]";
}
args1 = $"array[{args1}]";
if (objExp != null)
{
var dbinfo = _common._orm.CodeFirst.GetDbInfo(objExp.Type);
if (dbinfo.HasValue)
{
args1 = $"{args1}::{dbinfo.Value.dbtype}";
}
}
return($"({left} @> {args1})");
case "Concat":
left = objExp == null ? null : getExp(objExp);
if (left.StartsWith("(") || left.EndsWith(")"))
{
left = $"array[{left.TrimStart('(').TrimEnd(')')}]";
}
var right2 = getExp(callExp.Arguments[argIndex]);
if (right2.StartsWith("(") || right2.EndsWith(")"))
{
right2 = $"array[{right2.TrimStart('(').TrimEnd(')')}]";
}
return($"({left} || {right2})");
case "GetLength":
case "GetLongLength":
case "Length":
case "Count":
left = objExp == null ? null : getExp(objExp);
if (left.StartsWith("(") || left.EndsWith(")"))
{
left = $"array[{left.TrimStart('(').TrimEnd(')')}]";
}
return($"case when {left} is null then 0 else array_length({left},1) end");
}
}
break;
case ExpressionType.MemberAccess:
var memExp = exp as MemberExpression;
var memParentExp = memExp.Expression?.Type;
if (memParentExp?.FullName == "System.Byte[]")
{
return(null);
}
if (memParentExp != null)
{
if (memParentExp.IsArray == true)
{
var left = getExp(memExp.Expression);
if (left.StartsWith("(") || left.EndsWith(")"))
{
left = $"array[{left.TrimStart('(').TrimEnd(')')}]";
}
switch (memExp.Member.Name)
{
case "Length":
case "Count": return($"case when {left} is null then 0 else array_length({left},1) end");
}
}
switch (memParentExp.FullName)
{
case "Newtonsoft.Json.Linq.JToken":
case "Newtonsoft.Json.Linq.JObject":
case "Newtonsoft.Json.Linq.JArray":
var left = getExp(memExp.Expression);
switch (memExp.Member.Name)
{
case "Count": return($"jsonb_array_length(coalesce({left},'[]'))");
}
break;
}
if (memParentExp.FullName == typeof(Dictionary <string, string>).FullName)
{
var left = getExp(memExp.Expression);
switch (memExp.Member.Name)
{
case "Count": return($"case when {left} is null then 0 else array_length(akeys({left}),1) end");
case "Keys": return($"akeys({left})");
case "Values": return($"avals({left})");
}
}
}
break;
case ExpressionType.NewArrayInit:
var arrExp = exp as NewArrayExpression;
var arrSb = new StringBuilder();
arrSb.Append("array[");
for (var a = 0; a < arrExp.Expressions.Count; a++)
{
if (a > 0)
{
arrSb.Append(",");
}
arrSb.Append(getExp(arrExp.Expressions[a]));
}
if (arrSb.Length == 1)
{
arrSb.Append("NULL");
}
return(arrSb.Append("]").ToString());
case ExpressionType.ListInit:
var listExp = exp as ListInitExpression;
var listSb = new StringBuilder();
listSb.Append("(");
for (var a = 0; a < listExp.Initializers.Count; a++)
{
if (listExp.Initializers[a].Arguments.Any() == false)
{
continue;
}
if (a > 0)
{
listSb.Append(",");
}
listSb.Append(getExp(listExp.Initializers[a].Arguments.FirstOrDefault()));
}
if (listSb.Length == 1)
{
listSb.Append("NULL");
}
return(listSb.Append(")").ToString());
case ExpressionType.New:
var newExp = exp as NewExpression;
if (typeof(IList).IsAssignableFrom(newExp.Type))
{
if (newExp.Arguments.Count == 0)
{
return("(NULL)");
}
if (typeof(IEnumerable).IsAssignableFrom(newExp.Arguments[0].Type) == false)
{
return("(NULL)");
}
return(getExp(newExp.Arguments[0]));
}
return(null);
}
return(null);
}