internal void ExpressionJoinLambda(List <SelectTableInfo> _tables, SelectTableInfoType tbtype, Expression exp, Func <Expression[], string> getSelectGroupingMapString)
{
var tbidx = _tables.Count;
var filter = ExpressionLambdaToSql(exp, _tables, null, getSelectGroupingMapString, tbtype, true);
switch (filter)
{
case "1":
case "'t'": filter = "1=1"; break;
case "0":
case "'f'": filter = "1=2"; break;
default: break;
}
if (_tables.Count > tbidx)
{
_tables[tbidx].Type = tbtype;
_tables[tbidx].On = filter;
for (var a = tbidx + 1; a < _tables.Count; a++)
{
_tables[a].Type = SelectTableInfoType.From;
}
}
else
{
var find = _tables.Where((a, c) => c > 0 && (a.Type == tbtype || a.Type == SelectTableInfoType.From) && string.IsNullOrEmpty(a.On)).LastOrDefault();
if (find != null)
{
find.Type = tbtype;
find.On = filter;
}
}
}
internal override string ExpressionLambdaToSqlCallString(MethodCallExpression exp, List <SelectTableInfo> _tables, List <SelectColumnInfo> _selectColumnMap, Func <Expression[], string> getSelectGroupingMapString, SelectTableInfoType tbtype, bool isQuoteName)
{
Func <Expression, string> getExp = exparg => ExpressionLambdaToSql(exparg, _tables, _selectColumnMap, getSelectGroupingMapString, tbtype, isQuoteName);
if (exp.Object == null)
{
switch (exp.Method.Name)
{
case "IsNullOrEmpty":
var arg1 = getExp(exp.Arguments[0]);
return($"({arg1} is null or {arg1} = '')");
}
}
else
{
var left = getExp(exp.Object);
switch (exp.Method.Name)
{
case "StartsWith":
case "EndsWith":
case "Contains":
var args0Value = getExp(exp.Arguments[0]);
if (args0Value == "NULL")
{
return($"({left}) IS NULL");
}
if (exp.Method.Name == "StartsWith")
{
return($"({left}) LIKE {(args0Value.EndsWith("'") ? args0Value.Insert(args0Value.Length - 1, "%") : $"(cast({args0Value} as nvarchar)+'%')")}");
}
if (exp.Method.Name == "EndsWith")
{
return($"({left}) LIKE {(args0Value.StartsWith("'") ? args0Value.Insert(1, "%") : $"('%'+cast({args0Value} as nvarchar))")}");
}
if (args0Value.StartsWith("'") && args0Value.EndsWith("'"))
{
return($"({left}) LIKE {args0Value.Insert(1, "%").Insert(args0Value.Length, "%")}");
}
return($"({left}) LIKE ('%'+cast({args0Value} as nvarchar)+'%')");
internal override string ExpressionLambdaToSqlMemberAccessTimeSpan(MemberExpression exp, List <SelectTableInfo> _tables, List <SelectColumnInfo> _selectColumnMap, Func <Expression[], string> getSelectGroupingMapString, SelectTableInfoType tbtype, bool isQuoteName)
{
if (exp.Expression == null)
{
switch (exp.Member.Name)
{
case "Zero": return("0");
case "MinValue": return("-922337203685477580"); //微秒 Ticks / 10
case "MaxValue": return("922337203685477580");
}
return(null);
}
var left = ExpressionLambdaToSql(exp.Expression, _tables, _selectColumnMap, getSelectGroupingMapString, tbtype, isQuoteName);
switch (exp.Member.Name)
{
case "Days": return($"floor(({left})/{60 * 60 * 24})");
case "Hours": return($"floor(({left})/{60 * 60}%24)");
case "Milliseconds": return($"(cast({left} as bigint)*1000)");
case "Minutes": return($"floor(({left})/60%60)");
case "Seconds": return($"(({left})%60)");
case "Ticks": return($"(cast({left} as bigint)*10000000)");
case "TotalDays": return($"(({left})/{60 * 60 * 24})");
case "TotalHours": return($"(({left})/{60 * 60})");
case "TotalMilliseconds": return($"(cast({left} as bigint)*1000)");
case "TotalMinutes": return($"(({left})/60)");
case "TotalSeconds": return($"({left})");
}
return(null);
}
internal override string ExpressionLambdaToSqlOther(Expression exp, List <SelectTableInfo> _tables, List <SelectColumnInfo> _selectColumnMap, Func <Expression[], string> getSelectGroupingMapString, SelectTableInfoType tbtype, bool isQuoteName)
{
Func <Expression, string> getExp = exparg => ExpressionLambdaToSql(exparg, _tables, _selectColumnMap, getSelectGroupingMapString, tbtype, isQuoteName);
switch (exp.NodeType)
{
case ExpressionType.Call:
var callExp = exp as MethodCallExpression;
var objExp = callExp.Object;
var objType = objExp?.Type;
if (objType?.FullName == "System.Byte[]")
{
return(null);
}
var argIndex = 0;
if (objType == null && callExp.Method.DeclaringType.FullName == typeof(Enumerable).FullName)
{
objExp = callExp.Arguments.FirstOrDefault();
objType = objExp?.Type;
argIndex++;
}
if (objType == null)
{
objType = callExp.Method.DeclaringType;
}
if (objType != null)
{
var left = objExp == null ? null : getExp(objExp);
if (objType.IsArray == true)
{
switch (callExp.Method.Name)
{
case "Contains":
//判断 in
return($"({getExp(callExp.Arguments[argIndex])}) 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]));
}
return(arrSb.Append(")").ToString());
}
return(null);
}
internal override string ExpressionLambdaToSqlCallString(MethodCallExpression exp, List <SelectTableInfo> _tables, List <SelectColumnInfo> _selectColumnMap, Func <Expression[], string> getSelectGroupingMapString, SelectTableInfoType tbtype, bool isQuoteName)
{
Func <Expression, string> getExp = exparg => ExpressionLambdaToSql(exparg, _tables, _selectColumnMap, getSelectGroupingMapString, tbtype, isQuoteName);
if (exp.Object == null)
{
switch (exp.Method.Name)
{
case "IsNullOrEmpty":
var arg1 = getExp(exp.Arguments[0]);
return($"({arg1} is null or {arg1} = '')");
}
}
else
{
var left = getExp(exp.Object);
switch (exp.Method.Name)
{
case "StartsWith":
case "EndsWith":
case "Contains":
var args0Value = getExp(exp.Arguments[0]);
if (args0Value == "NULL")
{
return($"({left}) IS NULL");
}
var likeOpt = "LIKE";
if (exp.Arguments.Count > 1)
{
if (exp.Arguments[1].Type == typeof(bool) ||
exp.Arguments[1].Type == typeof(StringComparison) && getExp(exp.Arguments[0]).Contains("IgnoreCase"))
{
likeOpt = "ILIKE";
}
}
if (exp.Method.Name == "StartsWith")
{
return($"({left}) {likeOpt} {(args0Value.EndsWith("'") ? args0Value.Insert(args0Value.Length - 1, "%") : $"(({args0Value})::varchar || '%')")}");
}
if (exp.Method.Name == "EndsWith")
{
return($"({left}) {likeOpt} {(args0Value.StartsWith("'") ? args0Value.Insert(1, "%") : $"('%' || ({args0Value})::varchar)")}");
}
if (args0Value.StartsWith("'") && args0Value.EndsWith("'"))
{
return($"({left}) {likeOpt} {args0Value.Insert(1, "%").Insert(args0Value.Length, "%")}");
}
return($"({left}) {likeOpt} ('%' || ({args0Value})::varchar || '%')");
internal override string ExpressionLambdaToSqlMemberAccessDateTime(MemberExpression exp, List <SelectTableInfo> _tables, List <SelectColumnInfo> _selectColumnMap, Func <Expression[], string> getSelectGroupingMapString, SelectTableInfoType tbtype, bool isQuoteName)
{
if (exp.Expression == null)
{
switch (exp.Member.Name)
{
case "Now": return("current_timestamp");
case "UtcNow": return("(current_timestamp at time zone 'UTC')");
case "Today": return("current_date");
case "MinValue": return("'0001/1/1 0:00:00'::timestamp");
case "MaxValue": return("'9999/12/31 23:59:59'::timestamp");
}
return(null);
}
var left = ExpressionLambdaToSql(exp.Expression, _tables, _selectColumnMap, getSelectGroupingMapString, tbtype, isQuoteName);
switch (exp.Member.Name)
{
case "Date": return($"({left})::date");
case "TimeOfDay": return($"(extract(epoch from ({left})::time)*1000000)");
case "DayOfWeek": return($"extract(dow from ({left})::timestamp)");
case "Day": return($"extract(day from ({left})::timestamp)");
case "DayOfYear": return($"extract(doy from ({left})::timestamp)");
case "Month": return($"extract(month from ({left})::timestamp)");
case "Year": return($"extract(year from ({left})::timestamp)");
case "Hour": return($"extract(hour from ({left})::timestamp)");
case "Minute": return($"extract(minute from ({left})::timestamp)");
case "Second": return($"extract(second from ({left})::timestamp)");
case "Millisecond": return($"(extract(milliseconds from ({left})::timestamp)-extract(second from ({left})::timestamp)*1000)");
case "Ticks": return($"(extract(epoch from ({left})::timestamp)*10000000+621355968000000000)");
}
return(null);
}
internal override string ExpressionLambdaToSqlOther(Expression exp, List <SelectTableInfo> _tables, List <SelectColumnInfo> _selectColumnMap, Func <Expression[], string> getSelectGroupingMapString, SelectTableInfoType tbtype, bool isQuoteName)
{
Func <Expression, string> getExp = exparg => ExpressionLambdaToSql(exparg, _tables, _selectColumnMap, getSelectGroupingMapString, tbtype, isQuoteName);
switch (exp.NodeType)
{
case ExpressionType.Convert:
var operandExp = (exp as UnaryExpression)?.Operand;
var gentype = exp.Type.NullableTypeOrThis();
if (gentype != exp.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($"({getExp(callExp.Object)})::varchar");
}
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)
{
var left = objExp == null ? null : getExp(objExp);
switch (objType.FullName)
{
case "Newtonsoft.Json.Linq.JToken":
case "Newtonsoft.Json.Linq.JObject":
case "Newtonsoft.Json.Linq.JArray":
switch (callExp.Method.Name)
{
case "Any": return($"(jsonb_array_length(coalesce({left},'[]')) > 0)");
case "Contains":
var json = getExp(callExp.Arguments[argIndex]);
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)
{
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})");
}
}
if (objType.IsArray || typeof(IList).IsAssignableFrom(callExp.Method.DeclaringType))
{
switch (callExp.Method.Name)
{
case "Any":
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":
//判断 in 或 array @> array
var right1 = getExp(callExp.Arguments[argIndex]);
if (left.StartsWith("array[") || left.EndsWith("]"))
{
return($"{right1} in ({left.Substring(6, left.Length - 7)})");
}
if (left.StartsWith("(") || left.EndsWith(")"))
{
return($"{right1} in {left}");
}
if (right1.StartsWith("(") || right1.EndsWith(")"))
{
right1 = $"array[{right1.TrimStart('(').TrimEnd(')')}]";
}
return($"({left} @> array[{right1}])");
case "Concat":
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":
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);
}
internal override string ExpressionLambdaToSqlMemberAccessDateTime(MemberExpression exp, List <SelectTableInfo> _tables, List <SelectColumnInfo> _selectColumnMap, Func <Expression[], string> getSelectGroupingMapString, SelectTableInfoType tbtype, bool isQuoteName)
{
if (exp.Expression == null)
{
switch (exp.Member.Name)
{
case "Now": return("systimestamp");
case "UtcNow": return("sys_extract_utc(systimestamp)");
case "Today": return("trunc(systimestamp)");
case "MinValue": return("to_timestamp('0001-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS.FF6')");
case "MaxValue": return("to_timestamp('9999-12-31 23:59:59','YYYY-MM-DD HH24:MI:SS.FF6')");
}
return(null);
}
var left = ExpressionLambdaToSql(exp.Expression, _tables, _selectColumnMap, getSelectGroupingMapString, tbtype, isQuoteName);
switch (exp.Member.Name)
{
case "Date": return($"trunc({left})");
case "TimeOfDay": return($"({left}-trunc({left}))");
case "DayOfWeek": return($"case when to_char({left})='7' then 0 else cast(to_char({left}) as number) end");
case "Day": return($"cast(to_char({left},'DD') as number)");
case "DayOfYear": return($"cast(to_char({left},'DDD') as number)");
case "Month": return($"cast(to_char({left},'MM') as number)");
case "Year": return($"cast(to_char({left},'YYYY') as number)");
case "Hour": return($"cast(to_char({left},'HH24') as number)");
case "Minute": return($"cast(to_char({left},'MI') as number)");
case "Second": return($"cast(to_char({left},'SS') as number)");
case "Millisecond": return($"cast(to_char({left},'FF3') as number)");
case "Ticks": return($"cast(to_char({left},'FF7') as number)");
}
return(null);
}
internal override string ExpressionLambdaToSqlOther(Expression exp, List <SelectTableInfo> _tables, List <SelectColumnInfo> _selectColumnMap, Func <Expression[], string> getSelectGroupingMapString, SelectTableInfoType tbtype, bool isQuoteName)
{
Func <Expression, string> getExp = exparg => ExpressionLambdaToSql(exparg, _tables, _selectColumnMap, getSelectGroupingMapString, tbtype, isQuoteName);
switch (exp.NodeType)
{
case ExpressionType.Convert:
var operandExp = (exp as UnaryExpression)?.Operand;
var gentype = exp.Type.NullableTypeOrThis();
if (gentype != exp.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 unsigned)");
case "System.Char": return($"substr(cast({getExp(operandExp)} as char), 1, 1)");
case "System.DateTime": return($"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($"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)");
}
break;
case "NewGuid":
break;
case "Next":
if (callExp.Object?.Type == typeof(Random))
{
return("cast(rand()*1000000000 as signed)");
}
break;
case "NextDouble":
if (callExp.Object?.Type == typeof(Random))
{
return("rand()");
}
break;
case "Random":
if (callExp.Method.DeclaringType.IsNumberType())
{
return("rand()");
}
break;
case "ToString":
if (callExp.Object != null)
{
return($"cast({getExp(callExp.Object)} as char)");
}
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)
{
var left = objExp == null ? null : getExp(objExp);
if (objType.IsArray || typeof(IList).IsAssignableFrom(callExp.Method.DeclaringType))
{
switch (callExp.Method.Name)
{
case "Contains":
//判断 in
return($"({getExp(callExp.Arguments[argIndex])}) 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);
}
internal override string ExpressionLambdaToSqlMemberAccessDateTime(MemberExpression exp, List <SelectTableInfo> _tables, List <SelectColumnInfo> _selectColumnMap, Func <Expression[], string> getSelectGroupingMapString, SelectTableInfoType tbtype, bool isQuoteName)
{
if (exp.Expression == null)
{
switch (exp.Member.Name)
{
case "Now": return("datetime(current_timestamp,'localtime')");
case "UtcNow": return("current_timestamp");
case "Today": return("date(current_timestamp,'localtime')");
case "MinValue": return("datetime('0001-01-01 00:00:00.000')");
case "MaxValue": return("datetime('9999-12-31 23:59:59.999')");
}
return(null);
}
var left = ExpressionLambdaToSql(exp.Expression, _tables, _selectColumnMap, getSelectGroupingMapString, tbtype, isQuoteName);
switch (exp.Member.Name)
{
case "Date": return($"date({left})");
case "TimeOfDay": return($"strftime('%s',{left})");
case "DayOfWeek": return($"strftime('%w',{left})");
case "Day": return($"strftime('%d',{left})");
case "DayOfYear": return($"strftime('%j',{left})");
case "Month": return($"strftime('%m',{left})");
case "Year": return($"strftime('%Y',{left})");
case "Hour": return($"strftime('%H',{left})");
case "Minute": return($"strftime('%M',{left})");
case "Second": return($"strftime('%S',{left})");
case "Millisecond": return($"(strftime('%f',{left})-strftime('%S',{left}))");
case "Ticks": return($"(strftime('%s',{left})*10000000+621355968000000000)");
}
return(null);
}
internal override string ExpressionLambdaToSqlMemberAccessTimeSpan(MemberExpression exp, List <SelectTableInfo> _tables, List <SelectColumnInfo> _selectColumnMap, Func <Expression[], string> getSelectGroupingMapString, SelectTableInfoType tbtype, bool isQuoteName)
{
if (exp.Expression == null)
{
switch (exp.Member.Name)
{
case "Zero": return("numtodsinterval(0,'second')");
case "MinValue": return("numtodsinterval(-233720368.5477580,'second')");
case "MaxValue": return("numtodsinterval(233720368.5477580,'second')");
}
return(null);
}
var left = ExpressionLambdaToSql(exp.Expression, _tables, _selectColumnMap, getSelectGroupingMapString, tbtype, isQuoteName);
switch (exp.Member.Name)
{
case "Days": return($"extract(day from {left})");
case "Hours": return($"extract(hour from {left})");
case "Milliseconds": return($"cast(substr(extract(second from {left})-floor(extract(second from {left})),2,3) as number)");
case "Minutes": return($"extract(minute from {left})");
case "Seconds": return($"floor(extract(second from {left}))");
case "Ticks": return($"(extract(day from {left})*86400+extract(hour from {left})*3600+extract(minute from {left})*60+extract(second from {left}))*10000000");
case "TotalDays": return($"extract(day from {left})");
case "TotalHours": return($"(extract(day from {left})*24+extract(hour from {left}))");
case "TotalMilliseconds": return($"(extract(day from {left})*86400+extract(hour from {left})*3600+extract(minute from {left})*60+extract(second from {left}))*1000");
case "TotalMinutes": return($"(extract(day from {left})*1440+extract(hour from {left})*60+extract(minute from {left}))");
case "TotalSeconds": return($"(extract(day from {left})*86400+extract(hour from {left})*3600+extract(minute from {left})*60+extract(second from {left}))");
}
return(null);
}
internal override string ExpressionLambdaToSqlOther(Expression exp, List <SelectTableInfo> _tables, List <SelectColumnInfo> _selectColumnMap, Func <Expression[], string> getSelectGroupingMapString, SelectTableInfoType tbtype, bool isQuoteName)
{
Func <Expression, string> getExp = exparg => ExpressionLambdaToSql(exparg, _tables, _selectColumnMap, getSelectGroupingMapString, tbtype, isQuoteName);
switch (exp.NodeType)
{
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;
var objExp = callExp.Object;
var objType = objExp?.Type;
if (objType?.FullName == "System.Byte[]")
{
return(null);
}
var argIndex = 0;
if (objType == null && callExp.Method.DeclaringType.FullName == typeof(Enumerable).FullName)
{
objExp = callExp.Arguments.FirstOrDefault();
objType = objExp?.Type;
argIndex++;
}
if (objType == null)
{
objType = callExp.Method.DeclaringType;
}
if (objType != null)
{
var left = objExp == null ? null : getExp(objExp);
if (objType.IsArray == true)
{
switch (callExp.Method.Name)
{
case "Any":
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":
//判断 in 或 array @> array
var right1 = getExp(callExp.Arguments[argIndex]);
if (left.StartsWith("array[") || left.EndsWith("]"))
{
return($"{right1} in ({left.Substring(6, left.Length - 7)})");
}
if (left.StartsWith("(") || left.EndsWith(")"))
{
return($"{right1} in {left}");
}
if (right1.StartsWith("(") || right1.EndsWith(")"))
{
right1 = $"array[{right1.TrimStart('(').TrimEnd(')')}]";
}
return($"({left} @> array[{right1}])");
case "Concat":
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":
if (left.StartsWith("(") || left.EndsWith(")"))
{
left = $"array[{left.TrimStart('(').TrimEnd(')')}]";
}
return($"case when {left} is null then 0 else array_length({left},1) end");
}
}
switch (objType.FullName)
{
case "Newtonsoft.Json.Linq.JToken":
case "Newtonsoft.Json.Linq.JObject":
case "Newtonsoft.Json.Linq.JArray":
switch (callExp.Method.Name)
{
case "Any": return($"(jsonb_array_length(coalesce({left},'[]')) > 0)");
case "Contains":
var json = getExp(callExp.Arguments[argIndex]);
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)
{
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})");
}
}
}
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]));
}
return(arrSb.Append("]").ToString());
}
return(null);
}
internal abstract string ExpressionLambdaToSqlOther(Expression exp, List <SelectTableInfo> _tables, List <SelectColumnInfo> _selectColumnMap, Func <Expression[], string> getSelectGroupingMapString, SelectTableInfoType tbtype, bool isQuoteName);
internal string ExpressionLambdaToSql(Expression exp, List <SelectTableInfo> _tables, List <SelectColumnInfo> _selectColumnMap, Func <Expression[], string> getSelectGroupingMapString, SelectTableInfoType tbtype, bool isQuoteName)
{
switch (exp.NodeType)
{
case ExpressionType.Not: return($"not({ExpressionLambdaToSql((exp as UnaryExpression)?.Operand, _tables, _selectColumnMap, getSelectGroupingMapString, tbtype, isQuoteName)})");
case ExpressionType.Quote: return(ExpressionLambdaToSql((exp as UnaryExpression)?.Operand, _tables, _selectColumnMap, getSelectGroupingMapString, tbtype, isQuoteName));
case ExpressionType.Lambda: return(ExpressionLambdaToSql((exp as LambdaExpression)?.Body, _tables, _selectColumnMap, getSelectGroupingMapString, tbtype, isQuoteName));
case ExpressionType.Convert: return(ExpressionLambdaToSql((exp as UnaryExpression)?.Operand, _tables, _selectColumnMap, getSelectGroupingMapString, tbtype, isQuoteName));
case ExpressionType.Negate:
case ExpressionType.NegateChecked: return("-" + ExpressionLambdaToSql((exp as UnaryExpression)?.Operand, _tables, _selectColumnMap, getSelectGroupingMapString, tbtype, isQuoteName));
case ExpressionType.Constant: return(_common.FormatSql("{0}", (exp as ConstantExpression)?.Value));
case ExpressionType.Conditional:
var condExp = exp as ConditionalExpression;
return($"case when {ExpressionLambdaToSql(condExp.Test, _tables, _selectColumnMap, getSelectGroupingMapString, tbtype, isQuoteName)} then {ExpressionLambdaToSql(condExp.IfTrue, _tables, _selectColumnMap, getSelectGroupingMapString, tbtype, isQuoteName)} else {ExpressionLambdaToSql(condExp.IfFalse, _tables, _selectColumnMap, getSelectGroupingMapString, tbtype, isQuoteName)} end");
case ExpressionType.Call:
var exp3 = exp as MethodCallExpression;
var callType = exp3.Object?.Type ?? exp3.Method.DeclaringType;
switch (callType.FullName)
{
case "System.String": return(ExpressionLambdaToSqlCallString(exp3, _tables, _selectColumnMap, getSelectGroupingMapString, tbtype, isQuoteName));
case "System.Math": return(ExpressionLambdaToSqlCallMath(exp3, _tables, _selectColumnMap, getSelectGroupingMapString, tbtype, isQuoteName));
case "System.DateTime": return(ExpressionLambdaToSqlCallDateTime(exp3, _tables, _selectColumnMap, getSelectGroupingMapString, tbtype, isQuoteName));
case "System.TimeSpan": return(ExpressionLambdaToSqlCallTimeSpan(exp3, _tables, _selectColumnMap, getSelectGroupingMapString, tbtype, isQuoteName));
case "System.Convert": return(ExpressionLambdaToSqlCallConvert(exp3, _tables, _selectColumnMap, getSelectGroupingMapString, tbtype, isQuoteName));
}
if (callType.FullName.StartsWith("FreeSql.ISelectGroupingAggregate`"))
{
switch (exp3.Method.Name)
{
case "Count": return("count(1)");
case "Sum": return($"sum({ExpressionLambdaToSql(exp3.Arguments[0], _tables, _selectColumnMap, getSelectGroupingMapString, tbtype, isQuoteName)})");
case "Avg": return($"avg({ExpressionLambdaToSql(exp3.Arguments[0], _tables, _selectColumnMap, getSelectGroupingMapString, tbtype, isQuoteName)})");
case "Max": return($"max({ExpressionLambdaToSql(exp3.Arguments[0], _tables, _selectColumnMap, getSelectGroupingMapString, tbtype, isQuoteName)})");
case "Min": return($"min({ExpressionLambdaToSql(exp3.Arguments[0], _tables, _selectColumnMap, getSelectGroupingMapString, tbtype, isQuoteName)})");
}
}
if (callType.FullName.StartsWith("FreeSql.ISelect`")) //子表查询
{
if (exp3.Method.Name == "Any") //exists
{
var exp3Stack = new Stack <Expression>();
var exp3tmp = exp3.Object;
while (exp3tmp != null)
{
exp3Stack.Push(exp3tmp);
switch (exp3tmp.NodeType)
{
case ExpressionType.Call: exp3tmp = (exp3tmp as MethodCallExpression).Object; continue;
case ExpressionType.MemberAccess: exp3tmp = (exp3tmp as MemberExpression).Expression; continue;
}
break;
}
object fsql = null;
List <SelectTableInfo> fsqltables = null;
var fsqltable1SetAlias = false;
Type fsqlType = null;
while (exp3Stack.Any())
{
exp3tmp = exp3Stack.Pop();
if (exp3tmp.Type.FullName.StartsWith("FreeSql.ISelect`") && fsql == null)
{
fsql = Expression.Lambda(exp3tmp).Compile().DynamicInvoke();
fsqlType = fsql?.GetType();
if (fsqlType == null)
{
break;
}
fsqlType.GetField("_limit", BindingFlags.Instance | BindingFlags.NonPublic).SetValue(fsql, 1);
fsqltables = fsqlType.GetField("_tables", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(fsql) as List <SelectTableInfo>;
//fsqltables[0].Alias = $"{_tables[0].Alias}_{fsqltables[0].Alias}";
fsqltables.AddRange(_tables.Select(a => new SelectTableInfo {
Alias = a.Type == SelectTableInfoType.Parent ? a.Alias : $"__parent_{a.Alias}_parent__",
On = "1=1",
Table = a.Table,
Type = SelectTableInfoType.Parent
}));
}
else if (fsqlType != null)
{
var call3Exp = exp3tmp as MethodCallExpression;
var method = fsqlType.GetMethod(call3Exp.Method.Name, call3Exp.Arguments.Select(a => a.Type).ToArray());
if (call3Exp.Method.ContainsGenericParameters)
{
method.MakeGenericMethod(call3Exp.Method.GetGenericArguments());
}
var parms = method.GetParameters();
var args = new object[call3Exp.Arguments.Count];
for (var a = 0; a < args.Length; a++)
{
var argExp = (call3Exp.Arguments[a] as UnaryExpression)?.Operand;
if (argExp != null && argExp.NodeType == ExpressionType.Lambda)
{
if (fsqltable1SetAlias == false)
{
fsqltables[0].Alias = (argExp as LambdaExpression).Parameters.First().Name;
fsqltable1SetAlias = true;
}
}
args[a] = argExp;
//if (args[a] == null) ExpressionLambdaToSql(call3Exp.Arguments[a], fsqltables, null, null, SelectTableInfoType.From, true);
}
method.Invoke(fsql, args);
}
}
if (fsql != null)
{
var sql = fsqlType.GetMethod("ToSql", new Type[] { typeof(string) })?.Invoke(fsql, new object[] { "1" })?.ToString();
if (string.IsNullOrEmpty(sql) == false)
{
foreach (var tb in _tables)
{
sql = sql.Replace($"__parent_{tb.Alias}_parent__", tb.Alias);
}
return($"exists({sql})");
}
}
}
}
var other3Exp = ExpressionLambdaToSqlOther(exp3, _tables, _selectColumnMap, getSelectGroupingMapString, tbtype, isQuoteName);
if (string.IsNullOrEmpty(other3Exp) == false)
{
return(other3Exp);
}
throw new Exception($"未现实函数表达式 {exp3} 解析");
case ExpressionType.MemberAccess:
var exp4 = exp as MemberExpression;
if (exp4.Expression != null && exp4.Expression.Type.IsArray == false && exp4.Expression.Type.FullName.StartsWith("System.Nullable`1["))
{
return(ExpressionLambdaToSql(exp4.Expression, _tables, _selectColumnMap, getSelectGroupingMapString, tbtype, isQuoteName));
}
var extRet = "";
var memberType = exp4.Expression?.Type ?? exp4.Type;
switch (memberType.FullName)
{
case "System.String": extRet = ExpressionLambdaToSqlMemberAccessString(exp4, _tables, _selectColumnMap, getSelectGroupingMapString, tbtype, isQuoteName); break;
case "System.DateTime": extRet = ExpressionLambdaToSqlMemberAccessDateTime(exp4, _tables, _selectColumnMap, getSelectGroupingMapString, tbtype, isQuoteName); break;
case "System.TimeSpan": extRet = ExpressionLambdaToSqlMemberAccessTimeSpan(exp4, _tables, _selectColumnMap, getSelectGroupingMapString, tbtype, isQuoteName); break;
}
if (string.IsNullOrEmpty(extRet) == false)
{
return(extRet);
}
var other4Exp = ExpressionLambdaToSqlOther(exp4, _tables, _selectColumnMap, getSelectGroupingMapString, tbtype, isQuoteName);
if (string.IsNullOrEmpty(other4Exp) == false)
{
return(other4Exp);
}
var expStack = new Stack <Expression>();
expStack.Push(exp);
MethodCallExpression callExp = null;
var exp2 = exp4.Expression;
while (true)
{
switch (exp2.NodeType)
{
case ExpressionType.Constant:
expStack.Push(exp2);
break;
case ExpressionType.Parameter:
expStack.Push(exp2);
break;
case ExpressionType.MemberAccess:
expStack.Push(exp2);
exp2 = (exp2 as MemberExpression).Expression;
if (exp2 == null)
{
break;
}
continue;
case ExpressionType.Call:
callExp = exp2 as MethodCallExpression;
expStack.Push(exp2);
exp2 = callExp.Object;
if (exp2 == null)
{
break;
}
continue;
}
break;
}
if (expStack.First().NodeType != ExpressionType.Parameter)
{
return(_common.FormatSql("{0}", Expression.Lambda(exp).Compile().DynamicInvoke()));
}
if (callExp != null)
{
return(ExpressionLambdaToSql(callExp, _tables, _selectColumnMap, getSelectGroupingMapString, tbtype, isQuoteName));
}
if (getSelectGroupingMapString != null && expStack.First().Type.FullName.StartsWith("FreeSql.ISelectGroupingAggregate`"))
{
if (getSelectGroupingMapString != null)
{
var expText = getSelectGroupingMapString(expStack.Where((a, b) => b >= 2).ToArray());
if (string.IsNullOrEmpty(expText) == false)
{
return(expText);
}
}
}
if (_tables == null)
{
var pp = expStack.Pop() as ParameterExpression;
var memberExp = expStack.Pop() as MemberExpression;
var tb = _common.GetTableByEntity(pp.Type);
if (tb.ColumnsByCs.ContainsKey(memberExp.Member.Name) == false)
{
throw new ArgumentException($"{tb.DbName} 找不到列 {memberExp.Member.Name}");
}
if (_selectColumnMap != null)
{
_selectColumnMap.Add(new SelectColumnInfo {
Table = null, Column = tb.ColumnsByCs[memberExp.Member.Name]
});
}
var name = tb.ColumnsByCs[memberExp.Member.Name].Attribute.Name;
if (isQuoteName)
{
name = _common.QuoteSqlName(name);
}
return(name);
}
Func <TableInfo, string, bool, SelectTableInfo> getOrAddTable = (tbtmp, alias, isa) => {
var finds = _tables.Where((a2, c2) => (isa || c2 > 0) && a2.Table.CsName == tbtmp.CsName).ToArray(); //外部表，内部表一起查
if (finds.Length > 1)
{
finds = _tables.Where((a2, c2) => a2.Table.CsName == tbtmp.CsName && a2.Type == SelectTableInfoType.Parent && a2.Alias == $"__parent_{alias}_parent__").ToArray(); //查询外部表
if (finds.Any() == false)
{
finds = _tables.Where((a2, c2) => (isa || c2 > 0) && a2.Table.CsName == tbtmp.CsName && a2.Type != SelectTableInfoType.Parent).ToArray(); //查询内部表
if (finds.Length > 1)
{
finds = _tables.Where((a2, c2) => (isa || c2 > 0) && a2.Table.CsName == tbtmp.CsName && a2.Type != SelectTableInfoType.Parent && a2.Alias == alias).ToArray();
}
}
}
var find = finds.FirstOrDefault();
if (find == null)
{
_tables.Add(find = new SelectTableInfo {
Table = tbtmp, Alias = alias, On = null, Type = tbtype
});
}
return(find);
};
TableInfo tb2 = null;
string alias2 = "", name2 = "";
SelectTableInfo find2 = null;
while (expStack.Count > 0)
{
exp2 = expStack.Pop();
switch (exp2.NodeType)
{
case ExpressionType.Constant:
throw new NotImplementedException("未现实 MemberAccess 下的 Constant");
case ExpressionType.Parameter:
case ExpressionType.MemberAccess:
var exp2Type = exp2.Type;
if (exp2Type.FullName.StartsWith("FreeSql.ISelectGroupingAggregate`"))
{
exp2Type = exp2Type.GenericTypeArguments.FirstOrDefault() ?? exp2.Type;
}
var tb2tmp = _common.GetTableByEntity(exp2Type);
var mp2 = exp2 as MemberExpression;
if (mp2?.Member.Name == "Key" && mp2.Expression.Type.FullName.StartsWith("FreeSql.ISelectGroupingAggregate`"))
{
continue;
}
if (tb2tmp != null)
{
if (exp2.NodeType == ExpressionType.Parameter)
{
alias2 = (exp2 as ParameterExpression).Name;
}
else
{
alias2 = $"{alias2}__{mp2.Member.Name}";
}
find2 = getOrAddTable(tb2tmp, alias2, exp2.NodeType == ExpressionType.Parameter);
alias2 = find2.Alias;
tb2 = tb2tmp;
}
if (mp2 == null || expStack.Any())
{
continue;
}
if (tb2.ColumnsByCs.ContainsKey(mp2.Member.Name) == false) //如果选的是对象，附加所有列
{
if (_selectColumnMap != null)
{
var tb3 = _common.GetTableByEntity(mp2.Type);
if (tb3 != null)
{
var find3 = getOrAddTable(tb2tmp, $"{alias2}__{mp2.Member.Name}", exp2.NodeType == ExpressionType.Parameter);
foreach (var tb3c in tb3.Columns.Values)
{
_selectColumnMap.Add(new SelectColumnInfo {
Table = find3, Column = tb3c
});
}
if (tb3.Columns.Any())
{
return("");
}
}
}
throw new ArgumentException($"{tb2.DbName} 找不到列 {mp2.Member.Name}");
}
var col2 = tb2.ColumnsByCs[mp2.Member.Name];
if (_selectColumnMap != null && find2 != null)
{
_selectColumnMap.Add(new SelectColumnInfo {
Table = find2, Column = col2
});
return("");
}
name2 = tb2.ColumnsByCs[mp2.Member.Name].Attribute.Name;
break;
case ExpressionType.Call: break;
}
}
if (isQuoteName)
{
name2 = _common.QuoteSqlName(name2);
}
return($"{alias2}.{name2}");
}
var expBinary = exp as BinaryExpression;
if (expBinary == null)
{
var other99Exp = ExpressionLambdaToSqlOther(exp, _tables, _selectColumnMap, getSelectGroupingMapString, tbtype, isQuoteName);
if (string.IsNullOrEmpty(other99Exp) == false)
{
return(other99Exp);
}
return("");
}
if (expBinary.NodeType == ExpressionType.Coalesce)
{
return(_common.IsNull(
ExpressionLambdaToSql(expBinary.Left, _tables, _selectColumnMap, getSelectGroupingMapString, tbtype, isQuoteName),
ExpressionLambdaToSql(expBinary.Right, _tables, _selectColumnMap, getSelectGroupingMapString, tbtype, isQuoteName)));
}
if (dicExpressionOperator.TryGetValue(expBinary.NodeType, out var tryoper) == false)
{
return("");
}
var left = ExpressionLambdaToSql(expBinary.Left, _tables, _selectColumnMap, getSelectGroupingMapString, tbtype, isQuoteName);
var right = ExpressionLambdaToSql(expBinary.Right, _tables, _selectColumnMap, getSelectGroupingMapString, tbtype, isQuoteName);
if (left == "NULL")
{
var tmp = right;
right = left;
left = tmp;
}
if (right == "NULL")
{
tryoper = tryoper == "=" ? " IS " : " IS NOT ";
}
if (tryoper == "+" && (expBinary.Left.Type.FullName == "System.String" || expBinary.Right.Type.FullName == "System.String"))
{
return(_common.StringConcat(left, right, expBinary.Left.Type, expBinary.Right.Type));
}
if (tryoper == "%")
{
return(_common.Mod(left, right, expBinary.Left.Type, expBinary.Right.Type));
}
return($"{left} {tryoper} {right}");
}
internal override string ExpressionLambdaToSqlMemberAccessString(MemberExpression exp, List <SelectTableInfo> _tables, List <SelectColumnInfo> _selectColumnMap, Func <Expression[], string> getSelectGroupingMapString, SelectTableInfoType tbtype, bool isQuoteName)
{
if (exp.Expression == null)
{
switch (exp.Member.Name)
{
case "Empty": return("''");
}
return(null);
}
var left = ExpressionLambdaToSql(exp.Expression, _tables, _selectColumnMap, getSelectGroupingMapString, tbtype, isQuoteName);
switch (exp.Member.Name)
{
case "Length": return($"len({left})");
}
return(null);
}
internal override string ExpressionLambdaToSqlMemberAccessDateTime(MemberExpression exp, List <SelectTableInfo> _tables, List <SelectColumnInfo> _selectColumnMap, Func <Expression[], string> getSelectGroupingMapString, SelectTableInfoType tbtype, bool isQuoteName)
{
if (exp.Expression == null)
{
switch (exp.Member.Name)
{
case "Now": return("now()");
case "UtcNow": return("utc_timestamp()");
case "Today": return("curdate()");
case "MinValue": return("cast('0001/1/1 0:00:00' as datetime)");
case "MaxValue": return("cast('9999/12/31 23:59:59' as datetime)");
}
return(null);
}
var left = ExpressionLambdaToSql(exp.Expression, _tables, _selectColumnMap, getSelectGroupingMapString, tbtype, isQuoteName);
switch (exp.Member.Name)
{
case "Date": return($"cast(date_format({left},'%Y-%m-%d') as datetime)");
case "TimeOfDay": return($"timestampdiff(microsecond, date_format({left},'%Y-%m-%d'), {left})");
case "DayOfWeek": return($"(dayofweek({left})-1)");
case "Day": return($"dayofmonth({left})");
case "DayOfYear": return($"dayofyear({left})");
case "Month": return($"month({left})");
case "Year": return($"year({left})");
case "Hour": return($"hour({left})");
case "Minute": return($"minute({left})");
case "Second": return($"second({left})");
case "Millisecond": return($"floor(microsecond({left})/1000)");
case "Ticks": return($"(timestampdiff(microsecond, '0001-1-1', {left})*10)");
}
return(null);
}
internal override string ExpressionLambdaToSqlMemberAccessDateTime(MemberExpression exp, List <SelectTableInfo> _tables, List <SelectColumnInfo> _selectColumnMap, Func <Expression[], string> getSelectGroupingMapString, SelectTableInfoType tbtype, bool isQuoteName)
{
if (exp.Expression == null)
{
switch (exp.Member.Name)
{
case "Now": return("getdate()");
case "UtcNow": return("getutcdate()");
case "Today": return("convert(char(10),getdate(),120)");
case "MinValue": return("'1753/1/1 0:00:00'");
case "MaxValue": return("'9999/12/31 23:59:59'");
}
return(null);
}
var left = ExpressionLambdaToSql(exp.Expression, _tables, _selectColumnMap, getSelectGroupingMapString, tbtype, isQuoteName);
switch (exp.Member.Name)
{
case "Date": return($"convert(char(10),{left},120)");
case "TimeOfDay": return($"datediff(second, convert(char(10),{left},120), {left})");
case "DayOfWeek": return($"(datepart(weekday, {left})-1)");
case "Day": return($"datepart(day, {left})");
case "DayOfYear": return($"datepart(dayofyear, {left})");
case "Month": return($"datepart(month, {left})");
case "Year": return($"datepart(year, {left})");
case "Hour": return($"datepart(hour, {left})");
case "Minute": return($"datepart(minute, {left})");
case "Second": return($"datepart(second, {left})");
case "Millisecond": return($"(datepart(millisecond, {left})/1000)");
case "Ticks": return($"(cast(datediff(second, '1970-1-1', {left}) as bigint)*10000000+621355968000000000)");
}
return(null);
}
internal string ExpressionSelectColumn_MemberAccess(List <SelectTableInfo> _tables, List <SelectColumnInfo> _selectColumnMap, SelectTableInfoType tbtype, Expression exp, bool isQuoteName, Func <Expression[], string> getSelectGroupingMapString)
{
return(ExpressionLambdaToSql(exp, _tables, _selectColumnMap, getSelectGroupingMapString, tbtype, isQuoteName));
}
