/// <summary>
/// Queries a data from the database in an asynchronous way.
/// </summary>
/// <param name="tableName">The name of the target table.</param>
/// <param name="where">The query expression to be used.</param>
/// <param name="fields">The list of fields to be queried.</param>
/// <param name="orderBy">The order definition of the fields to be used.</param>
/// <param name="top">The top number of data to be used.</param>
/// <param name="hints">The table hints to be used. See <see cref="SqlServerTableHints"/> class.</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 the repository to query from the database.
/// </param>
/// <param name="transaction">The transaction to be used.</param>
/// <returns>An enumerable list of dynamic object.</returns>
public async Task<IEnumerable<dynamic>> QueryAsync(string tableName,
QueryGroup where = null,
IEnumerable<Field> fields = null,
IEnumerable<OrderField> orderBy = null,
int? top = 0,
string hints = null,
string cacheKey = null,
IDbTransaction transaction = null)
{
// Create a connection
var connection = (transaction?.Connection ?? CreateConnection());
try
{
// Call the method
return await connection.QueryAsync(tableName: tableName,
where: where,
fields: fields,
orderBy: orderBy,
top: top,
hints: hints,
cacheKey: cacheKey,
cacheItemExpiration: CacheItemExpiration,
commandTimeout: CommandTimeout,
transaction: transaction,
cache: Cache,
trace: Trace,
statementBuilder: StatementBuilder);
}
catch
{
// Throw back the error
throw;
}
finally
{
// Dispose the connection
DisposeConnectionForPerCall(connection, transaction);
}
}
/// <summary>
/// Creates a SQL Statement for update operation.
/// </summary>
/// <param name="queryBuilder">The query builder to be used.</param>
/// <param name="tableName">The name of the target table.</param>
/// <param name="fields">The list of fields to be updated.</param>
/// <param name="where">The query expression.</param>
/// <param name="primaryField">The primary field from the database.</param>
/// <param name="identityField">The identity field from the database.</param>
/// <returns>A sql statement for update operation.</returns>
public string CreateUpdate(QueryBuilder queryBuilder,
string tableName,
IEnumerable <Field> fields,
QueryGroup where = null,
DbField primaryField = null,
DbField identityField = null)
{
// Ensure with guards
GuardTableName(tableName);
GuardPrimary(primaryField);
GuardIdentity(identityField);
// Append the proper prefix
where?.PrependAnUnderscoreAtTheParameters();
// Gets the updatable fields
var updatableFields = fields
.Where(f => f.UnquotedName.ToLower() != primaryField?.UnquotedName.ToLower() &&
f.UnquotedName.ToLower() != identityField?.UnquotedName.ToLower());
// Check if there are updatable fields
if (updatableFields?.Any() != true)
{
throw new InvalidOperationException("The list of updatable fields cannot be null or empty.");
}
// Build the query
(queryBuilder ?? new QueryBuilder())
.Clear()
.Update()
.TableNameFrom(tableName)
.Set()
.FieldsAndParametersFrom(updatableFields)
.WhereFrom(where)
.End();
// Return the query
return(queryBuilder.GetString());
}
/// <summary>
/// Creates a SQL Statement for count operation.
/// </summary>
/// <param name="queryBuilder">The query builder to be used.</param>
/// <param name="tableName">The name of the target table.</param>
/// <param name="where">The query expression.</param>
/// <param name="hints">The table hints to be used. See <see cref="SqlTableHints"/> class.</param>
/// <returns>A sql statement for count operation.</returns>
public string CreateCount(QueryBuilder queryBuilder,
string tableName,
QueryGroup where = null,
string hints = null)
{
// Ensure with guards
GuardTableName(tableName);
// Build the query
(queryBuilder ?? new QueryBuilder())
.Clear()
.Select()
.CountBig()
.WriteText("(1) AS [Counted]")
.From()
.TableNameFrom(tableName)
.HintsFrom(hints)
.WhereFrom(where)
.End();
// Return the query
return(queryBuilder.GetString());
}
/// <summary>
/// Queries a data from the database.
/// </summary>
/// <typeparam name="TEntity">The type of the data entity object.</typeparam>
/// <param name="where">The query expression to be used.</param>
/// <param name="orderBy">The order definition of the fields to be used.</param>
/// <param name="top">The top number of data to be used.</param>
/// <param name="hints">The table hints to be used. See <see cref="SqlServerTableHints"/> class.</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 the repository to query from the database.
/// </param>
/// <param name="transaction">The transaction to be used.</param>
/// <returns>An enumerable list of data entity object.</returns>
public IEnumerable<TEntity> Query<TEntity>(QueryGroup where = null,
IEnumerable<OrderField> orderBy = null,
int? top = 0,
string hints = null,
string cacheKey = null,
IDbTransaction transaction = null)
where TEntity : class
{
// Create a connection
var connection = (transaction?.Connection ?? CreateConnection());
try
{
// Call the method
return connection.Query<TEntity>(where: where,
orderBy: orderBy,
top: top,
hints: hints,
cacheKey: cacheKey,
cacheItemExpiration: CacheItemExpiration,
commandTimeout: CommandTimeout,
transaction: transaction,
cache: Cache,
trace: Trace,
statementBuilder: StatementBuilder);
}
catch
{
// Throw back the error
throw;
}
finally
{
// Dispose the connection
DisposeConnectionForPerCall(connection, transaction);
}
}
/// <summary>
/// Check whether the rows are existing in the table.
/// </summary>
/// <typeparam name="TEntity">The type of the data entity.</typeparam>
/// <param name="where">The query expression to be used.</param>
/// <param name="hints">The table hints to be used.</param>
/// <param name="transaction">The transaction to be used.</param>
/// <returns>A boolean value that indicates whether the rows are existing in the table.</returns>
public bool Exists <TEntity>(QueryGroup where,
string hints = null,
IDbTransaction transaction = null)
where TEntity : class
{
// Create a connection
var connection = (transaction?.Connection ?? CreateConnection());
try
{
// Call the method
return(connection.Exists <TEntity>(where : where,
hints: hints,
commandTimeout: CommandTimeout,
transaction: transaction,
trace: Trace,
statementBuilder: StatementBuilder));
}
finally
{
// Dispose the connection
DisposeConnectionForPerCall(connection, transaction);
}
}
/// <summary>
/// Creates a SQL Statement for repository query operation that is meant for SQL Server.
/// </summary>
/// <typeparam name="TEntity">
/// The data entity object bound for the SQL Statement to be created.
/// </typeparam>
/// <param name="queryBuilder">An instance of query builder used to build the SQL statement.</param>
/// <param name="where">The query expression for SQL statement.</param>
/// <param name="orderBy">The list of fields to be used for ordering in SQL Statement composition.</param>
/// <param name="top">The number of rows to be returned by the query operation in SQL Statement composition.</param>
/// <returns>A string containing the composed SQL Statement for query operation.</returns>
public string CreateQuery <TEntity>(QueryBuilder <TEntity> queryBuilder, QueryGroup where = null, IEnumerable <OrderField> orderBy = null, int?top = null)
where TEntity : class
{
var properties = PropertyCache.Get <TEntity>(Command.Query);
if (properties?.Any() == false)
{
throw new InvalidOperationException($"No queryable fields found from type '{typeof(TEntity).FullName}'.");
}
var fields = properties?.Select(property => new Field(property.GetMappedName().AsQuoted(true)));
queryBuilder = queryBuilder ?? new QueryBuilder <TEntity>();
queryBuilder
.Clear()
.Select()
.TopFrom(top)
.FieldsFrom(fields)
.From()
.TableName()
.WhereFrom(where)
.OrderByFrom(orderBy)
.End();
return(queryBuilder.GetString());
}
/// <summary>
/// Appends a word WHERE and the stringified values of the <see cref="QueryGroup"/> to the SQL Query Statement.
/// </summary>
/// <param name="queryGroup">The query group to be stringified.</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 WhereFrom(QueryGroup queryGroup,
int index,
IDbSetting dbSetting)
{
return((queryGroup?.GetFields(true)?.Any() == true) ? Append(string.Concat("WHERE ", queryGroup.GetString(index, dbSetting))) : this);
}
/// <summary>
/// Appends a word WHERE and the stringified values of the <see cref="QueryGroup"/> to the SQL Query Statement.
/// </summary>
/// <param name="queryGroup">The query group to be stringified.</param>
/// <param name="dbSetting">The currently in used <see cref="IDbSetting"/> object.</param>
/// <returns>The current instance.</returns>
public QueryBuilder WhereFrom(QueryGroup queryGroup,
IDbSetting dbSetting)
{
return(WhereFrom(queryGroup, 0, dbSetting));
}
/// <summary>
/// Appends a word WHERE and the stringified values of the <see cref="QueryGroup"/> to the SQL Query Statement.
/// </summary>
/// <param name="queryGroup">The query group to be stringified.</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 WhereFrom(QueryGroup queryGroup,
int index,
IDbSetting dbSetting)
{
return((queryGroup != null) ? Append(string.Concat("WHERE ", queryGroup.GetString(index, dbSetting))) : this);
}
/// <summary>
/// Creates a SQL Statement for repository inline update operation that is meant for SQL Server.
/// </summary>
/// <typeparam name="TEntity">
/// The data entity object bound for the SQL Statement to be created.
/// </typeparam>
/// <param name="queryBuilder">An instance of query builder used to build the SQL statement.</param>
/// <param name="fields">The list of the fields to be a part of inline update operation in SQL Statement composition.</param>
/// <param name="where">The query expression for SQL statement.</param>
/// <param name="overrideIgnore">
/// Set to true if the defined <see cref="IgnoreAttribute"/> would likely
/// be ignored on the inline update operation in SQL Statement composition.
/// </param>
/// <returns>A string containing the composed SQL Statement for inline-update operation.</returns>
public string CreateInlineUpdate <TEntity>(QueryBuilder <TEntity> queryBuilder, IEnumerable <Field> fields = null,
QueryGroup where = null, bool?overrideIgnore = false)
where TEntity : class
{
// Check for the fields presence
if (fields == null)
{
throw new NullReferenceException("The target fields must be present.");
}
// Check for all the fields
var properties = PropertyCache.Get <TEntity>(Command.None)?
.Select(property => property.GetMappedName());
var unmatchesFields = fields?.Where(field =>
properties?.FirstOrDefault(property =>
field.Name.ToLower() == property.ToLower()) == null);
if (unmatchesFields?.Count() > 0)
{
throw new InvalidOperationException($"The fields '{unmatchesFields.Select(field => field.AsField()).Join(", ")}' are not " +
$"present at type '{typeof(TEntity).FullName}'.");
}
// Important fields
var primary = PrimaryKeyCache.Get <TEntity>();
var identity = IdentityCache.Get <TEntity>();
if (identity != null && identity != primary)
{
throw new InvalidOperationException($"Identity property must be the primary property for type '{typeof(TEntity).FullName}'.");
}
// Variables
var hasFields = fields?.Any(field => field.Name.ToLower() != primary?.GetMappedName().ToLower()) == true;
// Check if there are fields
if (hasFields == false)
{
throw new InvalidOperationException($"No inline updatable fields for object '{ClassMappedNameCache.Get<TEntity>()}'.");
}
// Append prefix to all parameters
where?.AppendParametersPrefix();
// Check for the unmatches
if (overrideIgnore == false)
{
var updateableFields = PropertyCache.Get <TEntity>(Command.Update)
.Select(property => property.GetMappedName());
var inlineUpdateableFields = PropertyCache.Get <TEntity>(Command.InlineUpdate)
.Select(property => property.GetMappedName())
.Where(field => field.ToLower() != primary?.GetMappedName().ToLower() && updateableFields.Contains(field));
var unmatchesProperties = fields?.Where(field =>
inlineUpdateableFields?.FirstOrDefault(property => field.Name.ToLower() == property.ToLower()) == null);
if (unmatchesProperties.Count() > 0)
{
throw new InvalidOperationException($"The fields '{unmatchesProperties.Select(field => field.AsField()).Join(", ")}' are not " +
$"inline updateable for object '{ClassMappedNameCache.Get<TEntity>()}'.");
}
}
// Build the SQL Statement
queryBuilder = queryBuilder ?? new QueryBuilder <TEntity>();
queryBuilder
.Clear()
.Update()
.TableName()
.Set()
.FieldsAndParametersFrom(fields)
.WhereFrom(where)
.End();
// Return the query
return(queryBuilder.GetString());
}
/// <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>
/// Creates a SQL Statement for repository batch-query operation that is meant for SQL Server.
/// </summary>
/// <typeparam name="TEntity">
/// The data entity object bound for the SQL Statement to be created.
/// </typeparam>
/// <param name="queryBuilder">An instance of query builder used to build the SQL statement.</param>
/// <param name="where">The query expression for SQL statement.</param>
/// <param name="page">The page of the batch.</param>
/// <param name="rowsPerBatch">The number of rows per batch.</param>
/// <param name="orderBy">The list of fields used for ordering.</param>
/// <returns>A string containing the composed SQL Statement for batch-query operation.</returns>
public string CreateBatchQuery <TEntity>(QueryBuilder <TEntity> queryBuilder, QueryGroup where = null, int?page = null, int?rowsPerBatch = null, IEnumerable <OrderField> orderBy = null)
where TEntity : class
{
var queryProperties = PropertyCache.Get <TEntity>(Command.Query);
var batchQueryProperties = PropertyCache.Get <TEntity>(Command.BatchQuery)
.Where(property => queryProperties.Contains(property));
var fields = batchQueryProperties.Select(property => new Field(property.GetMappedName()));
// Validate the fields
if (fields?.Any() == false)
{
throw new InvalidOperationException($"No batch queryable fields found from type '{typeof(TEntity).FullName}'.");
}
// Build the SQL Statement
queryBuilder = queryBuilder ?? new QueryBuilder <TEntity>();
queryBuilder
.Clear()
.With()
.WriteText("CTE")
.As()
.OpenParen()
.Select()
.RowNumber()
.Over()
.OpenParen()
.OrderByFrom(orderBy)
.CloseParen()
.As("[RowNumber],")
.FieldsFrom(fields)
.From()
.TableName()
.WhereFrom(where)
.CloseParen()
.Select()
.FieldsFrom(fields)
.From()
.WriteText("CTE")
.WriteText($"WHERE ([RowNumber] BETWEEN {(page * rowsPerBatch) + 1} AND {(page + 1) * rowsPerBatch})")
.OrderByFrom(orderBy)
.End();
// Return the query
return(queryBuilder.GetString());
}
/// <summary>
/// Appends a word WHERE and the stringified values of the Query Group to the SQL Query Statement.
/// </summary>
/// <param name="queryGroup">The query group to be stringified.</param>
/// <returns>The current instance.</returns>
public QueryBuilder <TEntity> WhereFrom(QueryGroup queryGroup)
{
return((queryGroup != null) ? Append($"WHERE {queryGroup.FixParameters().GetString()}") : this);
}
/// <summary>
/// Appends a word WHERE and the stringified values of the Query Group to the SQL Query Statement.
/// </summary>
/// <param name="queryGroup">The query group to be stringified.</param>
/// <returns>The current instance.</returns>
public QueryBuilder <TEntity> WhereFrom(QueryGroup queryGroup)
{
return((queryGroup != null) ? Append(string.Concat("WHERE ", queryGroup.Fix().GetString())) : this);
}
/// <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(QueryGroup where,
IDbTransaction transaction = null)
{
return(DbRepository.DeleteAsync <TEntity>(where : where,
transaction: transaction));
}
/// <summary>
/// Creates a SQL Statement for batch query operation.
/// </summary>
/// <param name="queryBuilder">The query builder to be used.</param>
/// <param name="tableName">The name of the target table.</param>
/// <param name="fields">The list of fields to be queried.</param>
/// <param name="page">The page of the batch.</param>
/// <param name="rowsPerBatch">The number of rows per batch.</param>
/// <param name="orderBy">The list of fields for ordering.</param>
/// <param name="where">The query expression.</param>
/// <param name="hints">The table hints to be used. See <see cref="SqlTableHints"/> class.</param>
/// <returns>A sql statement for batch query operation.</returns>
public string CreateBatchQuery(QueryBuilder queryBuilder,
string tableName,
IEnumerable <Field> fields,
int page,
int rowsPerBatch,
IEnumerable <OrderField> orderBy = null,
QueryGroup where = null,
string hints = null)
{
// Ensure with guards
GuardTableName(tableName);
// There should be fields
if (fields?.Any() != true)
{
throw new NullReferenceException($"The list of queryable fields must not be null for '{tableName}'.");
}
// Validate order by
if (orderBy == null || orderBy?.Any() != true)
{
throw new InvalidOperationException("The argument 'orderBy' is required.");
}
// Validate the page
if (page < 0)
{
throw new InvalidOperationException("The page must be equals or greater than 0.");
}
// Validate the page
if (rowsPerBatch < 1)
{
throw new InvalidOperationException($"The rows per batch must be equals or greater than 1.");
}
// Build the query
(queryBuilder ?? new QueryBuilder())
.Clear()
.With()
.WriteText("CTE")
.As()
.OpenParen()
.Select()
.RowNumber()
.Over()
.OpenParen()
.OrderByFrom(orderBy)
.CloseParen()
.As("[RowNumber],")
.FieldsFrom(fields)
.From()
.TableNameFrom(tableName)
.HintsFrom(hints)
.WhereFrom(where)
.CloseParen()
.Select()
.FieldsFrom(fields)
.From()
.WriteText("CTE")
.WriteText(string.Concat("WHERE ([RowNumber] BETWEEN ", (page * rowsPerBatch) + 1, " AND ", (page + 1) * rowsPerBatch, ")"))
.OrderByFrom(orderBy)
.End();
// Return the query
return(queryBuilder.GetString());
}
/// <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(QueryGroup where,
IDbTransaction transaction = null)
{
return(DbRepository.Delete <TEntity>(where : where,
transaction: transaction));
}
/// <summary>
/// Appends a word WHERE and the stringified values of the <see cref="QueryGroup"/> to the SQL Query Statement.
/// </summary>
/// <param name="queryGroup">The query group to be stringified.</param>
/// <returns>The current instance.</returns>
public QueryBuilder WhereFrom(QueryGroup queryGroup)
{
return(WhereFrom(queryGroup, 0));
}
/// <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>
/// Creates an instance of <see cref="QueryGroupTypeMap"/> class.
/// </summary>
/// <param name="queryGroup">The <see cref="QueryGroup"/> object.</param>
/// <param name="type">The type where the <see cref="QueryGroup"/> object is mapped.</param>
public QueryGroupTypeMap(QueryGroup queryGroup, Type type)
{
QueryGroup = queryGroup;
MappedType = type;
}
/// <summary>
/// Creates a SQL Statement for repository <i>BatchQuery</i> operation that is meant for SQL Server.
/// </summary>
/// <typeparam name="TEntity">
/// The <i>DataEntity</i> object bound for the SQL Statement to be created.
/// </typeparam>
/// <param name="queryBuilder">An instance of query builder used to build the SQL statement.</param>
/// <param name="where">The query expression for SQL statement.</param>
/// <param name="page">The page of the batch.</param>
/// <param name="rowsPerBatch">The number of rows per batch.</param>
/// <param name="orderBy">The list of fields used for ordering.</param>
/// <returns>A string containing the composed SQL Statement for <i>BatchQuery</i> operation.</returns>
public string CreateBatchQuery <TEntity>(QueryBuilder <TEntity> queryBuilder, QueryGroup where, int page, int rowsPerBatch, IEnumerable <OrderField> orderBy)
where TEntity : DataEntity
{
var queryProperties = DataEntityExtension.GetPropertiesFor <TEntity>(Command.Query);
var batchQueryProperties = DataEntityExtension.GetPropertiesFor <TEntity>(Command.BatchQuery)
.Where(property => queryProperties.Contains(property));
var fields = batchQueryProperties.Select(property => new Field(property.GetMappedName()));
// Build the SQL Statement
queryBuilder = queryBuilder ?? new QueryBuilder <TEntity>();
queryBuilder
.Clear()
.With()
.WriteText("CTE")
.As()
.OpenParen()
.Select()
.RowNumber()
.Over()
.OpenParen()
.OrderByFrom(orderBy)
.CloseParen()
.As("[RowNumber],")
.FieldsFrom(fields)
.From()
.TableFrom(Command.BatchQuery)
.WhereFrom(where)
.CloseParen()
.Select()
.FieldsFrom(fields)
.From()
.WriteText("CTE")
.WriteText($"WHERE ([RowNumber] BETWEEN {(page * rowsPerBatch) + 1} AND {(page + 1) * rowsPerBatch})")
.OrderByFrom(orderBy)
.End();
// Return the query
return(queryBuilder.GetString());
}
// 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))
{
// Throws an exception if conjunction is not a conjunction type
if (property.PropertyType != typeof(Conjunction))
{
throw new InvalidQueryExpressionException($"Conjunction field must be of type {typeof(Conjunction).FullName}.");
}
// 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 InvalidQueryExpressionException($"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 InvalidQueryExpressionException($"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 InvalidQueryExpressionException($"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 InvalidQueryExpressionException($"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).FixParameters());
}
/// <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);
}
