/// <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()); }