/// <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>
/// Gets a command text from the cache for the <see cref="DbConnectionExtension.Merge{TEntity}(IDbConnection, TEntity, IEnumerable{Field}, int?, IDbTransaction, ITrace, IStatementBuilder)"/> operation.
/// </summary>
/// <typeparam name="TEntity">The type of the target entity.</typeparam>
/// <param name="request">The request object.</param>
/// <returns>The cached command text.</returns>
public static string GetMergeText <TEntity>(MergeRequest request)
where TEntity : class
{
var commandText = (string)null;
if (m_cache.TryGetValue(request, out commandText) == false)
{
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 isPrimaryIdentity = (identity != null);
var statementBuilder = EnsureStatementBuilder(request.Connection, request.StatementBuilder);
if (statementBuilder is SqlStatementBuilder)
{
var sqlStatementBuilder = (SqlStatementBuilder)statementBuilder;
if (isPrimaryIdentity == false)
{
isPrimaryIdentity = PrimaryKeyIdentityCache.Get <TEntity>(request.Connection.ConnectionString);
}
commandText = sqlStatementBuilder.CreateMerge(queryBuilder: new QueryBuilder <TEntity>(),
qualifiers: request.Qualifiers,
isPrimaryIdentity: isPrimaryIdentity);
}
else
{
commandText = statementBuilder.CreateMerge(queryBuilder: new QueryBuilder <TEntity>(),
qualifiers: request.Qualifiers);
}
m_cache.TryAdd(request, commandText);
}
return(commandText);
}
/// <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>
/// Gets a command text from the cache for the <see cref="DbConnectionExtension.Insert{TEntity}(IDbConnection, TEntity, int?, IDbTransaction, ITrace, IStatementBuilder)"/> operation.
/// </summary>
/// <typeparam name="TEntity">The type of the target entity.</typeparam>
/// <param name="request">The request object.</param>
/// <returns>The cached command text.</returns>
public static string GetInsertText <TEntity>(InsertRequest request)
where TEntity : class
{
var commandText = (string)null;
if (m_cache.TryGetValue(request, out commandText) == false)
{
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 isPrimaryIdentity = (identity != null);
var statementBuilder = (request.StatementBuilder ??
StatementBuilderMapper.Get(request.Connection?.GetType())?.StatementBuilder ??
new SqlDbStatementBuilder());
if (statementBuilder is SqlDbStatementBuilder)
{
var sqlStatementBuilder = (SqlDbStatementBuilder)statementBuilder;
if (isPrimaryIdentity == false)
{
isPrimaryIdentity = PrimaryKeyIdentityCache.Get <TEntity>(request.Connection.ConnectionString, Command.Insert);
}
commandText = sqlStatementBuilder.CreateInsert(queryBuilder: new QueryBuilder <TEntity>(),
isPrimaryIdentity: isPrimaryIdentity);
}
else
{
commandText = statementBuilder.CreateInsert(queryBuilder: new QueryBuilder <TEntity>());
}
m_cache.TryAdd(request, commandText);
}
return(commandText);
}
/// <summary>
/// Gets the value that defines whether the data entity has primary key is identity.
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="connectionString">The connection string object to be used.</param>
/// <returns>A boolean value indicating the identification of the column.</returns>
public static bool Get <TEntity>(string connectionString)
where TEntity : class
{
var key = typeof(TEntity).FullName;
var value = false;
if (!m_cache.TryGetValue(key, out value))
{
var primary = PrimaryKeyCache.Get <TEntity>();
if (primary != null)
{
var tableName = ClassMappedNameCache.Get <TEntity>();
var fieldDefinitions = SqlHelper.GetFieldDefinitions(connectionString, tableName);
if (fieldDefinitions != null)
{
var field = fieldDefinitions
.FirstOrDefault(fd =>
string.Equals(fd.Name, primary.GetMappedName(), StringComparison.CurrentCultureIgnoreCase));
value = field?.IsIdentity == true;
}
}
m_cache.TryAdd(key, value);
}
return(value);
}
/// <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>
/// <returns>A string containing the composed SQL Statement for merge operation.</returns>
public string CreateMerge <TEntity>(QueryBuilder <TEntity> queryBuilder, IEnumerable <Field> qualifiers = null)
where TEntity : class
{
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 isPrimaryIdentity = (identity != null) && identity == primary;
return(CreateMerge(queryBuilder, qualifiers, isPrimaryIdentity));
}
/// <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>
/// <returns>A string containing the composed SQL Statement for inline-insert operation.</returns>
public string CreateInlineInsert <TEntity>(QueryBuilder <TEntity> queryBuilder, IEnumerable <Field> fields = null, bool?overrideIgnore = false)
where TEntity : class
{
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 isPrimaryIdentity = (identity != null) && identity == primary;
return(CreateInlineInsert <TEntity>(queryBuilder, fields, overrideIgnore, isPrimaryIdentity));
}
/// <summary>
/// Gets the <see cref="MapAttribute.Name"/> value implemented on the data entity on a target command.
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="connectionString">The connection string object to be used.</param>
/// <param name="command">The target command.</param>
/// <returns>A boolean value indicating the identification of the column.</returns>
public static bool Get <TEntity>(string connectionString, Command command)
where TEntity : class
{
var key = $"{typeof(TEntity).FullName}.{command.ToString()}";
var value = false;
if (!m_cache.TryGetValue(key, out value))
{
var primary = PrimaryKeyCache.Get <TEntity>();
if (primary != null)
{
value = SqlDbHelper.IsIdentity <TEntity>(connectionString, command, primary.GetMappedName());
}
m_cache.TryAdd(key, value);
}
return(value);
}
/// <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>
/// 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 = false)
where TEntity : class
{
// Variables
var primary = PrimaryKeyCache.Get <TEntity>();
// Check for all the fields
var fields = PropertyCache.Get <TEntity>()?
.Select(property => new Field(property.GetMappedName()));
// Check for all the qualifier (from the entity table)
if (qualifiers != null)
{
var unmatchesQualifiers = qualifiers?.Where(field =>
fields?.FirstOrDefault(f =>
field.Name.ToLower() == f.Name.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}'.");
}
}
else
{
// Use the primary for qualifiers if there is no any
if (primary != null)
{
qualifiers = Field.From(primary.GetMappedName());
}
}
// Get the insertable and updateable fields
var insertableFields = fields
.Where(field => (field.Name.ToLower() == primary?.GetMappedName().ToLower() && isPrimaryIdentity.Value) == false);
var updateableFields = fields
.Where(field => (field.Name.ToLower() == primary?.GetMappedName().ToLower()) == false);
// Build the SQL Statement
queryBuilder = queryBuilder ?? new QueryBuilder <TEntity>();
queryBuilder
.Clear()
// MERGE T USING S
.Merge()
.TableName()
.As("T")
.Using()
.OpenParen()
.Select()
.ParametersAsFieldsFrom()
.CloseParen()
.As("S")
// QUALIFIERS
.On()
.OpenParen()
.WriteText(qualifiers?
.Select(
field => field.AsJoinQualifier("S", "T"))
.Join(" AND "))
.CloseParen()
// WHEN NOT MATCHED THEN INSERT VALUES
.When()
.Not()
.Matched()
.Then()
.Insert()
.OpenParen()
.FieldsFrom(insertableFields)
.CloseParen()
.Values()
.OpenParen()
.AsAliasFieldsFrom(insertableFields, "S")
.CloseParen()
// WHEN MATCHED THEN UPDATE SET
.When()
.Matched()
.Then()
.Update()
.Set()
.FieldsAndAliasFieldsFrom(updateableFields, "S")
.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="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?isPrimaryIdentity = false)
where TEntity : class
{
// Variables
var primary = PrimaryKeyCache.Get <TEntity>();
// 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 (must be present from the database table)
var properties = PropertyCache.Get <TEntity>()?
.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 (from the database table)
if (qualifiers != null)
{
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 all the qualifiers (from the passed entity)
unmatchesQualifiers = qualifiers.Where(field =>
fields?.FirstOrDefault(f =>
field.Name.ToLower() == f.Name.ToLower()) == null);
if (unmatchesQualifiers?.Count() > 0)
{
throw new InvalidOperationException($"The qualifiers '{unmatchesQualifiers.Select(field => field.AsField()).Join(", ")}' are not " +
$"present at the properties of the passed object.");
}
}
else
{
// The primary key must be in the passed object if the qualifiers are null
if (fields?.FirstOrDefault(f => f.Name.ToLower() == primary.GetMappedName().ToLower()) == null)
{
throw new InvalidOperationException($"The primary key '{primary.GetMappedName()}' must be preset at the " +
$"object properties if the qualifiers are not defined.");
}
// Use the primary for qualifiers if there is no any
if (primary != null)
{
qualifiers = Field.From(primary.GetMappedName());
}
}
// Get the insertable and updateable fields
var targetFields = fields.ToList();
var insertableFields = fields
.Where(field => (field.Name.ToLower() == primary?.GetMappedName().ToLower() && isPrimaryIdentity.Value) == false);
var updateableFields = fields
.Where(field => (field.Name.ToLower() == primary?.GetMappedName().ToLower()) == false && qualifiers?.Any(f => f == field) == false);
// Build the SQL Statement
queryBuilder = queryBuilder ?? new QueryBuilder <TEntity>();
queryBuilder
.Clear()
// MERGE T USING S
.Merge()
.TableName()
.As("T")
.Using()
.OpenParen()
.Select()
.ParametersAsFieldsFrom(targetFields)
.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(insertableFields)
.CloseParen()
.Values()
.OpenParen()
.AsAliasFieldsFrom(insertableFields, "S")
.CloseParen()
// WHEN MATCHED THEN UPDATE SET
.When()
.Matched()
.Then()
.Update()
.Set()
.FieldsAndAliasFieldsFrom(updateableFields, "S")
.End();
// Return the query
return(queryBuilder.GetString());
}