/// <summary>
/// Base DbConnection (SqlConnection) extension for Offset Paginated Batch Query capability.
///
/// Public Facade method to provide dynamically paginated results using Offset based paging/slicing.
///
/// NOTE: Since RepoDb supports only Batch querying using Page Number and Page Size -- it's less flexible
/// than pure Offset based paging which uses Skip/Take. Therefore, this logic provides an extension
/// of RepoDb core functionality; and if this is ever provided by the Core functionality
/// this facade will remain as a proxy to the core feature.
///
/// NOTE: Cursor Slice Querying is more flexible and works perfectly for Offset Based processing also so this
/// represents a facade around the Cursor Page slicing that maps between Skip/Take and Cursor paging paradigm.
///
/// NOTE: If the implementor needs further optimization then it's recommended to implement the optimized query
/// exactly as required; this should work well for many common use cases.
///
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="dbConnection">Extends the RepoDb BaseRepository abstraction</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>OffsetPageResults<TEntity></returns>
public static async Task <OffsetPageResults <TEntity> > GraphQLBatchSkipTakeQueryAsync <TEntity>(
this DbConnection dbConnection,
IEnumerable <OrderField> orderBy,
RawSqlWhere whereRawSql = null, //NOTE: This Overload allows cases where NO WHERE Filter is needed...
IRepoDbOffsetPagingParams 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
{
//Slice Querying is more flexible and works perfectly for Offset Based processing also so there is no
// need to maintain duplicated code for the less flexible paging approach since we can provide
// the simplified Offset Paging facade on top of the existing Slice Queries!
var sliceResults = await dbConnection.GraphQLBatchSliceQueryAsync <TEntity>(
orderBy : orderBy,
whereRawSql : whereRawSql,
pagingParams : ConvertOffsetParamsToCursorParams(pagingParams),
tableName : tableName,
hints : hints,
fields : fields,
commandTimeout : commandTimeout,
transaction : transaction,
logTrace : logTrace,
cancellationToken : cancellationToken
).ConfigureAwait(false);
//Map the Slice into the OffsetPageResults for simplified processing by calling code...
return(sliceResults.ToOffsetPageResults());;
}
/// <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>
/// BBernard
/// Borrowed and Adapted from RepoDb source code: SqlServerStatementBuilder.CreateBatchQuery.
/// NOTE: Due to internally only accessible elements it was easier to just construct the query as needed
/// to allow us to return the RowNumber as the CursorIndex!
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="tableName"></param>
/// <param name="fields"></param>
/// <param name="orderBy"></param>
/// <param name="where">May be either a QueryGroup or RawSqlWhere object</param>
/// <param name="hints"></param>
/// <param name="afterCursorIndex"></param>
/// <param name="firstTake"></param>
/// <param name="beforeCursorIndex"></param>
/// <param name="lastTake"></param>
/// <param name="includeTotalCountQuery"></param>
/// <returns></returns>
public static SqlQuerySliceInfo BuildSqlServerBatchSliceQuery <TEntity>(
string tableName,
IEnumerable <Field> fields,
IEnumerable <OrderField> orderBy,
object where = null,
string hints = null,
int?afterCursorIndex = null,
int?firstTake = null,
int?beforeCursorIndex = null,
int?lastTake = null,
bool includeTotalCountQuery = true
)
where TEntity : class
{
var dbSetting = RepoDbSettings.SqlServerSettings;
string cursorIndexName = nameof(IHaveCursor.CursorIndex);
var fieldsList = fields.ToList();
var orderByList = orderBy.ToList();
var orderByLookup = orderByList.ToLookup(o => o.Name.ToLower());
//Ensure that we Remove any risk of Name conflicts with the CursorIndex field on the CTE
// because we are dynamically adding ROW_NUMBER() as [CursorIndex]! And, ensure
// that there are no conflicts with the OrderBy
var cteFields = new List <Field>(fieldsList);
cteFields.RemoveAll(f => f.Name.Equals(cursorIndexName, StringComparison.OrdinalIgnoreCase));
//We must ensure that all OrderBy fields are also part of the CTE Select Clause so that they are
// actually available to be sorted on (or else 'Invalid Column Errors' will occur if the field is not
// originally part of the Select Fields list.
var missingSortFieldNames = orderByList.Select(o => o.Name).Except(cteFields.Select(f => f.Name)).ToList();
if (missingSortFieldNames.Count > 0)
{
cteFields.AddRange(missingSortFieldNames.Select(n => new Field(n)));
}
var selectFields = new List <Field>();
selectFields.AddRange(fieldsList);
selectFields.Add(new Field(cursorIndexName));
// Initialize the builder
var cteBuilder = new QueryBuilder();
//Support either QueryGroup object model or Raw SQL; which enables support for complex Field processing & filtering not supported
// by QueryField/QueryGroup objects (e.g. LOWER(), TRIM()), use of Sql Server Full Text Searching on Fields (e.g. CONTAINS(), FREETEXT()), etc.
//var sqlWhereDataFilter = whereQueryGroup?.GetString(0, dbSetting) ?? whereRawSql;
var sqlWhereDataFilter = where switch
{
QueryGroup whereQueryGroup => whereQueryGroup?.GetString(0, dbSetting),
RawSqlWhere whereRawSql => whereRawSql.RawSqlWhereClause,
string whereRawSql => whereRawSql,
_ => null
};
bool isWhereFilterSpecified = !string.IsNullOrWhiteSpace(sqlWhereDataFilter);
//Dynamically build/optimize the core data SQL that will be used as a CTE wrapped by the Pagination logic!
cteBuilder.Clear()
.Select()
.RowNumber().Over().OpenParen().OrderByFrom(orderByList, dbSetting).CloseParen().As($"[{cursorIndexName}],")
.FieldsFrom(cteFields, dbSetting)
.From().TableNameFrom(tableName, dbSetting)
.HintsFrom(hints);
if (isWhereFilterSpecified)
{
cteBuilder
.Where()
.WriteText(sqlWhereDataFilter);
}
var sqlCte = cteBuilder.GetString();
var sqlWhereClauseSliceInfo = BuildRelaySpecQuerySliceInfo(cursorIndexName, afterCursorIndex, beforeCursorIndex, firstTake, lastTake);
// Build the base Paginated Query
var sqlBuilder = new QueryBuilder();
sqlBuilder.Clear()
.With()
.WriteText("CTE").As().OpenParen()
//Dynamically insert the CTE that is built separately...
.WriteText(sqlCte)
.CloseParen()
.Select()
.FieldsFrom(selectFields, dbSetting)
.From().WriteText("CTE")
//Implement Relay Spec Cursor Slicing Algorithm!
.WriteText(sqlWhereClauseSliceInfo.SQL)
.OrderByFrom(orderByList, dbSetting)
.End();
if (includeTotalCountQuery)
{
//////Look for PKey Field to use as the Count Column... as this just makes sense...
//////NOTE: COUNT(1) may not work as expected when column permission are in use, so we use a real field.
//////NOTE:
////var countField = PropertyCache.Get<TEntity>().FirstOrDefault(p => p.GetPrimaryAttribute() != null)?.AsField()
//// ?? selectFields.FirstOrDefault();
//Add SECOND Count Query into the Query so it can be executed as an efficient MultipleQuery!
//NOTE: For optimization we do not need to Sort or, select more than one field to get the Total Count,
// the only thing that changes this result is the Where filter fields!
//NOTE: TO FIX RISK of Null values being skipped by the Count aggregation, this is changed to use the standard COUNT(*),
// which is RepoDb's default behavior if field is null, to ensure that we get the true row count and nothing is
// eliminated due to Null values.
//NOTE We also rely on SqlServer to optimize this query instead of trying to do too much ourselves (with other unknown risks
// such as column permissions, etc.
sqlBuilder.Select()
.Count(null, dbSetting)
.From().TableNameFrom(tableName, dbSetting)
.HintsFrom(hints);
if (isWhereFilterSpecified)
{
sqlBuilder
.Where()
.WriteText(sqlWhereDataFilter);
}
sqlBuilder.End();
}
// Build the Query and other Slice Info metadata needed for optimal pagination...
var sqlQuery = sqlBuilder.GetString();
var sqlQuerySliceInfo = new SqlQuerySliceInfo()
{
SQL = sqlQuery,
ExpectedCount = sqlWhereClauseSliceInfo.ExpectedCount,
IsPreviousPagePossible = sqlWhereClauseSliceInfo.IsPreviousPagePossible,
IsNextPagePossible = sqlWhereClauseSliceInfo.IsNextPagePossible,
IsEndIndexOverFetchedForNextPageCheck = sqlWhereClauseSliceInfo.IsEndIndexOverFetchedForNextPageCheck
};
// Return the query
return(sqlQuerySliceInfo);
}
/// <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);
}
