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