/// <summary>
/// Creates a new instance of <see cref="DataEntityDataReader{TEntity}"/> object.
/// </summary>
/// <param name="tableName">The name of the target table.</param>
/// <param name="entities">The list of the data entity object to be used for manipulation.</param>
/// <param name="connection">The actual <see cref="IDbConnection"/> object used.</param>
/// <param name="transaction">The transaction object that is currently in used.</param>
public DataEntityDataReader(string tableName,
IEnumerable <TEntity> entities,
IDbConnection connection,
IDbTransaction transaction)
{
if (entities == null)
{
throw new NullReferenceException("The entities could not be null.");
}
// Fields
this.tableName = tableName ?? ClassMappedNameCache.Get <TEntity>();
isClosed = false;
isDisposed = false;
position = -1;
recordsAffected = -1;
// Type
var entityType = typeof(TEntity);
EntityType = entityType == StaticType.Object ?
(entities?.FirstOrDefault()?.GetType() ?? entityType) :
entityType;
isDictionaryStringObject = EntityType.IsDictionaryStringObject();
// DbSetting
DbSetting = connection?.GetDbSetting();
// Properties
Connection = connection;
Transaction = transaction;
Enumerator = entities.GetEnumerator();
Entities = entities;
Properties = GetClassProperties().AsList();
Fields = GetFields(Entities?.FirstOrDefault() as IDictionary <string, object>).AsList();
fieldCount = isDictionaryStringObject ? Fields.Count : Properties.Count;
}
/// <summary>
/// Inserts a list of entities into the target table by bulk. Underneath this operation is a call directly to the existing
/// <see cref="NpgsqlConnection.BeginBinaryExport(string)"/> method via the 'BinaryImport' extended method.
/// </summary>
/// <typeparam name="TEntity">The type of the entity.</typeparam>
/// <param name="repository">The instance of <see cref="DbRepository{TDbConnection}"/> object.</param>
/// <param name="entities">The list of entities to be bulk-inserted to the target table.
/// This can be an <see cref="IEnumerable{T}"/> of the following objects (<typeparamref name="TEntity"/> (as class/model), <see cref="ExpandoObject"/>,
/// <see cref="IDictionary{TKey, TValue}"/> (of <see cref="string"/>/<see cref="object"/>) and Anonymous Types).</param>
/// <param name="mappings">The list of mappings to be used. If not specified, only the matching properties/columns from the target table will be used. (This is not an entity mapping)</param>
/// <param name="bulkCopyTimeout">The timeout expiration of the operation (see <see cref="NpgsqlBinaryImporter.Timeout"/>).</param>
/// <param name="batchSize">The size per batch to be sent to the database. If not specified, all the entities will be sent together in one-go.</param>
/// <param name="identityBehavior">The behavior of how the identity column would work during the operation.</param>
/// <param name="pseudoTableType">The value that defines whether an actual or temporary table will be created for the pseudo-table.</param>
/// <param name="transaction">The current transaction object in used. If not specified, an implicit transaction will be created and used.</param>
/// <returns>The number of rows that has been inserted into the target table.</returns>
public static int BinaryBulkInsert <TEntity>(this DbRepository <NpgsqlConnection> repository,
IEnumerable <TEntity> entities,
IEnumerable <NpgsqlBulkInsertMapItem> mappings = null,
int?bulkCopyTimeout = null,
int?batchSize = null,
BulkImportIdentityBehavior identityBehavior = default,
BulkImportPseudoTableType pseudoTableType = default,
NpgsqlTransaction transaction = null)
where TEntity : class
{
// Create a connection
var connection = (transaction?.Connection ?? repository.CreateConnection());
try
{
// Call the method
return(connection.BinaryBulkInsert <TEntity>(tableName: ClassMappedNameCache.Get <TEntity>(),
entities: entities,
mappings: mappings,
bulkCopyTimeout: bulkCopyTimeout,
batchSize: batchSize,
identityBehavior: identityBehavior,
pseudoTableType: pseudoTableType,
transaction: transaction));
}
finally
{
// Dispose the connection
if (repository.ConnectionPersistency == ConnectionPersistency.PerCall)
{
if (transaction == null)
{
connection.Dispose();
}
}
}
}
/// <summary>
/// Creates a new instance of <see cref="DataEntityDataReader{TEntity}"/> object.
/// </summary>
/// <param name="entities">The list of the data entity object to be used for manipulation.</param>
/// <param name="connection">The actual <see cref="IDbConnection"/> object used.</param>
/// <param name="transaction">The transaction object that is currently in used.</param>
public DataEntityDataReader(IEnumerable <TEntity> entities,
IDbConnection connection,
IDbTransaction transaction)
{
if (entities == null)
{
throw new NullReferenceException("The entities could not be null.");
}
// Fields
m_isClosed = false;
m_isDisposed = false;
m_position = -1;
m_recordsAffected = -1;
// DbSetting
DbSetting = connection?.GetDbSetting();
// Properties
if (connection != null)
{
var fields = DbFieldCache.Get(connection, ClassMappedNameCache.Get <TEntity>(DbSetting), transaction);
if (fields?.Any() == true)
{
Properties = PropertyCache.Get <TEntity>(DbSetting)
.Where(p => fields.FirstOrDefault(f => string.Equals(f.UnquotedName, p.GetUnquotedMappedName(), StringComparison.OrdinalIgnoreCase)) != null)
.AsList();
}
}
if (Properties == null)
{
Properties = PropertyCache.Get <TEntity>(DbSetting).AsList();
}
Enumerator = entities.GetEnumerator();
Entities = entities;
FieldCount = Properties.Count;
}
/// <summary>
/// Insert multiple rows in the table in an asynchronous way.
/// </summary>
/// <typeparam name="TEntity">The type of the data entity.</typeparam>
/// <param name="connection">The connection object to be used.</param>
/// <param name="entities">The list of data entity objects to be inserted.</param>
/// <param name="batchSize">The batch size of the insertion.</param>
/// <param name="fields">The mapping list of <see cref="Field"/> objects to be used.</param>
/// <param name="hints">The table hints to be used.</param>
/// <param name="commandTimeout">The command timeout in seconds to be used.</param>
/// <param name="transaction">The transaction to be used.</param>
/// <param name="trace">The trace object to be used.</param>
/// <param name="statementBuilder">The statement builder object to be used.</param>
/// <param name="cancellationToken">The <see cref="CancellationToken"/> object to be used during the asynchronous operation.</param>
/// <returns>The number of inserted rows in the table.</returns>
public static Task <int> InsertAllAsync <TEntity>(this IDbConnection connection,
IEnumerable <TEntity> entities,
int batchSize = Constant.DefaultBatchOperationSize,
IEnumerable <Field> fields = null,
string hints = null,
int?commandTimeout = null,
IDbTransaction transaction = null,
ITrace trace = null,
IStatementBuilder statementBuilder = null,
CancellationToken cancellationToken = default)
where TEntity : class
{
return(InsertAllAsyncInternal <TEntity>(connection: connection,
tableName: ClassMappedNameCache.Get <TEntity>(),
entities: entities,
batchSize: batchSize,
fields: fields,
hints: hints,
commandTimeout: commandTimeout,
transaction: transaction,
trace: trace,
statementBuilder: statementBuilder,
cancellationToken: cancellationToken));
}
/// <summary>
/// Merges a list of entities into the target table by bulk in an asynchronous way. This operation is inserting a row (if not present), and updating an existing
/// row (if present), based on the given qualifiers. It uses either of the 'INSERT/UPDATE' and 'ON CONFLICT DO UPDATE' commands of the
/// PostgreSQL based on the value passed to the '<paramref name="mergeCommandType"/>' argument. Underneath this operation is a call directly to
/// the existing <see cref="NpgsqlConnection.BeginBinaryExport(string)"/> method via the customized 'BinaryBulkInsertAsync' extended method.
/// </summary>
/// <typeparam name="TEntity">The type of the entity.</typeparam>
/// <param name="connection">The current connection object in used.</param>
/// <param name="entities">The list of entities to be bulk-merged to the target table.
/// This can be an <see cref="IEnumerable{T}"/> of the following objects (<typeparamref name="TEntity"/> (as class/model), <see cref="ExpandoObject"/>,
/// <see cref="IDictionary{TKey, TValue}"/> (of <see cref="string"/>/<see cref="object"/>) and Anonymous Types).</param>
/// <param name="qualifiers">The list of qualifier fields to be used during the operation. Ensure to target the indexed columns to make the execution more performant. If not specified, the primary key will be used.</param>
/// <param name="mappings">The list of mappings to be used. If not specified, only the matching properties/columns from the target table will be used. (This is not the entity mappings, but is working on top of it)</param>
/// <param name="bulkCopyTimeout">The timeout expiration of the operation (see <see cref="NpgsqlBinaryImporter.Timeout"/>).</param>
/// <param name="batchSize">The size per batch to be sent to the database. If not specified, all the entities will be sent together in one-go.</param>
/// <param name="identityBehavior">The behavior of how the identity column would work during the operation.</param>
/// <param name="mergeCommandType">The value that defines the type of command to be used during the operation.</param>
/// <param name="pseudoTableType">The value that defines whether an actual or temporary table will be created for the pseudo-table.</param>
/// <param name="transaction">The current transaction object in used. If not specified, an implicit transaction will be created and used.</param>
/// <param name="cancellationToken">The <see cref="CancellationToken"/> object to be used during the asynchronous operation.</param>
/// <returns>The number of rows that has been merged into the target table.</returns>
public static Task <int> BinaryBulkMergeAsync <TEntity>(this NpgsqlConnection connection,
IEnumerable <TEntity> entities,
IEnumerable <Field> qualifiers = null,
IEnumerable <NpgsqlBulkInsertMapItem> mappings = null,
int?bulkCopyTimeout = null,
int?batchSize = null,
BulkImportIdentityBehavior identityBehavior = default,
BulkImportMergeCommandType mergeCommandType = default,
BulkImportPseudoTableType pseudoTableType = default,
NpgsqlTransaction transaction = null,
CancellationToken cancellationToken = default)
where TEntity : class =>
BinaryBulkMergeAsync <TEntity>(connection : connection,
tableName : ClassMappedNameCache.Get <TEntity>(),
entities : entities,
qualifiers : qualifiers,
mappings : mappings,
bulkCopyTimeout : bulkCopyTimeout,
batchSize : batchSize,
identityBehavior : identityBehavior,
mergeCommandType : mergeCommandType,
pseudoTableType : pseudoTableType,
transaction : transaction,
cancellationToken : cancellationToken);
/// <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>
/// Update the existing rows via entities by bulk in an asynchronous way. Underneath this operation is a call directly to the existing
/// <see cref="NpgsqlConnection.BeginBinaryExport(string)"/> method via the 'BinaryImport' extended method.
/// </summary>
/// <typeparam name="TEntity">The type of the entity.</typeparam>
/// <param name="repository">The instance of <see cref="DbRepository{TDbConnection}"/> object.</param>
/// <param name="tableName">The name of the target table from the database.</param>
/// <param name="entities">The list of entities to be bulk-updated to the target table.
/// This can be an <see cref="IEnumerable{T}"/> of the following objects (<typeparamref name="TEntity"/> (as class/model), <see cref="ExpandoObject"/>,
/// <see cref="IDictionary{TKey, TValue}"/> (of <see cref="string"/>/<see cref="object"/>) and Anonymous Types).</param>
/// <param name="qualifiers">The list of qualifier fields to be used during the operation. Ensure to target the indexed columns to make the execution more performant. If not specified, the primary key will be used.</param>
/// <param name="mappings">The list of mappings to be used. If not specified, only the matching properties/columns from the target table will be used. (This is not the entity mappings, but is working on top of it)</param>
/// <param name="bulkCopyTimeout">The timeout expiration of the operation (see <see cref="NpgsqlBinaryImporter.Timeout"/>).</param>
/// <param name="batchSize">The size per batch to be sent to the database. If not specified, all the rows of the table will be sent together in one-go.</param>
/// <param name="keepIdentity">A value that indicates whether the existing identity property values from the entities will be kept during the operation.</param>
/// <param name="pseudoTableType">The value that defines whether an actual or temporary table will be created for the pseudo-table.</param>
/// <param name="transaction">The current transaction object in used. If not specified, an implicit transaction will be created and used.</param>
/// <param name="cancellationToken">The <see cref="CancellationToken"/> object to be used during the asynchronous operation.</param>
/// <returns>The number of rows that has been updated into the target table.</returns>
public static async Task <int> BinaryBulkUpdateAsync <TEntity>(this DbRepository <NpgsqlConnection> repository,
string tableName,
IEnumerable <TEntity> entities,
IEnumerable <Field> qualifiers = null,
IEnumerable <NpgsqlBulkInsertMapItem> mappings = null,
int?bulkCopyTimeout = null,
int?batchSize = null,
bool keepIdentity = false,
BulkImportPseudoTableType pseudoTableType = default,
NpgsqlTransaction transaction = null,
CancellationToken cancellationToken = default)
where TEntity : class
{
// Create a connection
var connection = (transaction?.Connection ?? repository.CreateConnection());
try
{
// Call the method
return(await connection.BinaryBulkUpdateAsync <TEntity>(tableName : (tableName ?? ClassMappedNameCache.Get <TEntity>()),
entities : entities,
qualifiers : qualifiers,
mappings : mappings,
bulkCopyTimeout : bulkCopyTimeout,
batchSize : batchSize,
keepIdentity : keepIdentity,
pseudoTableType : pseudoTableType,
transaction : transaction,
cancellationToken : cancellationToken));
}
finally
{
// Dispose the connection
if (repository.ConnectionPersistency == ConnectionPersistency.PerCall)
{
if (transaction == null)
{
connection.Dispose();
}
}
}
}
/// <summary>
/// Appends the mapped entity name to the SQL Query Statement.
/// </summary>
/// <typeparam name="TEntity">The type of data entity object bound for the SQL Statement to be created.</typeparam>
/// <returns>The current instance.</returns>
public QueryBuilder TableNameFrom <TEntity>()
where TEntity : class
{
return(Append(ClassMappedNameCache.Get <TEntity>()));
}
/// <summary>
/// Query all the data from the database.
/// </summary>
/// <typeparam name="TEntity">The type of the data entity object.</typeparam>
/// <param name="connection">The connection object to be used.</param>
/// <param name="orderBy">The order definition of the fields to be used.</param>
/// <param name="hints">The table hints to be used. See <see cref="SqlTableHints"/> class.</param>
/// <param name="cacheKey">
/// The key to the cache. If the cache key is present in the cache, then the item from the cache will be returned instead. Setting this
/// to null would force to query from the database.
/// </param>
/// <param name="cacheItemExpiration">The expiration in minutes of the cache item.</param>
/// <param name="commandTimeout">The command timeout in seconds to be used.</param>
/// <param name="transaction">The transaction to be used.</param>
/// <param name="cache">The cache object to be used.</param>
/// <param name="trace">The trace object to be used.</param>
/// <param name="statementBuilder">The statement builder object to be used.</param>
/// <returns>An enumerable list of data entity object.</returns>
internal static Task <IEnumerable <TEntity> > QueryAllAsyncInternalBase <TEntity>(this IDbConnection connection,
IEnumerable <OrderField> orderBy = null,
string hints = null,
string cacheKey = null,
int cacheItemExpiration = Constant.DefaultCacheItemExpirationInMinutes,
int?commandTimeout = null,
IDbTransaction transaction = null,
ICache cache = null,
ITrace trace = null,
IStatementBuilder statementBuilder = null)
where TEntity : class
{
// Get Cache
if (cacheKey != null)
{
var item = cache?.Get(cacheKey, false);
if (item != null)
{
return(Task.FromResult((IEnumerable <TEntity>)item.Value));
}
}
// Variables
var commandType = CommandType.Text;
var request = new QueryAllRequest(typeof(TEntity),
connection,
FieldCache.Get <TEntity>(),
orderBy,
hints,
statementBuilder);
var commandText = CommandTextCache.GetQueryAllText(request);
var param = (object)null;
// Database pre-touch for field definitions
DbFieldCache.Get(connection, ClassMappedNameCache.Get <TEntity>());
// Before Execution
if (trace != null)
{
var cancellableTraceLog = new CancellableTraceLog(commandText, param, null);
trace.BeforeQueryAll(cancellableTraceLog);
if (cancellableTraceLog.IsCancelled)
{
if (cancellableTraceLog.IsThrowException)
{
throw new CancelledExecutionException(commandText);
}
return(Task.FromResult <IEnumerable <TEntity> >(null));
}
commandText = (cancellableTraceLog.Statement ?? commandText);
param = (cancellableTraceLog.Parameter ?? param);
}
// Before Execution Time
var beforeExecutionTime = DateTime.UtcNow;
// Actual Execution
var result = ExecuteQueryAsyncInternal <TEntity>(connection: connection,
commandText: commandText,
param: param,
commandType: commandType,
commandTimeout: commandTimeout,
transaction: transaction,
basedOnFields: false,
skipCommandArrayParametersCheck: true);
// After Execution
if (trace != null)
{
trace.AfterQueryAll(new TraceLog(commandText, param, result,
DateTime.UtcNow.Subtract(beforeExecutionTime)));
}
// Set Cache
if (cacheKey != null /* && result.Result?.Any() == true */)
{
cache?.Add(cacheKey, result, cacheItemExpiration);
}
// Result
return(result);
}
/// <summary>
/// Delete the existing rows via entities by bulk in an asynchronous way. Underneath this operation is a call directly to the existing
/// <see cref="NpgsqlConnection.BeginBinaryExport(string)"/> method via the 'BinaryImport' extended method.
/// </summary>
/// <typeparam name="TEntity">The type of the entity.</typeparam>
/// <param name="repository">The instance of <see cref="BaseRepository{TEntity, TDbConnection}"/> object.</param>
/// <param name="tableName">The name of the target table from the database.</param>
/// <param name="entities">The list of entities to be bulk-deleted to the target table.
/// This can be an <see cref="IEnumerable{T}"/> of the following objects (<typeparamref name="TEntity"/> (as class/model), <see cref="ExpandoObject"/>,
/// <see cref="IDictionary{TKey, TValue}"/> (of <see cref="string"/>/<see cref="object"/>) and Anonymous Types).</param>
/// <param name="qualifiers">The list of qualifier fields to be used during the operation. Ensure to target the indexed columns to make the execution more performant. If not specified, the primary key will be used.</param>
/// <param name="mappings">The list of mappings to be used. If not specified, only the matching properties/columns from the target table will be used. (This is not the entity mappings, but is working on top of it)</param>
/// <param name="bulkCopyTimeout">The timeout expiration of the operation (see <see cref="NpgsqlBinaryImporter.Timeout"/>).</param>
/// <param name="batchSize">The size per batch to be sent to the database. If not specified, all the rows of the table will be sent together in one-go.</param>
/// <param name="keepIdentity">A value that indicates whether the existing identity property values from the entities will be kept during the operation.</param>
/// <param name="pseudoTableType">The value that defines whether an actual or temporary table will be created for the pseudo-table.</param>
/// <param name="transaction">The current transaction object in used. If not specified, an implicit transaction will be created and used.</param>
/// <param name="cancellationToken">The <see cref="CancellationToken"/> object to be used during the asynchronous operation.</param>
/// <returns>The number of rows that has been deleted from the target table.</returns>
public static async Task <int> BinaryBulkDeleteAsync <TEntity>(this BaseRepository <TEntity, NpgsqlConnection> repository,
string tableName,
IEnumerable <TEntity> entities,
IEnumerable <Field> qualifiers = null,
IEnumerable <NpgsqlBulkInsertMapItem> mappings = null,
int?bulkCopyTimeout = null,
int?batchSize = null,
bool keepIdentity = true,
BulkImportPseudoTableType pseudoTableType = default,
NpgsqlTransaction transaction = null,
CancellationToken cancellationToken = default)
where TEntity : class =>
await repository.DbRepository.BinaryBulkDeleteAsync <TEntity>(tableName : (tableName ?? ClassMappedNameCache.Get <TEntity>()),
entities : entities,
qualifiers : qualifiers,
mappings : mappings,
bulkCopyTimeout : bulkCopyTimeout,
batchSize : batchSize,
keepIdentity : keepIdentity,
pseudoTableType : pseudoTableType,
transaction : transaction,
cancellationToken : cancellationToken);
/// <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>
///
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="connection"></param>
/// <param name="tableName"></param>
/// <param name="entities"></param>
/// <param name="mappings"></param>
/// <param name="bulkCopyTimeout"></param>
/// <param name="batchSize"></param>
/// <param name="identityBehavior"></param>
/// <param name="pseudoTableType"></param>
/// <param name="transaction"></param>
/// <param name="cancellationToken"></param>
/// <returns></returns>
private static async Task <int> BinaryBulkInsertBaseAsync <TEntity>(this NpgsqlConnection connection,
string tableName,
IEnumerable <TEntity> entities,
IEnumerable <NpgsqlBulkInsertMapItem> mappings = null,
int?bulkCopyTimeout = null,
int?batchSize = null,
BulkImportIdentityBehavior identityBehavior = default,
BulkImportPseudoTableType pseudoTableType = default,
NpgsqlTransaction transaction = null,
CancellationToken cancellationToken = default)
where TEntity : class
{
var entityType = entities?.First()?.GetType() ?? typeof(TEntity); // Solving the anonymous types
var isDictionary = entityType.IsDictionaryStringObject();
var dbSetting = connection.GetDbSetting();
var dbFields = await DbFieldCache.GetAsync(connection, tableName, transaction, cancellationToken);
var pseudoTableName = tableName;
return(await PseudoBasedBinaryImportAsync(connection,
tableName,
bulkCopyTimeout,
dbFields,
// getPseudoTableName
() =>
pseudoTableName = GetBinaryBulkInsertPseudoTableName(tableName ?? ClassMappedNameCache.Get <TEntity>(), dbSetting),
// getMappings
() =>
{
var includeIdentity = identityBehavior == BulkImportIdentityBehavior.KeepIdentity;
var isPrimaryAnIdentity = IsPrimaryAnIdentity(dbFields);
var includePrimary = isPrimaryAnIdentity == false ||
(isPrimaryAnIdentity && includeIdentity);
return mappings = mappings?.Any() == true ? mappings :
isDictionary ?
GetMappings(entities?.First() as IDictionary <string, object>,
dbFields,
includePrimary,
includeIdentity,
dbSetting) :
GetMappings(dbFields,
PropertyCache.Get(entityType),
includePrimary,
includeIdentity,
dbSetting);
},
// binaryImport
async (tableName) =>
await connection.BinaryImportAsync <TEntity>(tableName,
entities,
mappings,
dbFields,
bulkCopyTimeout,
batchSize,
identityBehavior,
dbSetting,
transaction,
cancellationToken),
// getMergeToPseudoCommandText
() =>
GetInsertCommandText(pseudoTableName,
tableName,
mappings.Select(mapping => new Field(mapping.DestinationColumn)),
dbFields.FirstOrDefault(dbField => dbField.IsIdentity)?.AsField(),
identityBehavior,
dbSetting),
// setIdentities
(identityResults) =>
SetIdentities(entityType, entities, dbFields, identityResults, dbSetting),
null,
true,
identityBehavior,
pseudoTableType,
dbSetting,
transaction,
cancellationToken));
}
/// <summary>
///
/// </summary>
/// <typeparam name="TPrimaryKey"></typeparam>
/// <param name="connection"></param>
/// <param name="tableName"></param>
/// <param name="primaryKeys"></param>
/// <param name="bulkCopyTimeout"></param>
/// <param name="batchSize"></param>
/// <param name="pseudoTableType"></param>
/// <param name="transaction"></param>
/// <returns></returns>
private static int BinaryBulkDeleteByKeyBase <TPrimaryKey>(this NpgsqlConnection connection,
string tableName,
IEnumerable <TPrimaryKey> primaryKeys,
int?bulkCopyTimeout = null,
int?batchSize = null,
BulkImportPseudoTableType pseudoTableType = default,
NpgsqlTransaction transaction = null)
{
var identityBehavior = BulkImportIdentityBehavior.Unspecified;
var dbSetting = connection.GetDbSetting();
var dbFields = DbFieldCache.Get(connection, tableName, transaction);
var primaryKey = dbFields.FirstOrDefault(dbField => dbField.IsPrimary);
var pseudoTableName = tableName;
IEnumerable <NpgsqlBulkInsertMapItem> mappings = null;
return(PseudoBasedBinaryImport(connection,
tableName,
bulkCopyTimeout,
dbFields,
// getPseudoTableName
() =>
pseudoTableName = GetBinaryBulkDeleteByKeyPseudoTableName(tableName ?? ClassMappedNameCache.Get <TPrimaryKey>(), dbSetting),
// getMappings
() =>
mappings = new[]
{
new NpgsqlBulkInsertMapItem(primaryKey.Name, primaryKey.Name)
},
// binaryImport
(tableName) =>
connection.BinaryImport(tableName,
GetExpandoObjectData(primaryKeys, primaryKey.AsField()),
mappings,
dbFields,
bulkCopyTimeout,
batchSize,
identityBehavior,
dbSetting,
transaction),
// getDeleteToPseudoCommandText
() =>
GetDeleteByKeyCommandText(pseudoTableName,
tableName,
dbFields.FirstOrDefault(dbField => dbField.IsPrimary)?.AsField(),
dbSetting),
// setIdentities
null,
null,
false,
identityBehavior,
pseudoTableType,
dbSetting,
transaction));
}
/// <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>
///
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="connection"></param>
/// <param name="tableName"></param>
/// <param name="entities"></param>
/// <param name="qualifiers"></param>
/// <param name="mappings"></param>
/// <param name="bulkCopyTimeout"></param>
/// <param name="batchSize"></param>
/// <param name="keepIdentity"></param>
/// <param name="pseudoTableType"></param>
/// <param name="transaction"></param>
/// <returns></returns>
private static int BinaryBulkDeleteBase <TEntity>(this NpgsqlConnection connection,
string tableName,
IEnumerable <TEntity> entities,
IEnumerable <Field> qualifiers = null,
IEnumerable <NpgsqlBulkInsertMapItem> mappings = null,
int?bulkCopyTimeout = null,
int?batchSize = null,
bool keepIdentity = true,
BulkImportPseudoTableType pseudoTableType = default,
NpgsqlTransaction transaction = null)
where TEntity : class
{
var entityType = entities?.First()?.GetType() ?? typeof(TEntity); // Solving the anonymous types
var isDictionary = entityType.IsDictionaryStringObject();
var dbSetting = connection.GetDbSetting();
var dbFields = DbFieldCache.Get(connection, tableName, transaction);
var pseudoTableName = tableName;
var identityBehavior = keepIdentity ? BulkImportIdentityBehavior.KeepIdentity : BulkImportIdentityBehavior.Unspecified;
return(PseudoBasedBinaryImport(connection,
tableName,
bulkCopyTimeout,
dbFields,
// getPseudoTableName
() =>
pseudoTableName = GetBinaryBulkDeletePseudoTableName(tableName ?? ClassMappedNameCache.Get <TEntity>(), dbSetting),
// getMappings
() =>
{
var includeIdentity = identityBehavior == BulkImportIdentityBehavior.KeepIdentity;
var includePrimary = true;
return mappings = mappings?.Any() == true ? mappings :
isDictionary ?
GetMappings(entities?.First() as IDictionary <string, object>,
dbFields,
includePrimary,
includeIdentity,
dbSetting) :
GetMappings(dbFields,
PropertyCache.Get(entityType),
includePrimary,
includeIdentity,
dbSetting);
},
// binaryImport
(tableName) =>
connection.BinaryImport <TEntity>(tableName,
entities,
mappings,
dbFields,
bulkCopyTimeout,
batchSize,
identityBehavior,
dbSetting,
transaction),
// getDeleteToPseudoCommandText
() =>
GetDeleteCommandText(pseudoTableName,
tableName,
mappings.Select(mapping => new Field(mapping.DestinationColumn)),
qualifiers,
dbFields.FirstOrDefault(dbField => dbField.IsPrimary)?.AsField(),
dbFields.FirstOrDefault(dbField => dbField.IsIdentity)?.AsField(),
identityBehavior,
dbSetting),
// setIdentities
(identityResults) =>
SetIdentities(entityType, entities, dbFields, identityResults, dbSetting),
qualifiers,
false,
identityBehavior,
pseudoTableType,
dbSetting,
transaction));
}
/// <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>
/// Appends the mapped entity name to the SQL Query Statement.
/// </summary>
/// <returns>The current instance.</returns>
public QueryBuilder <TEntity> TableName()
{
return(Append(ClassMappedNameCache.Get <TEntity>().AsQuoted(true)));
}
/// <summary>
/// Appends the mapped entity name to the SQL Query Statement.
/// </summary>
/// <typeparam name="TEntity">The type of data entity object bound for the SQL Statement to be created.</typeparam>
/// <param name="dbSetting">The currently in used <see cref="IDbSetting"/> object.</param>
/// <returns>The current instance.</returns>
public QueryBuilder TableNameFrom <TEntity>(IDbSetting dbSetting)
where TEntity : class
{
return(TableNameFrom(ClassMappedNameCache.Get <TEntity>(), dbSetting));
}
/// <summary>
/// Appends the mapped entity name to the SQL Query Statement based on the mapped command.
/// </summary>
/// <param name="command">The command where the mapping is defined.</param>
/// <returns>The current instance.</returns>
public QueryBuilder <TEntity> TableFrom(Command command)
{
return(Append($"{ClassMappedNameCache.Get<TEntity>(command).AsQuoted(true)}"));
}
