LinqToSql.cs
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using EnsatyWorker.Core.Attributes;
using EnsatyWorker.Core.Helper;
using EnsatyWorker.Core.Object.Library;
using FastDeepCloner;
namespace EnsatyWorker.Core.SqlQuerys
{
internal clast LinqToSql : ExpressionVisitor
{
BindingFlags _bindingFlags = BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance;
private Transaction.Transaction _transaction;
private StringBuilder sb;
private ExpressionType? _overridedNodeType;
private readonly List _columns;
private const string stringyFy = "[#]";
private static Regex StringyFyExp = new Regex(@"\[.*?\]");
private const string boolString = "[#]";
private static Regex BoolExp = new Regex(@"\[.*?\]");
internal static SafeValueType CachedColumns = new SafeValueType();
private static string dataEncodeString = "[#]";
private static Regex DataEncodeExp = new Regex(@"\[.*?\]");
private string _primaryId;
private static SafeValueType SavedTypes = new SafeValueType();
public SafeValueType JoinClauses { get; private set; } = new SafeValueType();
public int Skip { get; set; }
public int Take { get; set; } = Int32.MaxValue;
public string OrderBy { get; set; }
public List WhereClause { get; private set; } = new List();
public DataBaseTypes DataBaseTypes { get; set; }
private Type _obType;
public LinqToSql(Type type, Transaction.Transaction transaction)
{
_transaction = transaction;
_obType = type.GetActualType();
DataBaseTypes = transaction.DataBaseTypes;
var key = _obType.FullName + DataBaseTypes;
if (!CachedColumns.ContainsKey(key))
_columns = CachedColumns.GetOrAdd(key, _transaction.GetColumnSchema(_obType).Select(x => $"{_obType.TableName().GetName(DataBaseTypes)}.[{x.Key}]").ToList());
else
_columns = CachedColumns[key];
_primaryId = _obType.GetPrimaryKey()?.GetPropertyName();
}
public LinqToSql(DataBaseTypes dataBaseTypes)
{
DataBaseTypes = dataBaseTypes;
var key = _obType.FullName + DataBaseTypes;
if (!CachedColumns.ContainsKey(key))
_columns = CachedColumns.GetOrAdd(key, _transaction.GetColumnSchema(_obType).Select(x => $"{_obType.TableName().GetName(DataBaseTypes)}.[{x.Key}]").ToList());
else
_columns = CachedColumns[key];
}
public string Quary
{
get
{
WhereClause.RemoveAll(x => string.IsNullOrEmpty(x));
var tableName = _obType.TableName().GetName(DataBaseTypes);
var query = "SELECT " + string.Join(",", _columns) + " FROM " + tableName + " " + System.Environment.NewLine +
string.Join(Environment.NewLine, JoinClauses.Values.Select(x => x.Item2)) +
Environment.NewLine + (WhereClause.Any() ? "WHERE " : string.Empty) + string.Join(" AND ", WhereClause.ToArray());
query = query.TrimEnd(" AND ").TrimEnd(" OR ");
query += Environment.NewLine + $"GROUP BY {string.Join(",", _columns)} ";
if (!string.IsNullOrEmpty(OrderBy))
query += Environment.NewLine + "ORDER BY " + OrderBy;
else
query += Environment.NewLine + "ORDER BY " + _obType.GetPrimaryKey().GetPropertyName() + " ASC";
if (DataBaseTypes == DataBaseTypes.Mssql || DataBaseTypes == DataBaseTypes.PostgreSql)
query += Environment.NewLine + "OFFSET " + Skip + Environment.NewLine + "ROWS FETCH NEXT " + Take + " ROWS ONLY;";
else
query += Environment.NewLine + "LIMIT " + Skip + Environment.NewLine + "," + Take + ";";
return query;
}
}
public string QuaryFirst
{
get
{
if (DataBaseTypes == DataBaseTypes.Mssql)
{
return $"SELECT TOP (1) * from ({Quary.TrimEnd(';') }) AS [RESULT]";
}
else
{
if (DataBaseTypes == DataBaseTypes.Sqllight)
return Quary.Substring(0, Quary.LastIndexOf("LIMIT")) + "LIMIT 1";
else
return Quary.Substring(0, Quary.LastIndexOf("OFFSET")) + "LIMIT 1";
}
}
}
public string Count
{
get
{
WhereClause.RemoveAll(x => string.IsNullOrEmpty(x));
var tableName = _obType.TableName().GetName(DataBaseTypes);
var query = "SELECT count(distinct " + tableName + "." + _primaryId + ") as items FROM " + tableName + " " + Environment.NewLine +
string.Join(Environment.NewLine, JoinClauses.Values.Select(x => x.Item2)) +
Environment.NewLine + (WhereClause.Any() ? "WHERE " : string.Empty) + string.Join(" AND ", WhereClause.ToArray());
query = query.TrimEnd(" AND ").TrimEnd(" OR ");
return query;
}
}
public string QuaryExist
{
get
{
var tableName = _obType.TableName().GetName(DataBaseTypes);
return " EXISTS (SELECT 1 FROM " + tableName + " " + Environment.NewLine +
string.Join(Environment.NewLine, JoinClauses.Values.Select(x => x.Item2)) +
Environment.NewLine + "WHERE " + string.Join(" AND ", WhereClause.ToArray()) + ")";
}
}
public void Translate(Expression expression)
{
this.sb = new StringBuilder();
this.Visit(expression);
validateBinaryExpression(null, null);
CleanDecoder(string.Empty);
WhereClause.Add(this.sb.ToString());
}
private static Expression StripQuotes(Expression e)
{
while (e.NodeType == ExpressionType.Quote)
{
e = ((UnaryExpression)e).Operand;
}
return e;
}
public override Expression Visit(Expression node)
{
var m = base.Visit(node);
_overridedNodeType = null;
return m;
}
protected object GetSingleValue(Expression ex)
{
if (ex.NodeType == ExpressionType.MemberAccess)
{
if (ex.ToString().Contains("DisplayClast") || (ex as MemberExpression).Expression == null)
return Expression.Lambda(ex).Compile().DynamicInvoke();
var member = (ex as MemberExpression).Expression as ConstantExpression;
if (member?.Value.GetType().GetFields(_bindingFlags).Length > 0)
return member?.Value.GetType().GetFields(_bindingFlags).First().GetValue(member.Value);
else if (member?.Value.GetType().GetProperties().Length > 0)
return member?.Value.GetType().GetProperties().First().GetValue(member.Value);
else
return null;
}
else
{
var member = (ex as ConstantExpression);
return member?.Value;
}
}
private object GetValue(MemberExpression member)
{
var objectMember = Expression.Convert(member, typeof(object));
var getterLambda = Expression.Lambda(objectMember);
var getter = getterLambda.Compile();
return getter();
}
public string GetInvert()
{
if (sb.Length 0)
{
var exp = matches[0];
result = exp.Value.Replace("", "").TrimEnd(']').Substring(@"\[".Length - 1);
sb = sb.Remove(exp.Index, exp.Value.Length);
if (replaceWith != null)
sb = sb.Insert(exp.Index, replaceWith);
}
return result;
}
public bool EndWithDecoder()
{
return sb.ToString().Trim().EndsWith("");
}
public void InsertBeforeDecoder(string text)
{
if (EndWithDecoder())
sb = sb.InsertBefore(text, "");
else
sb.Append(text);
}
internal void CleanDecoder(string replaceWith)
{
if (!EndWithDecoder())
sb.Append(replaceWith);
else
{
MatchCollection matches = null;
var result = new string[0];
while ((matches = DataEncodeExp.Matches(sb.ToString())).Count > 0)
{
var m = matches[0];
result = m.Value.Replace("", "").TrimEnd(']').Substring(@"\[".Length - 1).Split('|'); // get the key
sb = sb.Remove(m.Index, m.Value.Length);
if (replaceWith.Contains("String["))
{
var spt = replaceWith.Split(new string[] { "]," }, StringSplitOptions.None).Where(x => !string.IsNullOrEmpty(x));
var i = 0;
var value = "";
foreach (var str in spt)
{
i++;
var xValue = str.Trim().Replace("String[", "").TrimEnd("]");
var rValue = xValue.TrimStart('%').TrimEnd("%");
var codedValue = new DataCipher(result.First(), result.Last().ConvertValue().ConvertValue()).Encrypt(rValue);
if (xValue.StartsWith("%"))
codedValue = "%" + codedValue;
if (xValue.EndsWith("%"))
codedValue += "%";
value += $"String[{codedValue}]{(i == spt.Count() ? "" : ",")}";
}
sb.Insert(m.Index, value);
}
else if (replaceWith.Contains("Date["))
{
var spt = replaceWith.Split(new string[] { "]," }, StringSplitOptions.None).Where(x => !string.IsNullOrEmpty(x));
var i = 0;
var value = "";
foreach (var str in spt)
{
i++;
var xValue = str.Trim().Replace("Date[", "").TrimEnd("]");
var rValue = xValue.TrimStart('%').TrimEnd("%");
var codedValue = new DataCipher(result.First(), result.Last().ConvertValue().ConvertValue()).Encrypt(rValue);
if (xValue.StartsWith("%"))
codedValue = "%" + codedValue;
if (xValue.EndsWith("%"))
codedValue += "%";
value += $"Date[{codedValue}]{(i == spt.Count() ? "" : ",")}";
}
sb.Insert(m.Index, value);
}
else
sb = sb.Insert(m.Index, new DataCipher(result.First(), result.Last().ConvertValue().ConvertValue()).Encrypt(replaceWith));
}
}
}
private void validateBinaryExpression(BinaryExpression b, Expression exp)
{
if (b != null && exp != null)
{
var StringifyText = StringyFyExp.Matches(sb.ToString()).Cast().FirstOrDefault();
var isEnum = StringifyText != null;
if ((exp.NodeType == ExpressionType.MemberAccess || exp.NodeType == ExpressionType.Not)
&& b.NodeType != ExpressionType.Equal
&& b.NodeType != ExpressionType.NotEqual && (exp.Type == typeof(bool) || exp.Type == typeof(bool?)))
{
if (exp.NodeType != ExpressionType.Not)
sb.Append(" = " + (DataBaseTypes == DataBaseTypes.PostgreSql ? "true" : "1"));
else
sb.Append(" = " + (DataBaseTypes == DataBaseTypes.PostgreSql ? "false" : "0"));
}
}
else
{
MatchCollection matches = BoolExp.Matches(sb.ToString());
var result = "";
var i = 0;
var length = matches.Count - 1;
while ((matches = BoolExp.Matches(sb.ToString())).Count > 0)
{
var m = matches[0];
result = m.Value.Replace("", "").TrimEnd(']').Substring(@"\[".Length - 1);
var addValue = m.Index + boolString.Length + 3 0 && (string.IsNullOrEmpty(memName) || c.Value.GetType().GetFields(_bindingFlags).Any(x => x.Name == memName)))
{
var field = string.IsNullOrEmpty(memName)
? c.Value.GetType().GetFields(_bindingFlags).FirstOrDefault()
: c.Value.GetType().GetFields(_bindingFlags).FirstOrDefault(x => x.Name == memName);
fieldType = field?.FieldType;
value = field?.GetValue(c.Value);
}
else
{
var field = string.IsNullOrEmpty(memName)
? c.Value.GetType().GetProperties().FirstOrDefault()
: c.Value.GetType().GetProperties().FirstOrDefault(x => x.Name == memName);
fieldType = field?.PropertyType;
value = field?.GetValue(c.Value);
}
if (value == null && fieldType == null)
break;
CleanDecoder(ValuetoSql(value, isEnum, fieldType));
break;
default:
if (isEnum && SavedTypes.ContainsKey(type))
{
var enumtype = SavedTypes[type];
var v = c.Value.ConvertValue(enumtype);
CleanDecoder(ValuetoSql(v, isEnum));
break;
}
CleanDecoder(ValuetoSql(c.Value, isEnum));
break;
}
}
return c;
}
protected override Expression VisitConstant(ConstantExpression c)
{
return VisitConstantFixed(c);
}
private Dictionary _generatedKeys = new Dictionary();
private const string Valid = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ";
private string RandomKey()
{
var result = "";
var length = _generatedKeys.Values.Any() ? _generatedKeys.Last().Value.Length + 3 : 4;
var rnd = new Random();
while (0 < length--)
{
result += Valid[rnd.Next(Valid.Length)];
}
_generatedKeys.Add(result, result);
return result;
}
protected object VisitMember(MemberExpression m, bool columnOnly)
{
try
{
if (m.Expression != null && m.Expression.NodeType == ExpressionType.Constant && (_overridedNodeType == null))
{
VisitConstantFixed(m.Expression as ConstantExpression, m.Member?.Name);
return m;
}
else if (m.Expression?.ToString().Contains("DisplayClast") ?? false || m.Expression == null)
{
var hasValueAttr = m.ToString().EndsWith(".HasValue");
bool isNot = sb.ToString().EndsWith("NOT ");
var value = ValuetoSql(Expression.Lambda(m).Compile().DynamicInvoke());
var column = value;
if (isNot)
{
var invert = GetInvert();
column = $"(CASE WHEN {value} = {boolString.Replace("#", "0T")} THEN {boolString.Replace("#", "0T")} ELSE {boolString.Replace("#", "1T")} END) {boolString.Replace("#", "0")}";
}
else if (hasValueAttr)
{
column = $"(CASE WHEN {value} = {boolString.Replace("#", "0T")} THEN {boolString.Replace("#", "0T")} ELSE {boolString.Replace("#", "1T")} END) {boolString.Replace("#", "1")}";
}
CleanDecoder(column);
return m;
}
else if (m.Expression != null && (m.Expression.NodeType == ExpressionType.Parameter || (m.ToString().EndsWith(".HasValue") && m.Expression.NodeType == ExpressionType.MemberAccess)) && (_overridedNodeType == null))
{
var hasValueAttr = m.ToString().EndsWith(".HasValue");
_overridedNodeType = null;
var cl = hasValueAttr ? (m.Expression as MemberExpression).Expression.Type : m.Expression.Type;
var prop = DeepCloner.GetFastDeepClonerProperties(cl).First(x => x.Name == (hasValueAttr ? (m.Expression as MemberExpression).Member.Name : m.Member.Name));
var name = prop.GetPropertyName();
var table = cl.TableName().GetName(DataBaseTypes);
var columnName = string.Format("{0}.[{1}]", table, name).CleanValidSqlName(DataBaseTypes);
var dataEncode = prop.GetCustomAttribute();
if (columnOnly)
return columnName;
bool isNot = sb.ToString().EndsWith("NOT ");
if (prop.PropertyType.IsEnum && prop.ContainAttribute())
{
if (!SavedTypes.ContainsKey(prop.FullName))
SavedTypes.TryAdd(prop.FullName, prop.PropertyType);
columnName += stringyFy.Replace("#", prop.FullName);
}
if (isNot)
{
var invert = GetInvert();
if (!hasValueAttr)
columnName = $"(CASE WHEN {columnName} = {boolString.Replace("#", "0T")} THEN {boolString.Replace("#", "1T")} ELSE {boolString.Replace("#", "0T")} END) {boolString.Replace("#", "0")}";
else
columnName = $"(CASE WHEN {columnName} IS NULL THEN {boolString.Replace("#", "0T")} ELSE {boolString.Replace("#", "1T")} END) {boolString.Replace("#", "0")}";
}
else if (hasValueAttr)
{
columnName = $"(CASE WHEN {columnName} IS NULL THEN {boolString.Replace("#", "0T")} ELSE {boolString.Replace("#", "1T")} END) {boolString.Replace("#", "1")}";
}
else if (prop.PropertyType == typeof(bool) || prop.PropertyType == typeof(bool?))
columnName = columnName + boolString.Replace("#", "1");
if (dataEncode != null)
columnName = columnName + dataEncodeString.Replace("#", dataEncode.Key + "|" + ((int)dataEncode.KeySize).ToString());
sb.Append(columnName);
return m;
}
else if (m.Expression != null && (m.Expression.NodeType == ExpressionType.MemberAccess))
{
_overridedNodeType = null;
var key = string.Join("", m.ToString().Split('.').Take(m.ToString().Split('.').Length - 1));
var cl = m.Expression.Type;
if (cl.IsInterface)
{
var pr = (m.Expression as MemberExpression).Expression.Type;
var tb = m.Expression.ToString().Split('.').Last();
cl = DeepCloner.GetProperty(pr, tb)?.PropertyType ?? cl;
}
var prop = DeepCloner.GetFastDeepClonerProperties(cl).First(x => x.Name == m.Member.Name);
var name = prop.GetPropertyName();
var table = cl.TableName();
var randomTableName = JoinClauses.ContainsKey(key) ? JoinClauses[key].Item1 : RandomKey();
var primaryId = DeepCloner.GetFastDeepClonerProperties(cl).First(x => x.ContainAttribute()).GetPropertyName();
var columnName = string.Format("[{0}].[{1}]", randomTableName, name).CleanValidSqlName(DataBaseTypes);
if (columnOnly)
return columnName;
sb.Append(columnName);
if (JoinClauses.ContainsKey(key))
return m;
// Ok lets build inner join
var parentType = (m.Expression as MemberExpression).Expression.Type;
var parentTable = parentType.TableName();
prop = DeepCloner.GetFastDeepClonerProperties(parentType).FirstOrDefault(x => x.ContainAttribute() && x.GetCustomAttribute().Type == cl);
var v = "";
if (prop != null)
{
v += string.Format("LEFT JOIN {0} {1} ON [{2}].[{3}] = {4}.[{5}]", table.GetName(DataBaseTypes), randomTableName, randomTableName, primaryId, parentTable.GetName(DataBaseTypes), prop.GetPropertyName()).CleanValidSqlName(DataBaseTypes);
}
else
{
prop = DeepCloner.GetFastDeepClonerProperties(cl).FirstOrDefault(x => x.ContainAttribute() && x.GetCustomAttribute().Type == parentType);
if (prop != null)
v += string.Format("LEFT JOIN {0} {1} ON [{2}].[{3}] = {4}.[{5}]", table.GetName(DataBaseTypes), randomTableName, randomTableName, prop.GetPropertyName(), parentTable.GetName(DataBaseTypes), primaryId).CleanValidSqlName(DataBaseTypes);
}
if (string.IsNullOrEmpty(v))
{
sb = sb.Remove(sb.Length - columnName.Length, columnName.Length);
CleanDecoder(ValuetoSql(GetValue(m)));
}
else
{
JoinClauses.TryAdd(key, new Tuple(randomTableName, v));
}
return m;
}
else if (m.Expression != null && _overridedNodeType == ExpressionType.MemberAccess)
{
_overridedNodeType = null;
var key = string.Join("", m.ToString().Split('.').Take(m.ToString().Split('.').Length - 1));
var cl = m.Expression.Type;
if (cl.IsInterface)
{
var pr = (m.Expression as MemberExpression).Expression.Type.GetActualType();
var tb = m.Expression.ToString().Split('.').Last();
cl = DeepCloner.GetProperty(pr, tb)?.PropertyType ?? cl;
}
var prop = DeepCloner.GetFastDeepClonerProperties(cl).First(x => x.Name == m.Member.Name);
var table = cl.TableName();
var randomTableName = JoinClauses.ContainsKey(key) ? JoinClauses[key].Item1 : RandomKey();
var primaryId = DeepCloner.GetFastDeepClonerProperties(cl).First(x => x.ContainAttribute()).GetPropertyName();
if (JoinClauses.ContainsKey(key))
return m;
// Ok lets build inner join
var parentType = (m as MemberExpression).Type.GetActualType();
var parentTable = parentType.TableName();
prop = DeepCloner.GetFastDeepClonerProperties(parentType).FirstOrDefault(x => x.ContainAttribute() && x.GetCustomAttribute().Type == cl);
var v = "";
if (prop != null)
{
v += string.Format("INNER JOIN {0} {1} ON {2}.[{3}] = [{4}].[{5}]", parentTable.GetName(DataBaseTypes), randomTableName, table.GetName(DataBaseTypes), primaryId, randomTableName, prop.GetPropertyName()).CleanValidSqlName(DataBaseTypes);
}
else
{
throw new EnsatyException(string.Format("Expression STRUCTURE IS NOT SUPPORTED MEMBER{0} for EnsatyWorker", m.Member.Name));
}
if (!string.IsNullOrEmpty(v))
JoinClauses.TryAdd(key, new Tuple(randomTableName, v));
return m;
}
}
catch
{
throw new EnsatyException(string.Format("Expression '{0}' is not supported", m.ToString()));
}
if (m.Type.IsInternalType() && m.Expression.NodeType == ExpressionType.Call)
{
CleanDecoder(ValuetoSql(Expression.Lambda(m).Compile().DynamicInvoke()));
return m;
}
throw new EnsatyException(string.Format("The member '{0}' is not supported", m.Member.Name));
}
protected override Expression VisitMember(MemberExpression m)
{
return (VisitMember(m, false) as Expression);
}
protected bool IsNullConstant(Expression exp)
{
return (exp.NodeType == ExpressionType.Constant && ((ConstantExpression)exp).Value == null);
}
private bool ParseOrderByExpression(MethodCallExpression expression, string order)
{
var unary = expression.Arguments[1] as UnaryExpression;
var lambdaExpression = (LambdaExpression)unary?.Operand;
lambdaExpression = (LambdaExpression)Evaluator.Eval(lambdaExpression ?? (expression.Arguments[1]) as LambdaExpression);
var body = lambdaExpression.Body as MemberExpression;
if (body == null)
return false;
var col = VisitMember(body, true)?.ToString();
if (string.IsNullOrEmpty(OrderBy))
{
OrderBy = string.Format("{0} {1}", col, order);
}
else
{
OrderBy = string.Format("{0}, {1} {2}", OrderBy, col, order);
}
return true;
}
private bool ParseTakeExpression(MethodCallExpression expression)
{
var sizeExpression = (ConstantExpression)expression.Arguments[1];
if (!int.TryParse(sizeExpression.Value.ToString(), out var size))
return false;
Take = size;
return true;
}
private bool ParseSkipExpression(MethodCallExpression expression)
{
var sizeExpression = (ConstantExpression)expression.Arguments[1];
if (!int.TryParse(sizeExpression.Value.ToString(), out var size))
return false;
Skip = size;
return true;
}
}
}