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); }