public void AddOrderBy <T>(Expression <Func <T, object> > expression)
{
SQLPredicate predicate = QueryTranslator.Translate(Evaluator.PartialEval(expression), i);
Where += " ORDER BY " + predicate.Sql;
//JoinFilters.Add(predicate);
}
/// <summary>
/// Gets a JOIN QueryPredicate based on a LINQ Expression with two parameters.
/// </summary>
/// <returns>The JOIN QueryPredicate.</returns>
/// <param name="expression">The expression to be translated.</param>
/// <typeparam name="T1">Left entity involved in the JOIN (it must inherit from IEntity class).</typeparam>
/// <typeparam name="T2">Right entity involved in the JOIN (it must inherit from IEntity class).</typeparam>
public void Join <T1, T2>(JoinType joinType, Expression <Func <T1, T2, bool> > expression, EntitySet <T1> entitySet1, EntitySet <T2> entitySet2) where T1 : class, IEntity, new() where T2 : class, IEntity, new()
{
T1 entity1 = new T1();
T2 entity2 = new T2();
string parameter1 = expression.Parameters[0].Name;
string parameter2 = expression.Parameters[1].Name;
if (joinType != JoinType.Inner && Parameters.Contains(parameter2))
{
throw new Exception($"Alias '" + parameter2 + " already used within a " + GetJoinType(JoinTypeDictionary[parameter2]) + "' operator. No further use with RIGHT JOIN or LEFT JOIN is allowed.");
}
SQLPredicate queryPredicate = QueryTranslator.Translate(Evaluator.PartialEval(expression), i);
i += queryPredicate.Parameters.Count;
AddParameter(parameter1, entity1, typeof(EntitySet <T1>), typeof(T1), entitySet1, joinType);
AddParameter(parameter2, entity2, typeof(EntitySet <T2>), typeof(T2), entitySet2, joinType);
if (AddingOrder[parameter1] > AddingOrder[parameter2])
{
QueryDictionary[parameter1].Add(queryPredicate);
}
else
{
QueryDictionary[parameter2].Add(queryPredicate);
}
}
/// <summary>
/// Uses QueryTranslator to translate a LINQ Expression to a SQL query.
/// </summary>
/// <returns>The sql query.</returns>
/// <param name="expression">The LINQ Expression to be translated.</param>
/// <typeparam name="T">The 1st type parameter.</typeparam>
private SQLPredicate Translate(Expression expression, bool ignoreAliases = false)
{
ISQLTranslator sql = SQLTranslatorFactory.GetTranslator(DbType);
SQLPredicate pred = sql.Translate(Evaluator.PartialEval(expression), 0, ignoreAliases);
return(pred);
}
/// <summary>
/// Uses QueryTranslator to translate a LINQ Expression to a SQL query.
/// </summary>
/// <returns>The sql query.</returns>
/// <param name="expression">The LINQ Expression to be translated.</param>
/// <typeparam name="T">The 1st type parameter.</typeparam>
private SQLPredicate Translate(Expression expression)
{
ISQLTranslator sql = SQLTranslatorFactory.GetTranslator(DbType);
SQLPredicate pred = sql.Translate(Evaluator.PartialEval(expression));
return(pred);
}
public void AddJoinFilter <T>(Expression <Func <T, bool> > expression) where T : IEntity, new()
{
SQLPredicate predicate = QueryTranslator.Translate(Evaluator.PartialEval(expression), i);
i += predicate.Parameters.Count;
Where += (Where.Length > 0 ? " AND " : " WHERE ") + predicate.Sql;
JoinFilters.Add(predicate);
}
public SQLPredicate Translate(Expression expression, int initial = 0)
{
HasPredicate = false;
Initial = initial;
sb = new StringBuilder();
predicate = new SQLPredicate();
Visit(expression);
predicate.Sql = sb.ToString();
return(predicate);
}
public SQLPredicate Translate(Expression expression, int initial = 0, bool ignoreAliases = false)
{
IgnoreAliases = ignoreAliases;
IsNested = false;
ResetClauses();
Initial = initial;
sb = new StringBuilder();
predicate = new SQLPredicate();
Visit(expression);
predicate.Sql = sb.ToString();
return(predicate);
}
public SQLPredicate Translate(Expression expression, int initial = 0, bool ignoreAliases = false)
{
IgnoreAliases = ignoreAliases;
HasOrderBy = false;
HasWhere = false;
HasGroupBy = false;
Initial = initial;
sb = new StringBuilder();
predicate = new SQLPredicate();
Visit(expression);
predicate.Sql = sb.ToString();
return(predicate);
}
public object Execute(Expression expression)
{
IDbCommand cmd = DbProvider.GetCommand();
SQLPredicate sql = Translate(expression);
cmd.CommandText = sql.Sql;
sql.SetParameters(cmd);
cmd.Connection = DbProvider.Connection;
try
{
IDataReader reader = cmd.ExecuteReader();
Type elementType = TypeSystem.GetElementType(expression.Type);
//This is to handle Count method
if (elementType == typeof(int))
{
reader.Read();
int result = Convert.ToInt32(reader[0] == DBNull.Value ? 0 : reader[0]);
reader.Dispose();
return(result);
}
if (elementType == typeof(double))
{
reader.Read();
double result = Convert.ToDouble(reader[0] == DBNull.Value ? 0 : reader[0]);
reader.Dispose();
return(result);
}
if (elementType == typeof(bool))
{
reader.Read();
bool result = Convert.ToBoolean(reader[0]);
reader.Dispose();
return(result);
}
return(Activator.CreateInstance(
typeof(ObjectReader <>).MakeGenericType(elementType),
BindingFlags.Instance | BindingFlags.NonPublic, null,
new object[] { reader },
null));
//}
}
catch (Exception e)
{
//TODO: Delete this in oproduction
throw new Exception(e.Message + "." + " Query: " + cmd.CommandText);
}
//}
}
public IDbCommand GetDeleteWhereCommand <T>(Expression <Func <T, bool> > expression, string TableName)
{
if (expression == null)
{
throw new ArgumentNullException(nameof(expression), "The provided expression cannot be null.");
}
SQLPredicate predicate = Translate(Evaluator.PartialEval(expression), true);
LambdaExpression lambda = (LambdaExpression)expression;//for getting alias
StringBuilder query = new StringBuilder();
query.Append($"DELETE FROM {TableName} WHERE {predicate.Sql};");
IDbCommand cmd = DbProvider.GetCommand();
cmd.CommandText = query.ToString();
predicate.SetParameters(cmd);
return(cmd);
}
public void AddPagination(int skip, int take)
{
SQLPredicate predicate = new SQLPredicate();
switch (DbProvider.DbType)
{
case DbType.MySql:
Where += " LIMIT " + take + " OFFSET " + skip;
break;
case DbType.Sql:
Where += "OFFSET" + skip + " ROWS FETCH NEXT " + take + " ROWS ONLY";
break;
default:
throw new Exception("Unsuported Database Provider.");
}
JoinFilters.Add(predicate);
}
public object Execute(Expression expression)
{
IDbCommand cmd = DbProvider.GetCommand();
SQLPredicate sql = Translate(expression);
LogQuery(sql.Sql, sql.Parameters);
cmd.CommandText = sql.Sql;
sql.SetParameters(cmd);
// cmd.Connection = DbProvider.Connection; eliminating this because DbProvider already passes its connection to cmd
try
{
IDataReader reader = cmd.ExecuteReader();
Type elementType = TypeSystem.GetElementType(expression.Type);
//TODO: for select to work properly, we need to identify whether the elementType is an IEnumerable or not
if (!typeof(IQueryable).IsAssignableFrom(expression.Type))
{
//This is to handle Count method
if (elementType == typeof(int))
{
reader.Read();
int result = Convert.ToInt32(reader[0] == DBNull.Value ? 0 : reader[0]);
reader.Dispose();
return(result);
}
//Sum method
if (elementType == typeof(double))
{
reader.Read();
double result = Convert.ToDouble(reader[0] == DBNull.Value ? 0 : reader[0]);
reader.Dispose();
return(result);
}
//Any method
if (elementType == typeof(bool))
{
reader.Read();
bool result = Convert.ToBoolean(reader[0]);
reader.Dispose();
return(result);
}
}
if (elementType.IsPrimitive || elementType == typeof(string))
{
return(Activator.CreateInstance(
typeof(VariableReader <>).MakeGenericType(elementType),
BindingFlags.Instance | BindingFlags.NonPublic, null,
new object[] { reader },
null));
}
return(Activator.CreateInstance(
typeof(ObjectReader <>).MakeGenericType(elementType),
BindingFlags.Instance | BindingFlags.NonPublic, null,
new object[] { reader },
null));
//}
}
catch (Exception e)
{
//TODO: Delete this in oproduction
throw new Exception(e.Message + "." + " Query: " + cmd.CommandText);
}
//}
}