public async Task <ICursorPageSlice <Droid> > GetPagedDroidCharactersAsync(
IEnumerable <Field> selectFields,
IEnumerable <OrderField> sortFields,
IRepoDbCursorPagingParams pagingParams
)
{
await using var sqlConn = CreateConnection();
//BBernard - 08/09/2021
//NOTE: This Examples shows the use of RepoDb mapping and Raw SQL with Batch Slice Query capabilities;
// which enables powerful field processing and features not supported by QueryField/QueryGroup objects
// (e.g. LOWER(), TRIM(), or Full Text Search via CONTAINS() and FREETEXT()).
var idFieldName = PropertyMappedNameCache.Get <CharacterDbModel>(c => c.Id);
var pageSlice = await sqlConn.GraphQLBatchSliceQueryAsync <CharacterDbModel>(
orderBy : sortFields ?? DefaultCharacterSortFields,
fields : selectFields,
whereRawSql : new RawSqlWhere(@$ "{idFieldName} >= @StartId AND {idFieldName} < @EndId", new { StartId = 2000, EndId = 3000 }),
pagingParams : pagingParams,
commandTimeout : 15
);
var convertedSlice = pageSlice.AsMappedType(r => (Droid)MapDbModelToCharacterModel(r));
return(convertedSlice);
}
/// <summary>
/// Base Repository extension for Relay Cursor Paginated Batch Query capability.
///
/// Public Facade method to provide dynamically paginated results using Relay Cursor slicing.
/// Relay spec cursor algorithm is implemented for Sql Server on top of RepoDb.
///
/// NOTE: Since RepoDb supports only Offset Batch querying, this logic provided as an extension
/// of RepoDb core functionality; and if this is ever provided by the Core functionality
/// this facade will remain as a proxy to core feature.
///
/// NOTE: For Relay Spec details and Cursor Algorithm see:
/// https://relay.dev/graphql/connections.htm#sec-Pagination-algorithm
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <typeparam name="TDbConnection"></typeparam>
/// <param name="baseRepo">Extends the RepoDb BaseRepository abstraction</param>
/// <param name="orderBy"></param>
/// <param name="whereExpression"></param>
/// <param name="pagingParams"></param>
/// <param name="tableName"></param>
/// <param name="hints"></param>
/// <param name="fields"></param>
/// <param name="commandTimeout"></param>
/// <param name="transaction"></param>
/// <param name="logTrace"></param>
/// <param name="cancellationToken"></param>
/// <returns>CursorPageSlice<TEntity></returns>
public static async Task <CursorPageSlice <TEntity> > GraphQLBatchSliceQueryAsync <TEntity, TDbConnection>(
this BaseRepository <TEntity, TDbConnection> baseRepo,
IEnumerable <OrderField> orderBy,
Expression <Func <TEntity, bool> > whereExpression,
IRepoDbCursorPagingParams pagingParams = default,
string tableName = null,
string hints = null,
IEnumerable <Field> fields = null,
int?commandTimeout = null,
IDbTransaction transaction = null,
Action <string> logTrace = null,
CancellationToken cancellationToken = default
)
//ALL entities retrieved and Mapped for Cursor Pagination must support IHaveCursor interface.
where TEntity : class
where TDbConnection : DbConnection, new()
{
return(await baseRepo.GraphQLBatchSliceQueryForRepoInternalAsync <TEntity, TDbConnection>(
orderBy : orderBy,
where : whereExpression != null?QueryGroup.Parse <TEntity>(whereExpression) : (QueryGroup)null,
pagingParams : pagingParams,
tableName : tableName,
hints : hints,
fields : fields,
commandTimeout : commandTimeout,
transaction : transaction,
logTrace : logTrace,
cancellationToken : cancellationToken
).ConfigureAwait(false));
}
/// <summary>
/// Base DbConnection (SqlConnection) extension for Relay Cursor Paginated Batch Query capability.
///
/// Public Facade method to provide dynamically paginated results using Relay Cursor slicing.
/// Relay spec cursor algorithm is implemented for Sql Server on top of RepoDb.
///
/// NOTE: Since RepoDb supports only Offset Batch querying, this logic provided as an extension
/// of RepoDb core functionality; and if this is ever provided by the Core functionality
/// this facade will remain as a proxy to core feature.
///
/// NOTE: For Relay Spec details and Cursor Algorithm see:
/// https://relay.dev/graphql/connections.htm#sec-Pagination-algorithm
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="dbConnection">Extends DbConnection directly</param>
/// <param name="orderBy"></param>
/// <param name="whereRawSql"></param>
/// <param name="pagingParams"></param>
/// <param name="tableName"></param>
/// <param name="hints"></param>
/// <param name="fields"></param>
/// <param name="commandTimeout"></param>
/// <param name="transaction"></param>
/// <param name="logTrace"></param>
/// <param name="cancellationToken"></param>
/// <returns>CursorPageSlice<TEntity></returns>
public static async Task <CursorPageSlice <TEntity> > GraphQLBatchSliceQueryAsync <TEntity>(
this DbConnection dbConnection,
IEnumerable <OrderField> orderBy,
RawSqlWhere whereRawSql = null, //NOTE: This Overload allows cases where NO WHERE Filter is needed...
IRepoDbCursorPagingParams pagingParams = default,
string tableName = null,
string hints = null,
IEnumerable <Field> fields = null,
int?commandTimeout = null,
IDbTransaction transaction = null,
Action <string> logTrace = null,
CancellationToken cancellationToken = default
)
//ALL entities retrieved and Mapped for Cursor Pagination must support IHaveCursor interface.
where TEntity : class
{
return(await dbConnection.GraphQLBatchSliceQueryInternalAsync <TEntity>(
orderBy : orderBy,
//NOTE: Must cast to raw object to prevent Recursive execution with our catch-all overload...
where : whereRawSql,
pagingParams : pagingParams,
tableName : tableName,
hints : hints,
fields : fields,
commandTimeout : commandTimeout,
transaction : transaction,
logTrace : logTrace,
cancellationToken : cancellationToken
).ConfigureAwait(false));
}
/// <summary>
/// Base Repository extension for Relay Cursor Paginated Batch Query capability.
///
/// Public Facade method to provide dynamically paginated results using Relay Cursor slicing.
/// Relay spec cursor algorithm is implemented for Sql Server on top of RepoDb.
///
/// NOTE: Since RepoDb supports only Offset Batch querying, this logic provided as an extension
/// of RepoDb core functionality; and if this is ever provided by the Core functionality
/// this facade will remain as a proxy to core feature.
///
/// NOTE: For Relay Spec details and Cursor Algorithm see:
/// https://relay.dev/graphql/connections.htm#sec-Pagination-algorithm
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <typeparam name="TDbConnection"></typeparam>
/// <param name="baseRepo">Extends the RepoDb BaseRepository abstraction</param>
/// <param name="orderBy"></param>
/// <param name="where"></param>
/// <param name="pagingParams"></param>
/// <param name="tableName"></param>
/// <param name="hints"></param>
/// <param name="fields"></param>
/// <param name="commandTimeout"></param>
/// <param name="transaction"></param>
/// <param name="logTrace"></param>
/// <param name="cancellationToken"></param>
/// <returns>CursorPageSlice<TEntity></returns>
private static async Task <CursorPageSlice <TEntity> > GraphQLBatchSliceQueryForRepoInternalAsync <TEntity, TDbConnection>(
this BaseRepository <TEntity, TDbConnection> baseRepo,
IEnumerable <OrderField> orderBy,
object where = null,
IRepoDbCursorPagingParams pagingParams = default,
string tableName = null,
string hints = null,
IEnumerable <Field> fields = null,
int?commandTimeout = null,
IDbTransaction transaction = null,
Action <string> logTrace = null,
CancellationToken cancellationToken = default
)
//ALL entities retrieved and Mapped for Cursor Pagination must support IHaveCursor interface.
where TEntity : class
where TDbConnection : DbConnection, new()
{
//Below Logic mirrors that of RepoDb Source for managing the Connection (PerInstance or PerCall)!
var connection = (DbConnection)(transaction?.Connection ?? baseRepo.CreateConnection());
try
{
var cursorPageResult = await connection.GraphQLBatchSliceQueryInternalAsync <TEntity>(
orderBy : orderBy,
where : where,
pagingParams : pagingParams,
tableName : tableName,
hints : hints,
fields : fields,
commandTimeout : commandTimeout,
transaction : transaction,
logTrace : logTrace,
cancellationToken : cancellationToken
).ConfigureAwait(false);
return(cursorPageResult);
}
//catch
//{
// // Throw back the error
// throw;
//}
finally
{
// Dispose the connection
baseRepo.DisposeConnectionForPerCallExtension(connection, transaction);
}
}
public async Task <ICursorPageSlice <Human> > GetPagedHumanCharactersAsync(
IEnumerable <Field> selectFields,
IEnumerable <OrderField> sortFields,
IRepoDbCursorPagingParams pagingParams
)
{
await using var sqlConn = CreateConnection();
var pageSlice = await sqlConn.GraphQLBatchSliceQueryAsync <CharacterDbModel>(
orderBy : sortFields ?? DefaultCharacterSortFields,
fields : selectFields,
whereExpression : c => c.Id >= 1000 && c.Id <= 1999,
pagingParams : pagingParams,
commandTimeout : 15
);
var convertedSlice = pageSlice.AsMappedType(r => (Human)MapDbModelToCharacterModel(r));
return(convertedSlice);
}
public async Task <ICursorPageSlice <ICharacter> > GetCursorPagedCharactersAsync(
IEnumerable <Field> selectFields,
IEnumerable <OrderField> sortFields,
IRepoDbCursorPagingParams pagingParams
)
{
await using var sqlConn = CreateConnection();
var pageSlice = await sqlConn.GraphQLBatchSliceQueryAsync <CharacterDbModel>(
fields : selectFields,
orderBy : sortFields ?? DefaultCharacterSortFields,
pagingParams : pagingParams,
logTrace : s => Debug.WriteLine(s),
commandTimeout : 15
);
var convertedSlice = pageSlice.AsMappedType(r => MapDbModelToCharacterModel(r));
return(convertedSlice);
}
public async Task <ICursorPageSlice <ICharacter> > GetCharacterFriendsAsync(int characterId, IRepoDbCursorPagingParams pagingParams)
{
await using var sqlConn = CreateConnection();
var results = await sqlConn.GraphQLBatchSliceQueryAsync <CharacterFriendDbModel>(
whereExpression : f => f.FriendOfId == characterId,
//Always include a Default Sort Order (for paging)
orderBy : OrderField.Parse(new { Name = Order.Ascending }),
pagingParams : pagingParams,
commandTimeout : 15
);
var mappedResults = results.AsMappedType(c => MapDbModelToCharacterModel(c));
return(mappedResults);
}
/// <summary>
/// Base DbConnection (SqlConnection) extension for Relay Cursor Paginated Batch Query capability.
///
/// Public Facade method to provide dynamically paginated results using Relay Cursor slicing.
/// Relay spec cursor algorithm is implemented for Sql Server on top of RepoDb.
///
/// NOTE: Since RepoDb supports only Offset Batch querying, this logic provided as an extension
/// of RepoDb core functionality; and if this is ever provided by the Core functionality
/// this facade will remain as a proxy to core feature.
///
/// NOTE: For Relay Spec details and Cursor Algorithm see:
/// https://relay.dev/graphql/connections.htm#sec-Pagination-algorithm
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="dbConnection">Extends DbConnection directly</param>
/// <param name="orderBy"></param>
/// <param name="where">May be either a QueryGroup or RawSqlWhere object</param>
/// <param name="pagingParams"></param>
/// <param name="tableName"></param>
/// <param name="hints"></param>
/// <param name="fields"></param>
/// <param name="commandTimeout"></param>
/// <param name="transaction"></param>
/// <param name="logTrace"></param>
/// <param name="cancellationToken"></param>
/// <returns>CursorPageSlice<TEntity></returns>
internal static async Task <CursorPageSlice <TEntity> > GraphQLBatchSliceQueryInternalAsync <TEntity>(
this DbConnection dbConnection,
IEnumerable <OrderField> orderBy,
object where = null, //NOTE: May be either a QueryGroup or RawSqlWhere object
IRepoDbCursorPagingParams pagingParams = default,
string tableName = null,
string hints = null,
IEnumerable <Field> fields = null,
int?commandTimeout = null,
IDbTransaction transaction = null,
Action <string> logTrace = null,
CancellationToken cancellationToken = default
)
//ALL entities retrieved and Mapped for Cursor Pagination must support IHaveCursor interface.
where TEntity : class
{
if (orderBy == null)
{
throw new ArgumentNullException(nameof(orderBy), "A sort order must be specified to provide valid cursor paging results.");
}
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 dbConnection
.GetValidatedDbFieldsAsync(dbTableName, selectFields.Distinct())
.ConfigureAwait(false);
//Dynamically handle RepoDb where filters (QueryGroup or now supporting Raw Sql and Params object)...
var validatedWhereParams = where switch
{
QueryGroup whereQueryGroup => RepoDbQueryGroupProxy.GetMappedParamsObject <TEntity>(whereQueryGroup),
RawSqlWhere whereRawSql => whereRawSql.WhereParams,
_ => null
};
//Build the Cursor Paging query...
var querySliceInfo = RepoDbBatchSliceQueryBuilder.BuildSqlServerBatchSliceQuery <TEntity>(
tableName: dbTableName,
fields: validSelectFields,
orderBy: orderBy,
where : where,
hints: hints,
afterCursorIndex: pagingParams?.AfterIndex,
firstTake: pagingParams?.First,
beforeCursorIndex: pagingParams?.BeforeIndex,
lastTake: pagingParams?.Last,
//Optionally we compute the Total Count only when requested!
includeTotalCountQuery: pagingParams?.IsTotalCountRequested ?? false
);
//Now we can execute the process and get the results!
var cursorPageResult = await dbConnection.ExecuteBatchSliceQueryAsync <TEntity>(
sqlQuerySliceInfo : querySliceInfo,
queryParams : validatedWhereParams,
tableName : dbTableName,
commandTimeout : commandTimeout,
transaction : transaction,
logTrace : logTrace,
cancellationToken : cancellationToken
).ConfigureAwait(false);
return(cursorPageResult);
}