/// <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>(Command.InlineUpdate)}'.");
}
// 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>(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 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()
.TableFrom(Command.Merge)
.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-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>(Command.InlineInsert)}'.");
}
// 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>(Command.InlineInsert)}'.");
}
}
// 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()
.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 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>(Command.InlineMerge)}'.");
}
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>(Command.InlineMerge)}'.");
}
}
// 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()
.TableFrom(Command.Merge)
.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>
///
/// </summary>
/// <param name="tableName"></param>
/// <param name="tempTableName"></param>
/// <param name="fields"></param>
/// <param name="qualifiers"></param>
/// <param name="primaryField"></param>
/// <param name="identityField"></param>
/// <param name="hints"></param>
/// <param name="dbSetting"></param>
/// <param name="isReturnIdentity"></param>
/// <returns></returns>
private static string GetBulkMergeSqlText(string tableName,
string tempTableName,
IEnumerable <Field> fields,
IEnumerable <Field> qualifiers,
Field primaryField,
Field identityField,
string hints,
IDbSetting dbSetting,
bool isReturnIdentity)
{
// Validate the presence
if (fields?.Any() != true)
{
throw new MissingFieldException("There are no field(s) defined.");
}
if (qualifiers?.Any() != true)
{
throw new MissingFieldException("There is no qualifer field(s) defined.");
}
// Variables needed
var setFields = fields
.Select(field => field.AsJoinQualifier("T", "S", dbSetting))
.Join(", ");
var builder = new QueryBuilder();
// Insertable fields
var insertableFields = fields
.Where(field => string.Equals(field.Name, identityField?.Name, StringComparison.OrdinalIgnoreCase) == false);
// Updatable fields
var updateableFields = fields
.Where(field => field != identityField && field != primaryField)
.Where(field =>
qualifiers.Any(
q => string.Equals(q.Name, field.Name, StringComparison.OrdinalIgnoreCase)) == false);
// Compose the statement
builder.Clear()
// MERGE T USING S
.Merge()
.TableNameFrom(tableName, dbSetting)
.HintsFrom(hints)
.As("T")
.Using()
.OpenParen()
.Select()
.FieldsFrom(fields, dbSetting)
.From()
.TableNameFrom(tempTableName, dbSetting)
.CloseParen()
.As("S")
// QUALIFIERS
.On()
.OpenParen()
.WriteText(qualifiers?
.Select(
field => field.AsJoinQualifier("S", "T", dbSetting))
.Join(" AND "))
.CloseParen()
// WHEN NOT MATCHED THEN INSERT VALUES
.When()
.Not()
.Matched()
.Then()
.Insert()
.OpenParen()
.FieldsFrom(insertableFields, dbSetting)
.CloseParen()
.Values()
.OpenParen()
.AsAliasFieldsFrom(insertableFields, "S", dbSetting)
.CloseParen()
// WHEN MATCHED THEN UPDATE SET
.When()
.Matched()
.Then()
.Update()
.Set()
.FieldsAndAliasFieldsFrom(updateableFields, "T", "S", dbSetting);
// Set the output
if (isReturnIdentity && identityField != null)
{
builder
.WriteText(string.Concat("OUTPUT INSERTED.", identityField.Name.AsField(dbSetting)))
.As("[Result]");
}
// End the builder
builder.End();
// Return the sql
return(builder.ToString());
}
/// <summary>
///
/// </summary>
/// <param name="tableName"></param>
/// <param name="tempTableName"></param>
/// <param name="fields"></param>
/// <param name="identityField"></param>
/// <param name="hints"></param>
/// <param name="dbSetting"></param>
/// <param name="isReturnIdentity"></param>
/// <returns></returns>
private static string GetBulkInsertSqlText(string tableName,
string tempTableName,
IEnumerable <Field> fields,
Field identityField,
string hints,
IDbSetting dbSetting,
bool isReturnIdentity)
{
// Validate the presence
if (fields?.Any() != true)
{
throw new MissingFieldException("There are no field(s) defined.");
}
// Variables needed
var setFields = fields
.Select(field => field.AsJoinQualifier("T", "S", dbSetting))
.Join(", ");
var builder = new QueryBuilder();
// Insertable fields
var insertableFields = fields
.Where(field => string.Equals(field.Name, identityField?.Name, StringComparison.OrdinalIgnoreCase) == false);
// Compose the statement
builder.Clear()
// MERGE T USING S
.Merge()
.TableNameFrom(tableName, dbSetting)
.HintsFrom(hints)
.As("T")
.Using()
.OpenParen()
.Select()
.Top()
.WriteText("100 PERCENT")
//.FieldsFrom(fields, dbSetting)
.WriteText("*") // Including the [__RepoDb_OrderColumn]
.From()
.TableNameFrom(tempTableName, dbSetting);
// Return Identity
if (isReturnIdentity && identityField != null)
{
builder
.OrderBy()
.WriteText("[__RepoDb_OrderColumn]")
.Ascending();
}
// Continuation
builder
.CloseParen()
.As("S")
// QUALIFIERS
.On()
.OpenParen()
.WriteText("1 = 0")
.CloseParen()
// WHEN NOT MATCHED THEN INSERT VALUES
.When()
.Not()
.Matched()
.Then()
.Insert()
.OpenParen()
.FieldsFrom(insertableFields, dbSetting)
.CloseParen()
.Values()
.OpenParen()
.AsAliasFieldsFrom(insertableFields, "S", dbSetting)
.CloseParen();
// Set the output
if (isReturnIdentity == true && identityField != null)
{
builder
.WriteText(string.Concat("OUTPUT INSERTED.", identityField.Name.AsField(dbSetting)))
.As("[Result],")
.WriteText("S.[__RepoDb_OrderColumn]")
.As("[OrderColumn]");
}
// End
builder.End();
// Return the sql
return(builder.ToString());
}
/// <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());
}
/// <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());
}
