private static QueryGroup Parse <TEntity>(BinaryExpression expression) where TEntity : class
{
var leftQueryGroup = (QueryGroup)null;
var rightQueryGroup = (QueryGroup)null;
var skipRight = false;
var isEqualsTo = true;
/*
* LEFT
*/
// Get the value in the right
if (expression.Right.Type == typeof(bool) && (expression.Right.IsConstant() || expression.Right.IsMember()))
{
var value = expression.Right.GetValue();
isEqualsTo = value is bool && Equals(value, false) != true;
skipRight = true;
}
// Binary
if (expression.Left.IsBinary() == true)
{
leftQueryGroup = Parse <TEntity>(expression.Left.ToBinary());
leftQueryGroup.SetIsNot(isEqualsTo == false);
}
// Unary
else if (expression.Left.IsUnary() == true)
{
leftQueryGroup = Parse <TEntity>(expression.Left.ToUnary(), isEqualsTo: isEqualsTo);
}
// MethodCall
else if (expression.Left.IsMethodCall())
{
leftQueryGroup = Parse <TEntity>(expression.Left.ToMethodCall(), isEqualsTo: isEqualsTo);
}
else
{
// Extractable
if (expression.IsExtractable())
{
leftQueryGroup = new QueryGroup(QueryField.Parse <TEntity>(expression).AsEnumerable());
skipRight = true;
}
}
/*
* RIGHT
*/
if (skipRight == false)
{
// Binary
if (expression.Right.IsBinary() == true)
{
rightQueryGroup = Parse <TEntity>(expression.Right.ToBinary());
}
// Unary
if (expression.Right.IsUnary() == true)
{
var unary = expression.Right.ToUnary();
rightQueryGroup = Parse <TEntity>(unary);
}
// MethodCall
else if (expression.Right.IsMethodCall())
{
rightQueryGroup = Parse <TEntity>(expression.Right.ToMethodCall());
}
// Return both of them
if (leftQueryGroup != null && rightQueryGroup != null)
{
var conjunction = (expression.NodeType == ExpressionType.OrElse) ? Conjunction.Or : Conjunction.And;
return(new QueryGroup(null, new[] { leftQueryGroup, rightQueryGroup }, conjunction));
}
}
// Return either one of them
return(leftQueryGroup ?? rightQueryGroup);
}
private static QueryGroup ParseAllOrAnyForArray <TEntity>(MethodCallExpression expression, bool isNot = false, bool isEqualsTo = true) where TEntity : class
{
// Return null if there is no any arguments
if (expression.Arguments?.Any() == false)
{
return(null);
}
// Get the last property
var last = expression
.Arguments
.LastOrDefault();
// Make sure the last is a member
if (last == null || last?.IsLambda() == false)
{
throw new NotSupportedException($"Expression '{expression.ToString()}' is currently not supported.");
}
// Make sure the last is a binary
var lambda = last.ToLambda();
if (lambda.Body.IsBinary() == false)
{
throw new NotSupportedException($"Expression '{expression.ToString()}' is currently not supported.");
}
// Make sure it is a member
var binary = lambda.Body.ToBinary();
if (binary.Left.IsMember() == false && binary.Right.IsMember() == false)
{
throw new NotSupportedException($"Expression '{expression.ToString()}' is currently not supported. Expression must contain a single condition to any property of type '{typeof(TEntity).FullName}'.");
}
// Make sure it is a property
var member = binary.Left.IsMember() ? binary.Left.ToMember().Member : binary.Right.ToMember().Member;
if (member.IsPropertyInfo() == false)
{
throw new NotSupportedException($"Expression '{expression.ToString()}' is currently not supported.");
}
// Make sure the property is in the entity
var property = member.ToPropertyInfo();
if (PropertyCache.Get <TEntity>().FirstOrDefault(p => p.PropertyInfo == property) == null)
{
throw new InvalidQueryExpressionException($"Invalid expression '{expression.ToString()}'. The property {property.Name} is not defined on a target type '{typeof(TEntity).FullName}'.");
}
// Variables needed for fields
var queryFields = new List <QueryField>();
var conjunction = Conjunction.And;
// Support only various methods
if (expression.Method.Name == StringConstant.Any)
{
conjunction = Conjunction.Or;
}
else if (expression.Method.Name == StringConstant.All)
{
conjunction = Conjunction.And;
}
// Call the method
var first = expression.Arguments.First();
var values = (object)null;
// Identify the type of the argument
if (first.IsNewArray())
{
values = first.ToNewArray().GetValue();
}
else if (first.IsMember())
{
values = first.ToMember().GetValue();
}
// Values must be an array
if (values is Array)
{
var operation = QueryField.GetOperation(binary.NodeType);
foreach (var value in (Array)values)
{
queryFields.Add(new QueryField(property.Name, operation, value));
}
}
// Return the result
return(new QueryGroup(queryFields, null, conjunction, (isNot == isEqualsTo)));
}
/// <summary>
/// Appends a word HAVING COUNT and a conditional field to the SQL Query Statement.
/// </summary>
/// <param name="queryField">The conditional field object used for composition.</param>
/// <returns>The current instance.</returns>
public QueryBuilder <TEntity> HavingCountFrom(QueryField queryField)
{
return(Append($"HAVING COUNT({queryField.Field.AsField()}) {queryField.GetOperationText()} {queryField.AsParameter()}"));
}
// Static Methods
/// <summary>
/// This method is used to parse the customized query tree expression. This method expects a dynamic object and converts it to the actual
/// <i>RepoDb.QueryGroup</i> that defines the query tree expression.
/// </summary>
/// <param name="obj">
/// A dynamic query tree expression to be parsed.
/// Example:
/// var expression = new { Conjunction = Conjunction.And, Company = "Microsoft",
/// FirstName = new { Operation = Operation.Like, Value = "An%" },
/// UpdatedDate = new { Operation = Operation.LessThan, Value = DateTime.UtcNow.Date }}
/// </param>
/// <returns>An instance of the <i>RepoDb.QueryGroup</i> object that contains the parsed query expression.</returns>
public static QueryGroup Parse(object obj)
{
// Cannot further optimize and shortify this method, this one works like a charm for now.
// Check for value
if (obj == null)
{
throw new ArgumentNullException($"Parameter '{StringConstant.Obj.ToLower()}' cannot be null.");
}
// Variables
var queryFields = new List <QueryField>();
var queryGroups = new List <QueryGroup>();
var conjunction = Conjunction.And;
// Iterate every property
var objectProperties = obj.GetType().GetTypeInfo().GetProperties();
objectProperties.ToList().ForEach(property =>
{
var fieldName = property.Name;
// Identify the fields
if (string.Equals(fieldName, StringConstant.Conjunction, StringComparison.CurrentCultureIgnoreCase))
{
// Conjunction
conjunction = (Conjunction)property.GetValue(obj);
}
else if (string.Equals(fieldName, StringConstant.QueryGroups, StringComparison.CurrentCultureIgnoreCase))
{
// Child QueryGroups
var value = property.GetValue(obj);
if (value is Array)
{
((Array)value).AsEnumerable().ToList().ForEach(item =>
{
queryGroups.Add(Parse(item));
});
}
else
{
queryGroups.Add(Parse(value));
}
}
else
{
// Other pre-defined fields
var value = property.GetValue(obj);
var type = value?.GetType();
if (type?.GetTypeInfo().IsGenericType == false || value == null)
{
// Most likely, (Field.Name = <value|null>)
queryFields.Add(new QueryField(fieldName, value));
}
else
{
// Another dynamic object type, get the 'Operation' property
var properties = type?.GetTypeInfo().GetProperties();
var operationProperty = properties?.FirstOrDefault(p => p.Name.ToLower() == StringConstant.Operation.ToLower());
// The property 'Operation' must always be present
if (operationProperty == null)
{
throw new InvalidOperationException($"The 'Operation' property must be present for field '{property.Name}'.");
}
// The property operatoin must be of type 'RepoDb.Enumerations.Operation'
if (operationProperty.PropertyType != typeof(Operation))
{
throw new InvalidOperationException($"The 'Operation' property for field '{property.Name}' must be of type '{typeof(Operation).FullName}'.");
}
// The 'Value' property must always be present
var valueProperty = properties?.FirstOrDefault(p => p.Name.ToLower() == StringConstant.Value.ToLower());
// Check for the 'Value' property
if (valueProperty == null)
{
throw new InvalidOperationException($"The 'Value' property for dynamic type query must be present at field '{property.Name}'.");
}
// Get the 'Operation' and the 'Value' value
var operation = (Operation)operationProperty.GetValue(value);
value = valueProperty.GetValue(value);
// For other operation, the 'Value' property must be present
if (value == null && (operation != Operation.Equal && operation != Operation.NotEqual))
{
throw new InvalidOperationException($"The value property '{valueProperty.Name}' must not be null.");
}
// Identify the 'Operation' and parse the correct value
if ((operation == Operation.Equal || operation == Operation.NotEqual) && value == null)
{
// Most likely, new { Field.Name = { Operation = Operation.<Equal|NotEqual>, Value = (object)null } }
// It should be (IS NULL) or (IS NOT NULL) in SQL Statement
queryFields.Add(QueryField.Parse(fieldName, value));
}
else if (operation == Operation.All || operation == Operation.Any)
{
// Special case: All (AND), Any (OR)
if (value.GetType().IsArray)
{
var childQueryGroupFields = new List <QueryField>();
((Array)value).AsEnumerable().ToList().ForEach(underlyingValue =>
{
childQueryGroupFields.Add(QueryField.Parse(fieldName, underlyingValue));
});
var queryGroup = new QueryGroup(childQueryGroupFields, null, operation == Operation.All ? Conjunction.And : Conjunction.Or);
queryGroups.Add(queryGroup);
}
else
{
queryFields.Add(QueryField.Parse(fieldName, value));
}
}
else
{
if (operation == Operation.Between || operation == Operation.NotBetween)
{
// Special case: (Field.Name = new { Operation = Operation.<Between|NotBetween>, Value = new [] { value1, value2 })
ValidateBetweenOperations(fieldName, operation, value);
}
else if (operation == Operation.In || operation == Operation.NotIn)
{
// Special case: (Field.Name = new { Operation = Operation.<In|NotIn>, Value = new [] { value1, value2 })
ValidateInOperations(fieldName, operation, value);
}
else
{
// Other Operations
ValidateOtherOperations(fieldName, operation, value);
}
// Add the field values
queryFields.Add(new QueryField(fieldName, operation, value));
}
}
}
});
// Return
return(new QueryGroup(queryFields, queryGroups, conjunction));
}
/// <summary>
/// Deletes all the target existing data from the database in an asynchronous way. It uses the <see cref="DeleteAsync(IDbConnection, string, QueryGroup, string, int?, IDbTransaction, ITrace, IStatementBuilder)"/> operation as the underlying operation.
/// </summary>
/// <param name="connection">The connection object to be used.</param>
/// <param name="tableName">The name of the target table.</param>
/// <param name="primaryKeys">The list of the primary keys to be deleted.</param>
/// <param name="hints">The table hints to be used.</param>
/// <param name="commandTimeout">The command timeout in seconds to be used.</param>
/// <param name="transaction">The transaction to be used.</param>
/// <param name="trace">The trace object to be used.</param>
/// <param name="statementBuilder">The statement builder object to be used.</param>
/// <returns>The number of rows affected by the execution.</returns>
public static async Task <int> DeleteAllAsync(this IDbConnection connection,
string tableName,
IEnumerable <object> primaryKeys,
string hints = null,
int?commandTimeout = null,
IDbTransaction transaction = null,
ITrace trace = null,
IStatementBuilder statementBuilder = null)
{
var primary = GetAndGuardPrimaryKey(connection, tableName, transaction);
var dbSetting = connection.GetDbSetting();
var hasImplicitTransaction = false;
var count = primaryKeys?.AsList()?.Count;
var deletedRows = 0;
try
{
// Creates a transaction (if needed)
if (transaction == null && count > Constant.MaxParametersCount)
{
transaction = connection.BeginTransaction();
hasImplicitTransaction = true;
}
// Call the underlying method
var splitted = primaryKeys.Split(Constant.MaxParametersCount).AsList();
foreach (var keys in splitted)
{
if (keys.Any() != true)
{
break;
}
var field = new QueryField(primary.Name.AsQuoted(dbSetting), Operation.In, keys?.AsList());
deletedRows += await DeleteAsyncInternal(connection : connection,
tableName : tableName,
where : new QueryGroup(field),
hints : hints,
commandTimeout : commandTimeout,
transaction : transaction,
trace : trace,
statementBuilder : statementBuilder);
}
// Commit the transaction
if (hasImplicitTransaction)
{
transaction?.Commit();
}
}
finally
{
// Dispose the transaction
if (hasImplicitTransaction)
{
transaction?.Dispose();
}
}
// Return the value
return(deletedRows);
}
/// <summary>
/// Queries a data from the database table function.
/// </summary>
/// <typeparam name="TEntity">The type of the data entity object.</typeparam>
/// <param name="connection">The connection object to be used.</param>
/// <param name="funcName">Function name.</param>
/// <param name="parameters">The dynamic expression of function parameters.</param>
/// <param name="orderBy">The order definition of the fields to be used.</param>
/// <param name="hints">The table hints to be used.</param>
/// <param name="cacheKey">
/// The key to the cache. If the cache key is present in the cache, then the item from the cache will be returned instead. Setting this
/// to null would force to query from the database.
/// </param>
/// <param name="cacheItemExpiration">The expiration in minutes of the cache item.</param>
/// <param name="commandTimeout">The command timeout in seconds to be used.</param>
/// <param name="transaction">The transaction to be used.</param>
/// <param name="cache">The cache object to be used.</param>
/// <param name="trace">The trace object to be used.</param>
/// <param name="statementBuilder">The statement builder object to be used.</param>
/// <returns>An enumerable list of data entity object.</returns>
public static IEnumerable <TEntity> QueryAllTableFunc <TEntity>(this IDbConnection connection,
string funcName,
object parameters,
IEnumerable <OrderField> orderBy = null,
string hints = null,
string cacheKey = null,
int cacheItemExpiration = Constant.DefaultCacheItemExpirationInMinutes,
int?commandTimeout = null,
IDbTransaction transaction = null,
ICache cache = null,
ITrace trace = null,
IStatementBuilder statementBuilder = null)
where TEntity : class
{
return(QueryAllTableFunc <TEntity>(connection: connection,
funcName: funcName,
parameters: parameters == null ? null : QueryField.Parse(parameters),
orderBy: orderBy,
hints: hints,
cacheKey: cacheKey,
cacheItemExpiration: cacheItemExpiration,
commandTimeout: commandTimeout,
transaction: transaction,
cache: cache,
trace: trace,
statementBuilder: statementBuilder));
}
/// <summary>
///
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="expression"></param>
/// <param name="isNot"></param>
/// <param name="isEqualsTo"></param>
/// <returns></returns>
private static QueryGroup ParseContainsForArrayOrList <TEntity>(MethodCallExpression expression,
bool isNot,
bool isEqualsTo)
where TEntity : class
{
// TODO: Refactor this
// Return null if there is no any arguments
if (expression.Arguments?.Any() != true)
{
return(null);
}
// Get the last arg
var last = expression
.Arguments
.LastOrDefault();
// Make sure the last arg is a member
if (last == null || last?.IsMember() == false)
{
throw new NotSupportedException($"Expression '{expression.ToString()}' is currently not supported.");
}
// Make sure it is a property info
var member = last.ToMember().Member;
if (member.IsPropertyInfo() == false)
{
throw new NotSupportedException($"Expression '{expression.ToString()}' is currently not supported.");
}
// Get the property
var property = member.ToPropertyInfo();
// Make sure the property is in the entity
if (PropertyCache.Get <TEntity>().FirstOrDefault(p => string.Equals(p.PropertyInfo.Name, property.Name, StringComparison.OrdinalIgnoreCase)) == null)
{
throw new InvalidExpressionException($"Invalid expression '{expression.ToString()}'. The property {property.Name} is not defined on a target type '{typeof(TEntity).FullName}'.");
}
// Get the values
var values = (object)null;
// Array/List Separation
if (expression.Object == null)
{
// Expecting an array
values = expression.Arguments.First().GetValue();
}
else
{
// Expecting a list here
values = expression.Object.GetValue();
}
// Add to query fields
var operation = (isNot == false && isEqualsTo == true) ? Operation.In : Operation.NotIn;
var queryField = new QueryField(PropertyMappedNameCache.Get(property), operation, values);
// Return the result
var queryGroup = new QueryGroup(queryField);
// Set the IsNot value
queryGroup.SetIsNot(isNot == true && isEqualsTo == false);
// Return the instance
return(queryGroup);
}
/// <summary>
///
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="expression"></param>
/// <returns></returns>
private static QueryGroup Parse <TEntity>(BinaryExpression expression)
where TEntity : class
{
var leftQueryGroup = (QueryGroup)null;
var rightQueryGroup = (QueryGroup)null;
var rightValue = (object)null;
var skipRight = false;
var isEqualsTo = true;
// TODO: Refactor this
/*
* LEFT
*/
// Get the value in the right
if (expression.IsExtractable())
{
rightValue = expression.Right.GetValue();
skipRight = true;
if (rightValue is bool)
{
isEqualsTo = Equals(rightValue, false) == false;
}
}
// Binary
if (expression.Left.IsBinary() == true)
{
leftQueryGroup = Parse <TEntity>(expression.Left.ToBinary());
leftQueryGroup.SetIsNot(isEqualsTo == false);
}
// Unary
else if (expression.Left.IsUnary() == true)
{
leftQueryGroup = Parse <TEntity>(expression.Left.ToUnary(), rightValue, expression.NodeType, isEqualsTo);
}
// MethodCall
else if (expression.Left.IsMethodCall())
{
leftQueryGroup = Parse <TEntity>(expression.Left.ToMethodCall(), false, isEqualsTo);
}
else
{
// Extractable
if (expression.IsExtractable())
{
var queryField = QueryField.Parse <TEntity>(expression);
leftQueryGroup = new QueryGroup(queryField);
skipRight = true;
}
}
// Identify the node type
if (expression.NodeType == ExpressionType.NotEqual)
{
leftQueryGroup.SetIsNot(leftQueryGroup.IsNot == isEqualsTo);
}
/*
* RIGHT
*/
if (skipRight == false)
{
// Binary
if (expression.Right.IsBinary() == true)
{
rightQueryGroup = Parse <TEntity>(expression.Right.ToBinary());
}
// Unary
else if (expression.Right.IsUnary() == true)
{
rightQueryGroup = Parse <TEntity>(expression.Right.ToUnary(), null, expression.NodeType, true);
}
// MethodCall
else if (expression.Right.IsMethodCall())
{
rightQueryGroup = Parse <TEntity>(expression.Right.ToMethodCall(), false, true);
}
// Return both of them
if (leftQueryGroup != null && rightQueryGroup != null)
{
var conjunction = (expression.NodeType == ExpressionType.OrElse) ? Conjunction.Or : Conjunction.And;
return(new QueryGroup(new[] { leftQueryGroup, rightQueryGroup }, conjunction));
}
}
// Return either one of them
return(leftQueryGroup ?? rightQueryGroup);
}
/// <summary>
///
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="expression"></param>
/// <param name="rightValue"></param>
/// <param name="expressionType"></param>
/// <param name="isNot"></param>
/// <param name="isEqualsTo"></param>
/// <returns></returns>
private static QueryGroup Parse <TEntity>(MemberExpression expression,
object rightValue,
ExpressionType expressionType,
bool isNot,
bool isEqualsTo)
where TEntity : class
{
var queryGroup = (QueryGroup)null;
var value = rightValue;
var isForBoolean = expression.Type == typeof(bool) &&
(expressionType == ExpressionType.Not || expressionType == ExpressionType.AndAlso || expressionType == ExpressionType.OrElse);
var ignoreIsNot = false;
// Handle for boolean
if (value == null)
{
if (isForBoolean)
{
value = false;
ignoreIsNot = true;
}
else
{
value = expression.GetValue();
}
}
// Check if there are values
if (value != null)
{
// Specialized for enum
if (expression.Type.IsEnum)
{
value = Enum.ToObject(expression.Type, value);
}
// Create a new field
var field = (QueryField)null;
if (isForBoolean)
{
field = new QueryField(expression.Member.GetMappedName(),
value);
ignoreIsNot = true;
}
else
{
field = new QueryField(expression.Member.GetMappedName(),
QueryField.GetOperation(expressionType),
value);
}
// Set the query group
queryGroup = new QueryGroup(field);
// Set the query group IsNot property
if (ignoreIsNot == false)
{
queryGroup.SetIsNot(isEqualsTo == false);
}
}
// Return the result
return(queryGroup);
}
/// <summary>
/// Deletes an existing data from the database.
/// </summary>
/// <param name="where">The query expression to be used.</param>
/// <param name="transaction">The transaction to be used.</param>
/// <returns>The number of rows affected by the execution.</returns>
public int Delete(QueryField where,
IDbTransaction transaction = null)
{
return(DbRepository.Delete <TEntity>(where : where,
transaction: transaction));
}
/// <summary>
/// Deletes an existing data from the database in an asynchronous way.
/// </summary>
/// <param name="where">The query expression to be used.</param>
/// <param name="transaction">The transaction to be used.</param>
/// <returns>The number of rows affected by the execution.</returns>
public Task <int> DeleteAsync(QueryField where,
IDbTransaction transaction = null)
{
return(DbRepository.DeleteAsync <TEntity>(where : where,
transaction: transaction));
}
/// <summary>
/// Appends a word HAVING COUNT and a conditional field to the SQL Query Statement.
/// </summary>
/// <param name="queryField">The conditional field object used for composition.</param>
/// <param name="index">The parameter index.</param>
/// <param name="dbSetting">The currently in used <see cref="IDbSetting"/> object.</param>
/// <returns>The current instance.</returns>
public QueryBuilder HavingCountFrom(QueryField queryField,
int index,
IDbSetting dbSetting)
{
return(Append(string.Concat("HAVING COUNT(", queryField.Field.Name, ") ", queryField.GetOperationText(), ", ", queryField.AsParameter(index, dbSetting))));
}
/// <summary>
/// Appends a word HAVING COUNT and a conditional field to the SQL Query Statement.
/// </summary>
/// <param name="queryField">The conditional field object used for composition.</param>
/// <returns>The current instance.</returns>
public QueryBuilder <TEntity> HavingCountFrom(QueryField queryField)
{
return(Append(string.Concat("HAVING COUNT(", queryField.Field.AsField(), ") ", queryField.GetOperationText(), ", ", queryField.AsParameter())));
}
