/// <summary>
/// Creates a SQL Statement for repository insert 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="isPrimaryIdentity">A boolean value indicates whether the primary key is identity in the database.</param>
/// <returns>A string containing the composed SQL Statement for insert operation.</returns>
internal string CreateInsert <TEntity>(QueryBuilder <TEntity> queryBuilder, bool?isPrimaryIdentity = null)
where TEntity : class
{
var primary = PrimaryKeyCache.Get <TEntity>();
var fields = PropertyCache.Get <TEntity>(Command.Insert)
.Where(property => !(isPrimaryIdentity == true && property.IsPrimary() == true))
.Select(property => new Field(property.GetMappedName()));
// Build the SQL Statement
queryBuilder = queryBuilder ?? new QueryBuilder <TEntity>();
queryBuilder
.Clear()
.Insert()
.Into()
.TableName()
.OpenParen()
.FieldsFrom(fields)
.CloseParen()
.Values()
.OpenParen()
.ParametersFrom(fields)
.CloseParen()
.End();
var result = isPrimaryIdentity == true ? "SCOPE_IDENTITY()" : (primary != null) ? $"@{primary.GetMappedName()}" : "NULL";
queryBuilder
.Select()
.WriteText(result)
.As("[Result]")
.End();
// Return the query
return(queryBuilder.GetString());
}
/// <summary>
/// Creates a SQL Statement for repository count 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="hints">The hints to be used to optimze the query operation.</param>
/// <returns>A string containing the composed SQL Statement for count operation.</returns>
public string CreateCount <TEntity>(QueryBuilder <TEntity> queryBuilder,
QueryGroup where = null,
string hints = null)
where TEntity : class
{
queryBuilder = queryBuilder ?? new QueryBuilder <TEntity>();
queryBuilder
.Clear()
.Select()
.CountBig()
.WriteText("(1) AS [Counted]")
.From()
.TableName();
// Build the query optimizers
if (hints != null)
{
queryBuilder
.WriteText(hints);
}
// Build the filter and ordering
queryBuilder
.WhereFrom(where)
.End();
// Return the query
return(queryBuilder.GetString());
}
/// <summary>
/// Creates a SQL Statement for repository 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="where">The query expression for SQL statement.</param>
/// <returns>A string containing the composed SQL Statement for update operation.</returns>
public string CreateUpdate <TEntity>(QueryBuilder <TEntity> queryBuilder, QueryGroup where = null)
where TEntity : class
{
var properties = PropertyCache.Get <TEntity>(Command.Update);
if (properties?.Any() == false)
{
throw new InvalidOperationException($"No updateable fields found from type '{typeof(TEntity).FullName}'.");
}
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}'.");
}
var fields = properties
.Where(property => property.IsPrimary() == false && property.IsIdentity() == false)
.Select(p => new Field(p.GetMappedName()));
where?.AppendParametersPrefix();
queryBuilder = queryBuilder ?? new QueryBuilder <TEntity>();
queryBuilder
.Clear()
.Update()
.TableName()
.Set()
.FieldsAndParametersFrom(fields)
.WhereFrom(where)
.End();
return(queryBuilder.GetString());
}
/// <summary>
/// Creates a SQL Statement for repository <i>Insert</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="isPrimaryIdentity">A boolean value indicates whether the primary key is identity in the database.</param>
/// <returns>A string containing the composed SQL Statement for <i>Insert</i> operation.</returns>
internal string CreateInsert <TEntity>(QueryBuilder <TEntity> queryBuilder, bool isPrimaryIdentity)
where TEntity : DataEntity
{
var primary = DataEntityExtension.GetPrimaryProperty <TEntity>();
var fields = DataEntityExtension.GetPropertiesFor <TEntity>(Command.Insert)
.Where(property => !(isPrimaryIdentity && property == primary))
.Select(p => new Field(p.Name));
// Build the SQL Statement
queryBuilder = queryBuilder ?? new QueryBuilder <TEntity>();
queryBuilder
.Clear()
.Insert()
.Into()
.TableFrom(Command.Insert)
.OpenParen()
.FieldsFrom(fields)
.CloseParen()
.Values()
.OpenParen()
.ParametersFrom(fields)
.CloseParen()
.End();
var result = isPrimaryIdentity ? "SCOPE_IDENTITY()" : (primary != null) ? $"@{primary.GetMappedName()}" : "NULL";
queryBuilder
.Select()
.WriteText(result)
.As("[Result]")
.End();
// Return the query
return(queryBuilder.GetString());
}
/// <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>
/// <param name="hints">The hints to be used to optimze the query operation.</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,
string hints = null)
where TEntity : class
{
var fields = PropertyCache.Get <TEntity>().Select(property => new Field(property.GetMappedName()));
// There should be fields
if (fields == null || 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();
// Build the query optimizers
if (hints != null)
{
queryBuilder
.WriteText(hints);
}
// Build the filter and ordering
queryBuilder
.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>
/// Creates a SQL Statement for repository <i>Truncate</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>
/// <returns>A string containing the composed SQL Statement for <i>Truncate</i> operation.</returns>
public string CreateTruncate <TEntity>(QueryBuilder <TEntity> queryBuilder)
where TEntity : DataEntity
{
queryBuilder = queryBuilder ?? new QueryBuilder <TEntity>();
queryBuilder
.Clear()
.Truncate()
.Table()
.TableFrom(Command.Delete)
.End();
return(queryBuilder.GetString());
}
/// <summary>
/// Creates a SQL Statement for repository delete-all 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>
/// <returns>A string containing the composed SQL Statement for delete-all operation.</returns>
public string CreateDeleteAll <TEntity>(QueryBuilder <TEntity> queryBuilder)
where TEntity : class
{
queryBuilder = queryBuilder ?? new QueryBuilder <TEntity>();
queryBuilder
.Clear()
.Delete()
.From()
.TableName()
.End();
return(queryBuilder.GetString());
}
/// <summary>
/// Creates a SQL Statement for repository truncate 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>
/// <returns>A string containing the composed SQL Statement for truncate operation.</returns>
public string CreateTruncate <TEntity>(QueryBuilder <TEntity> queryBuilder)
where TEntity : class
{
queryBuilder = queryBuilder ?? new QueryBuilder <TEntity>();
queryBuilder
.Clear()
.Truncate()
.Table()
.TableName()
.End();
return(queryBuilder.GetString());
}
/// <summary>
/// Creates a SQL Statement for repository delete 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>
/// <returns>A string containing the composed SQL Statement for delete operation.</returns>
public string CreateDelete <TEntity>(QueryBuilder <TEntity> queryBuilder, QueryGroup where = null)
where TEntity : class
{
queryBuilder = queryBuilder ?? new QueryBuilder <TEntity>();
queryBuilder
.Clear()
.Delete()
.From()
.TableName()
.WhereFrom(where)
.End();
return(queryBuilder.GetString());
}
/// <summary>
/// Creates a SQL Statement for repository <i>Delete</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>
/// <returns>A string containing the composed SQL Statement for <i>Delete</i> operation.</returns>
public string CreateDelete <TEntity>(QueryBuilder <TEntity> queryBuilder, QueryGroup where)
where TEntity : DataEntity
{
queryBuilder = queryBuilder ?? new QueryBuilder <TEntity>();
queryBuilder
.Clear()
.Delete()
.From()
.TableFrom(Command.Delete)
.WhereFrom(where)
.End();
return(queryBuilder.GetString());
}
/// <summary>
/// Creates a SQL Statement for repository count 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>
/// <returns>A string containing the composed SQL Statement for count operation.</returns>
public string CreateCount <TEntity>(QueryBuilder <TEntity> queryBuilder, QueryGroup where = null)
where TEntity : class
{
queryBuilder = queryBuilder ?? new QueryBuilder <TEntity>();
queryBuilder
.Clear()
.Select()
.CountBig()
.WriteText("(1) AS [Counted]")
.From()
.TableName()
.WhereFrom(where)
.End();
return(queryBuilder.GetString());
}
/// <summary>
/// Creates a SQL Statement for 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.</param>
/// <param name="where">The query expression.</param>
/// <param name="orderBy">The list of fields for ordering.</param>
/// <param name="top">The number of rows to be returned.</param>
/// <param name="hints">The table hints to be used. See <see cref="SqlTableHints"/> class.</param>
/// <returns>A sql statement for query operation.</returns>
public string CreateQuery(QueryBuilder queryBuilder,
string tableName,
IEnumerable <Field> fields,
QueryGroup where = null,
IEnumerable <OrderField> orderBy = null,
int?top = 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}'.");
}
if (orderBy != null)
{
// Check if the order fields are present in the given fields
var unmatchesOrderFields = orderBy?.Where(orderField =>
fields?.FirstOrDefault(f =>
orderField.Name.ToLower() == f.Name.ToLower()) == null);
// Throw an error we found any unmatches
if (unmatchesOrderFields?.Any() == true)
{
throw new InvalidOperationException($"The order fields '{unmatchesOrderFields.Select(field => field.Name).Join(", ")}' are not " +
$"present at the given fields '{fields.Select(field => field.Name).Join(", ")}'.");
}
}
// Build the query
(queryBuilder ?? new QueryBuilder())
.Clear()
.Select()
.TopFrom(top)
.FieldsFrom(fields)
.From()
.TableNameFrom(tableName)
.HintsFrom(hints)
.WhereFrom(where)
.OrderByFrom(orderBy)
.End();
// Return the query
return(queryBuilder.GetString());
}
/// <summary>
/// Creates a SQL Statement for repository truncate 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>
/// <returns>A string containing the composed SQL Statement for truncate operation.</returns>
public string CreateTruncate <TEntity>(QueryBuilder <TEntity> queryBuilder)
where TEntity : class
{
queryBuilder = queryBuilder ?? new QueryBuilder <TEntity>();
// Build the SQL Statement
queryBuilder
.Clear()
.Truncate()
.Table()
.TableName()
.End();
// Return the query
return(queryBuilder.GetString());
}
/// <summary>
/// Creates a SQL Statement for repository <i>Query</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="top">The number of rows to be returned by the <i>Query</i> operation in SQL Statement composition.</param>
/// <param name="orderBy">The list of fields to be used for ordering in SQL Statement composition.</param>
/// <returns>A string containing the composed SQL Statement for <i>Query</i> operation.</returns>
public string CreateQuery <TEntity>(QueryBuilder <TEntity> queryBuilder, QueryGroup where, int?top = 0, IEnumerable <OrderField> orderBy = null)
where TEntity : DataEntity
{
queryBuilder = queryBuilder ?? new QueryBuilder <TEntity>();
queryBuilder
.Clear()
.Select()
.TopFrom(top)
.FieldsFrom(Command.Query)
.From()
.TableFrom(Command.Query)
.WhereFrom(where)
.OrderByFrom(orderBy)
.End();
return(queryBuilder.GetString());
}
/// <summary>
/// Creates a SQL Statement for delete-all operation.
/// </summary>
/// <param name="queryBuilder">The query builder to be used.</param>
/// <param name="tableName">The name of the target table.</param>
/// <returns>A sql statement for delete-all operation.</returns>
public string CreateDeleteAll(QueryBuilder queryBuilder,
string tableName)
{
// Ensure with guards
GuardTableName(tableName);
// Build the query
(queryBuilder ?? new QueryBuilder())
.Clear()
.Delete()
.From()
.TableNameFrom(tableName)
.End();
// Return the query
return(queryBuilder.GetString());
}
/// <summary>
/// Creates a SQL Statement for truncate operation.
/// </summary>
/// <param name="queryBuilder">The query builder to be used.</param>
/// <param name="tableName">The name of the target table.</param>
/// <returns>A sql statement for truncate operation.</returns>
public string CreateTruncate(QueryBuilder queryBuilder,
string tableName)
{
// Guard the target table
GuardTableName(tableName);
// Build the query
(queryBuilder ?? new QueryBuilder())
.Clear()
.Truncate()
.Table()
.TableNameFrom(tableName)
.End();
// Return the query
return(queryBuilder.GetString());
}
/// <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>
/// Creates a SQL Statement for repository <i>InlineUpdate</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="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 <i>true</i> if the defined <i>RepoDb.Attributes.IgnoreAttribute</i> would likely
/// be ignored on the inline update operation in SQL Statement composition.
/// </param>
/// <returns>A string containing the composed SQL Statement for <i>InlineUpdate</i> operation.</returns>
public string CreateInlineUpdate <TEntity>(QueryBuilder <TEntity> queryBuilder, IEnumerable <Field> fields,
QueryGroup where, bool?overrideIgnore = false)
where TEntity : DataEntity
{
var primary = DataEntityExtension.GetPrimaryProperty <TEntity>();
var hasFields = fields?.Any(field => field.Name.ToLower() != primary?.GetMappedName().ToLower());
// Check if there are fields
if (hasFields == false)
{
throw new InvalidOperationException($"No inline updatable fields found at type '{typeof(TEntity).FullName}'.");
}
// Get the target properties
var updateableProperties = DataEntityExtension.GetPropertiesFor <TEntity>(Command.Update);
var inlineUpdateableProperties = DataEntityExtension.GetPropertiesFor <TEntity>(Command.InlineUpdate)
.Where(property => property != primary && updateableProperties.Contains(property))
.Select(property => property.GetMappedName());
// Check for the unmatches
if (overrideIgnore == false)
{
var unmatchesProperties = fields?.Where(field =>
inlineUpdateableProperties?.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 '{DataEntityExtension.GetMappedName<TEntity>(Command.InlineUpdate)}'.");
}
}
// Build the SQL Statement
queryBuilder = queryBuilder ?? new QueryBuilder <TEntity>();
queryBuilder
.Clear()
.Update()
.TableFrom(Command.InlineUpdate)
.Set()
.FieldsAndParametersFrom(fields)
.WhereFrom(where)
.End();
// Return the query
return(queryBuilder.GetString());
}
/// <summary>
/// Creates a SQL Statement for repository <i>Update</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>
/// <returns>A string containing the composed SQL Statement for <i>Update</i> operation.</returns>
public string CreateUpdate <TEntity>(QueryBuilder <TEntity> queryBuilder, QueryGroup where)
where TEntity : DataEntity
{
queryBuilder = queryBuilder ?? new QueryBuilder <TEntity>();
var fields = DataEntityExtension.GetPropertiesFor <TEntity>(Command.Update)
.Where(property => property != DataEntityExtension.GetPrimaryProperty <TEntity>())
.Select(p => new Field(p.Name));
queryBuilder
.Clear()
.Update()
.TableFrom(Command.Update)
.Set()
.FieldsAndParametersFrom(fields)
.WhereFrom(where)
.End();
return(queryBuilder.GetString());
}
/// <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>
/// <param name="hints">The hints to be used to optimze the query operation.</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,
string hints = null)
where TEntity : class
{
var fields = PropertyCache.Get <TEntity>()?.Select(property => new Field(property.GetMappedName().AsQuoted(true)));
// There should be fields
if (fields == null || fields.Any() == false)
{
throw new InvalidOperationException($"There are no fields found for type '{typeof(TEntity).Name}'.");
}
// Build the SQL Statement
queryBuilder = queryBuilder ?? new QueryBuilder <TEntity>();
// Build the base
queryBuilder
.Clear()
.Select()
.TopFrom(top)
.FieldsFrom(fields)
.From()
.TableName();
// Build the query optimizers
if (hints != null)
{
queryBuilder
.WriteText(hints);
}
// Build the filter and ordering
queryBuilder
.WhereFrom(where)
.OrderByFrom(orderBy)
.End();
// Return the query
return(queryBuilder.GetString());
}
/// <summary>
/// Creates a SQL Statement for count-all operation.
/// </summary>
/// <param name="queryBuilder">The query builder to be used.</param>
/// <param name="tableName">The name of the target table.</param>
/// <param name="hints">The table hints to be used. See <see cref="SqlTableHints"/> class.</param>
/// <returns>A sql statement for count-all operation.</returns>
public string CreateCountAll(QueryBuilder queryBuilder,
string tableName,
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)
.End();
// Return the query
return(queryBuilder.GetString());
}
/// <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 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());
}
/// <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>
/// Creates a SQL Statement for repository merge 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="qualifiers">The list of qualifier fields to be used for the merge operation in SQL Statement composition.</param>
/// <param name="isPrimaryIdentity">A boolean value indicates whether the primary key is identity in the database.</param>
/// <returns>A string containing the composed SQL Statement for merge operation.</returns>
internal string CreateMerge <TEntity>(QueryBuilder <TEntity> queryBuilder, IEnumerable <Field> qualifiers, bool isPrimaryIdentity)
where TEntity : class
{
// Check for all the fields
var properties = PropertyCache.Get <TEntity>(Command.Merge)?
.Select(property => property.GetMappedName());
var unmatchesQualifiers = qualifiers?.Where(field =>
properties?.FirstOrDefault(property =>
field.Name.ToLower() == property.ToLower()) == null);
if (unmatchesQualifiers?.Count() > 0)
{
throw new InvalidOperationException($"The qualifiers '{unmatchesQualifiers.Select(field => field.AsField()).Join(", ")}' are not " +
$"present at type '{typeof(TEntity).FullName}'.");
}
// Variables
var primary = PrimaryKeyCache.Get <TEntity>();
var primaryKeyName = primary?.GetMappedName();
// Add the primary key as the default qualifier
if (qualifiers == null && primary != null)
{
qualifiers = Field.From(primaryKeyName);
}
// Throw an exception if there is no qualifiers defined
if (qualifiers == null || qualifiers?.Any() == false)
{
throw new InvalidOperationException("There are no qualifier fields defined.");
}
// Get the target properties
var insertableFields = PropertyCache.Get <TEntity>(Command.Insert)
.Select(property => property.GetMappedName())
.Where(field => !(isPrimaryIdentity && field.ToLower() == primaryKeyName?.ToLower()));
var updateableFields = PropertyCache.Get <TEntity>(Command.Update)
.Select(property => property.GetMappedName())
.Where(field => field.ToLower() != primaryKeyName?.ToLower());
var mergeableFields = PropertyCache.Get <TEntity>(Command.Merge)
.Select(property => property.GetMappedName());
var mergeInsertableFields = mergeableFields
.Where(field => insertableFields.Contains(field))
.Select(field => new Field(field));
var mergeUpdateableFields = mergeableFields
.Where(field => updateableFields.Contains(field) &&
qualifiers?.FirstOrDefault(qualifier => qualifier.Name.ToLower() == field.ToLower()) == null)
.Select(field => new Field(field));
// Build the SQL Statement
queryBuilder = queryBuilder ?? new QueryBuilder <TEntity>();
queryBuilder
.Clear()
// MERGE T USING S
.Merge()
.TableName()
.As("T")
.Using()
.OpenParen()
.Select()
.ParametersAsFieldsFrom(Command.Merge)
.CloseParen()
.As("S")
// QUALIFIERS
.On()
.OpenParen()
.WriteText(qualifiers?
.Select(
field => field.AsJoinQualifier("S", "T"))
.Join($" {StringConstant.And.ToUpper()} "))
.CloseParen()
// WHEN NOT MATCHED THEN INSERT VALUES
.When()
.Not()
.Matched()
.Then()
.Insert()
.OpenParen()
.FieldsFrom(mergeInsertableFields)
.CloseParen()
.Values()
.OpenParen()
.AsAliasFieldsFrom(mergeInsertableFields, "S")
.CloseParen()
// WHEN MATCHED THEN UPDATE SET
.When()
.Matched()
.Then()
.Update()
.Set()
.FieldsAndAliasFieldsFrom(mergeUpdateableFields, "S")
.End();
// Return the query
return(queryBuilder.GetString());
}
/// <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>
/// Creates a SQL Statement for repository inline-merge operation.
/// </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 the inline merge operation in SQL Statement composition.</param>
/// <param name="qualifiers">The list of the qualifier fields to be used by the inline merge operation on a SQL Statement.</param>
/// <param name="overrideIgnore">
/// Set to true if the defined <see cref="IgnoreAttribute"/> would likely
/// be ignored in the inline merge operation in SQL Statement composition.
/// </param>
/// <param name="isPrimaryIdentity">A boolean value indicates whether the primary key is identity in the database.</param>
/// <returns>A string containing the composed SQL Statement for inline-merge operation.</returns>
internal string CreateInlineMerge <TEntity>(QueryBuilder <TEntity> queryBuilder, IEnumerable <Field> fields = null, IEnumerable <Field> qualifiers = null,
bool?overrideIgnore = false, bool?isPrimaryIdentity = false)
where TEntity : class
{
// Variables
var primary = PrimaryKeyCache.Get <TEntity>();
var primaryMappedName = primary?.GetMappedName();
// Check for the fields presence
if (fields == null)
{
throw new NullReferenceException("The target fields must be present.");
}
// Check for the qualifiers presence
if (primary == null && qualifiers == null)
{
throw new NullReferenceException("The qualifiers 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}'.");
}
// Check for all the qualifiers
var unmatchesQualifiers = qualifiers?.Where(field =>
properties?.FirstOrDefault(property =>
field.Name.ToLower() == property.ToLower()) == null);
if (unmatchesQualifiers?.Count() > 0)
{
throw new InvalidOperationException($"The qualifiers '{unmatchesQualifiers.Select(field => field.AsField()).Join(", ")}' are not " +
$"present at type '{typeof(TEntity).FullName}'.");
}
// Check for the unmatches
if (overrideIgnore == false)
{
var mergeableProperties = PropertyCache.Get <TEntity>(Command.Merge)?
.Select(property => property.GetMappedName());
var inlineMergeableProperties = PropertyCache.Get <TEntity>(Command.InlineMerge)?
.Select(property => property.GetMappedName())
.Where(property => mergeableProperties.Contains(property));
unmatchesFields = fields?.Where(field =>
inlineMergeableProperties?.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 " +
$"inline mergeable for object '{ClassMappedNameCache.Get<TEntity>()}'.");
}
unmatchesQualifiers = qualifiers?.Where(field =>
inlineMergeableProperties?.FirstOrDefault(property => field.Name.ToLower() == property.ToLower()) == null);
if (unmatchesQualifiers?.Count() > 0)
{
throw new InvalidOperationException($"The qualifiers '{unmatchesQualifiers.Select(field => field.AsField()).Join(", ")}' are not " +
$"inline mergeable for object '{ClassMappedNameCache.Get<TEntity>()}'.");
}
}
// Use the primary for qualifiers if there is no any
if (qualifiers == null && primary != null)
{
qualifiers = Field.From(primaryMappedName);
}
// Get all target fields
var insertableFields = PropertyCache.Get <TEntity>(Command.Insert)
.Select(property => property.GetMappedName())
.Where(field => !(isPrimaryIdentity == true && field.ToLower() == primaryMappedName?.ToLower()));
var updateableFields = PropertyCache.Get <TEntity>(Command.Update)
.Select(property => property.GetMappedName())
.Where(field => field.ToLower() != primaryMappedName?.ToLower());
var mergeInsertableFields = fields
.Where(field => overrideIgnore == true || insertableFields.Contains(field.Name));
var mergeUpdateableFields = fields
.Where(field => overrideIgnore == true || updateableFields.Contains(field.Name) &&
qualifiers?.FirstOrDefault(qualifier => qualifier.Name.ToLower() == field.Name.ToLower()) == null);
// Check if there are inline mergeable fields (for insert)
if (mergeInsertableFields.Any() == false)
{
throw new InvalidOperationException($"No inline mergeable fields (for insert) found at type '{typeof(TEntity).FullName}'.");
}
// Check if there are inline mergeable fields (for update)
if (mergeUpdateableFields.Any() == false)
{
throw new InvalidOperationException($"No inline mergeable fields (for update) found at type '{typeof(TEntity).FullName}'.");
}
// Build the SQL Statement
queryBuilder = queryBuilder ?? new QueryBuilder <TEntity>();
queryBuilder
.Clear()
// MERGE T USING S
.Merge()
.TableName()
.As("T")
.Using()
.OpenParen()
.Select()
.ParametersAsFieldsFrom(fields)
.CloseParen()
.As("S")
// QUALIFIERS
.On()
.OpenParen()
.WriteText(qualifiers?
.Select(
field => field.AsJoinQualifier("S", "T"))
.Join($" {StringConstant.And.ToUpper()} "))
.CloseParen()
// WHEN NOT MATCHED THEN INSERT VALUES
.When()
.Not()
.Matched()
.Then()
.Insert()
.OpenParen()
.FieldsFrom(mergeInsertableFields)
.CloseParen()
.Values()
.OpenParen()
.AsAliasFieldsFrom(mergeInsertableFields, "S")
.CloseParen()
// WHEN MATCHED THEN UPDATE SET
.When()
.Matched()
.Then()
.Update()
.Set()
.FieldsAndAliasFieldsFrom(mergeUpdateableFields, "S")
.End();
// Return the query
return(queryBuilder.GetString());
}
/// <summary>
/// Creates a SQL Statement for repository inline-insert operation.
/// </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 the inline insert operation in SQL Statement composition.</param>
/// <param name="overrideIgnore">
/// Set to true if the defined <see cref="IgnoreAttribute"/> would likely
/// be ignored on the inline insert operation in SQL Statement composition.
/// </param>
/// <param name="isPrimaryIdentity">A boolean value indicates whether the primary key is identity in the database.</param>
/// <returns>A string containing the composed SQL Statement for inline-insert operation.</returns>
internal string CreateInlineInsert <TEntity>(QueryBuilder <TEntity> queryBuilder, IEnumerable <Field> fields = null,
bool?overrideIgnore = false, bool isPrimaryIdentity = 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}'.");
}
// Variables
var primary = PrimaryKeyCache.Get <TEntity>();
var hasFields = isPrimaryIdentity ? fields?.Any(field => field.Name.ToLower() != primary?.GetMappedName().ToLower()) : fields?.Any() == true;
// Check if there are fields
if (hasFields == false)
{
throw new InvalidOperationException($"No inline insertable fields for object '{ClassMappedNameCache.Get<TEntity>()}'.");
}
// Check for the unmatches
if (overrideIgnore == false)
{
var insertableProperties = PropertyCache.Get <TEntity>(Command.Insert)
.Select(property => property.GetMappedName());;
var inlineInsertableProperties = PropertyCache.Get <TEntity>(Command.InlineInsert)
.Select(property => property.GetMappedName())
.Where(property => insertableProperties.Contains(property));
unmatchesFields = fields?.Where(field =>
inlineInsertableProperties?.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 " +
$"inline insertable for object '{ClassMappedNameCache.Get<TEntity>()}'.");
}
}
// Check for the primary key
if (primary != null && isPrimaryIdentity)
{
fields = fields?
.Where(field => field.Name.ToLower() != primary.GetMappedName().ToLower())
.Select(field => field);
}
// Build the SQL Statement
queryBuilder = queryBuilder ?? new QueryBuilder <TEntity>();
queryBuilder
.Clear()
.Insert()
.Into()
.TableName()
.OpenParen()
.FieldsFrom(fields)
.CloseParen()
.Values()
.OpenParen()
.ParametersFrom(fields)
.CloseParen()
.End();
var result = isPrimaryIdentity ? "SCOPE_IDENTITY()" : (primary != null) ? $"@{primary.GetMappedName()}" : "NULL";
queryBuilder
.Select()
.WriteText(result)
.As("[Result]")
.End();
// Return the query
return(queryBuilder.GetString());
}
/// <summary>
/// Selecting data from Sql with Sql IN clause usually requires 1 Parameter for every value, and this result in
/// safe Sql Queries, but there is a limit of 2100 parameters on a Sql Command. This method provides a safe
/// alternative implementation that is highly performant for large data sets using a list of int values (e.g Ids).
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="sqlConnection"></param>
/// <param name="idList"></param>
/// <param name="filterFieldName"></param>
/// <param name="tableName"></param>
/// <param name="fields"></param>
/// <param name="orderBy"></param>
/// <param name="hints"></param>
/// <param name="cacheKey"></param>
/// <param name="cacheItemExpiration"></param>
/// <param name="commandTimeout"></param>
/// <param name="transaction"></param>
/// <param name="logTrace"></param>
/// <param name="cancellationToken"></param>
/// <param name="cache"></param>
/// <returns></returns>
public static async Task <IEnumerable <TEntity> > QueryBulkResultsByIdAsync <TEntity>(
this SqlConnection sqlConnection,
IEnumerable <int> idList,
string filterFieldName = null,
string tableName = null,
IEnumerable <Field> fields = null,
IEnumerable <OrderField> orderBy = null,
string hints = null,
string cacheKey = null,
int?cacheItemExpiration = null,
int?commandTimeout = null,
IDbTransaction transaction = null,
ICache cache = null,
Action <string> logTrace = null,
CancellationToken cancellationToken = default
) where TEntity : class
{
var connection = sqlConnection ?? throw new ArgumentNullException(nameof(sqlConnection));
var timer = Stopwatch.StartNew();
Field filterField;
if (string.IsNullOrWhiteSpace(filterFieldName))
{
//Attempt to dynamically resolve the Filter Field as the Identity or Primary Key field (if the field is a Numeric Type)!
var classProp = IdentityCache.Get <TEntity>() ?? PrimaryCache.Get <TEntity>();
if (classProp == null || !classProp.PropertyInfo.PropertyType.IsNumericType())
{
throw new ArgumentException(
$"The filter field name was not specified and an Int Id could not be dynamically resolved from the Identity or Primary Key properties for the type [{typeof(TEntity).Name}]",
nameof(filterFieldName)
);
}
filterField = new Field(classProp.GetMappedName());
}
else
{
//If Specified then we use the Filter Field Name specified and attempt to resolve it on the Model!
filterField = new Field(PropertyMappedNameCache.Get <TEntity>(filterFieldName) ?? filterFieldName);
}
var dbTableName = string.IsNullOrWhiteSpace(tableName)
? ClassMappedNameCache.Get <TEntity>()
: tableName;
//Ensure we have default fields; default is to include All Fields...
var fieldsList = fields?.ToList();
var selectFields = fieldsList?.Any() == true
? fieldsList
: FieldCache.Get <TEntity>();
//Retrieve only the select fields that are valid for the Database query!
//NOTE: We guard against duplicate values as a convenience.
var validSelectFields = await connection
.GetValidatedDbFieldsAsync(dbTableName, selectFields.Distinct())
.ConfigureAwait(false);
var dbSetting = connection.GetDbSetting();
var query = new QueryBuilder()
.Clear()
.Select().FieldsFrom(validSelectFields, dbSetting)
.From().TableNameFrom(dbTableName, dbSetting).WriteText("data")
.WriteText("INNER JOIN STRING_SPLIT(@StringSplitCsvValues, ',') split")
.On().WriteText("(data.").FieldFrom(filterField).WriteText("= split.value)")
.OrderByFrom(orderBy, dbSetting)
.HintsFrom(hints)
.End();
var commandText = query.GetString();
var commandParams = new { StringSplitCsvValues = idList.ToCsvString(false) };
logTrace?.Invoke($"Query: {commandText}");
logTrace?.Invoke($"Query Param @StringSplitCsvValues: {commandParams.StringSplitCsvValues}");
await connection.EnsureOpenAsync(cancellationToken : cancellationToken);
logTrace?.Invoke($"DB Connection Established in: {timer.ToElapsedTimeDescriptiveFormat()}");
//By creating a View Model of the data we are interested in we can easily query the View
// and teh complex many-to-many join is now encapsulated for us in the SQL View...
var results = await connection.ExecuteQueryAsync <TEntity>(
commandText,
commandParams,
commandType : CommandType.Text,
commandTimeout : commandTimeout,
transaction : transaction,
cancellationToken : cancellationToken,
cacheKey : cacheKey,
cacheItemExpiration : cacheItemExpiration,
cache : cache
).ConfigureAwait(false);
logTrace?.Invoke($"Query Execution Completed in: {timer.ToElapsedTimeDescriptiveFormat()}");
return(results);
}
/// <summary>
/// Creates a SQL Statement for repository <i>Merge</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="qualifiers">The list of qualifier fields to be used for the <i>Merge</i> operation in SQL Statement composition.</param>
/// <param name="isPrimaryIdentity">A boolean value indicates whether the primary key is identity in the database.</param>
/// <returns>A string containing the composed SQL Statement for <i>Merge</i> operation.</returns>
internal string CreateMerge <TEntity>(QueryBuilder <TEntity> queryBuilder, IEnumerable <Field> qualifiers, bool isPrimaryIdentity)
where TEntity : DataEntity
{
var primary = DataEntityExtension.GetPrimaryProperty <TEntity>();
// Add the primary key as the default qualifier
if (qualifiers == null && primary != null)
{
qualifiers = Field.From(primary.GetMappedName());
}
// Get the target properties
var insertableProperties = DataEntityExtension.GetPropertiesFor <TEntity>(Command.Insert)
.Where(property => !(isPrimaryIdentity && property == primary));
var updateableProperties = DataEntityExtension.GetPropertiesFor <TEntity>(Command.Merge)
.Where(property => property != primary);
var mergeableProperties = DataEntityExtension.GetPropertiesFor <TEntity>(Command.Merge);
var mergeInsertableFields = mergeableProperties
.Where(property => insertableProperties.Contains(property))
.Select(property => new Field(property.Name));
var mergeUpdateableFields = mergeableProperties
.Where(property => updateableProperties.Contains(property))
.Select(property => new Field(property.Name));
// Build the SQL Statement
queryBuilder = queryBuilder ?? new QueryBuilder <TEntity>();
queryBuilder
.Clear()
// MERGE T USING S
.Merge()
.TableFrom(Command.Merge)
.As("T")
.Using()
.OpenParen()
.Select()
.ParametersAsFieldsFrom(Command.None) // All fields must be included for selection
.CloseParen()
.As("S")
// QUALIFIERS
.On()
.OpenParen()
.WriteText(qualifiers?
.Select(
field => field.AsJoinQualifier("S", "T"))
.Join($" {StringConstant.And.ToUpper()} "))
.CloseParen()
// WHEN NOT MATCHED THEN INSERT VALUES
.When()
.Not()
.Matched()
.Then()
.Insert()
.OpenParen()
.FieldsFrom(mergeInsertableFields)
.CloseParen()
.Values()
.OpenParen()
.ParametersFrom(mergeInsertableFields)
.CloseParen()
// WHEN MATCHED THEN UPDATE SET
.When()
.Matched()
.Then()
.Update()
.Set()
.FieldsAndAliasFieldsFrom(mergeUpdateableFields, "S")
.End();
// Return the query
return(queryBuilder.GetString());
}
