public async Task <PagedCollection <T> > QueryPagedAsync <T>(string sql, IPagedFilter filter, CancellationToken cancellationToken = default) where T : class
{
var orderByIndex = sql.LastIndexOf("ORDER BY", StringComparison.CurrentCultureIgnoreCase);
if (orderByIndex <= 0)
{
throw new SqlSyntaxException("ORDER BY Clause not found. To filter as pagged you need to provide an ORDER BY Clause.");
}
var newSql = sql.Substring(0, orderByIndex);
var query = $"SELECT COUNT(1) FROM ({newSql}) C;" + sql.AddPagination(filter, dataSource);
using var con = GetConnection();
var qResult = await con.QueryMultipleAsync(new CommandDefinition(query, filter, cancellationToken : cancellationToken));
int totalRecords = qResult.ReadFirst <int>();
var result = new PagedCollection <T>(qResult.Read <T>(), filter.Page, filter.PageSize)
{
TotalRecords = totalRecords
};
return(result);
}
internal static string AddPagination(this string rawSql, IPagedFilter pagedFilter, IDataSource dataSource)
{
if (pagedFilter.IsNull())
{
return(rawSql);
}
var recordStart = pagedFilter.Page * pagedFilter.PageSize;
if (recordStart < 0)
{
recordStart = 0;
}
switch (dataSource.Provider)
{
case Provider.PostgreSqL:
return($"{rawSql} OFFSET ({recordStart}) LIMIT @PageSize ");
case Provider.Oracle:
pagedFilter.Page = pagedFilter.Page <= 0 ? 1 : pagedFilter.Page;
return
($" SELECT * FROM (SELECT a.*, rownum r_ FROM ({rawSql}) a WHERE rownum < (({pagedFilter.Page} * {pagedFilter.PageSize}) + 1) ) WHERE r_ >= ((({pagedFilter.Page} - 1) * {pagedFilter.PageSize}) + 1)");
case Provider.MsSql:
return($"{rawSql} OFFSET {recordStart} ROWS FETCH NEXT @PageSize ROWS ONLY");
default:
return($"{rawSql} OFFSET {recordStart} ROWS FETCH NEXT @PageSize ROWS ONLY");
}
}
public RemarkStorage(IStorageClient storageClient,
IPagedFilter <Remark, BrowseRemarks> browseRemarksFilter,
IPagedFilter <Remark, BrowseSimilarRemarks> browseSimilarRemarksFilter,
ICache cache,
RedisSettings redisSettings)
{
_storageClient = storageClient;
_cache = cache;
_browseRemarksFilter = browseRemarksFilter;
_browseSimilarRemarksFilter = browseSimilarRemarksFilter;
_useCache = redisSettings.Enabled;
}
public Task <IEnumerable <T> > QueryListPagedAsync <T>(string sql, IPagedFilter filter, CancellationToken cancellationToken = default)
{
if (sql == null)
{
throw new ArgumentNullException(nameof(sql));
}
if (filter == null)
{
throw new ArgumentNullException(nameof(filter));
}
return(QueryListPagedInternalAsync <T>(sql, filter, cancellationToken));
}
internal static string AddPagination(this string rawSql, IPagedFilter pagedFilter, IDataSource dataSource)
{
if (pagedFilter.IsNull())
{
return(rawSql);
}
var recordStart = (pagedFilter.Page) * pagedFilter.PageSize;
if (recordStart < 0)
{
recordStart = 0;
}
return(dataSource.Provider switch
{
Provider.PostgreSqL => $"{rawSql} OFFSET ({recordStart}) LIMIT @PageSize ",
_ => $"{rawSql} OFFSET {recordStart} ROWS FETCH NEXT @PageSize ROWS ONLY"
});
private async Task <IEnumerable <T> > QueryListPagedInternalAsync <T>(string sql, IPagedFilter filter, CancellationToken cancellationToken = default)
{
var query = sql.AddPagination(filter, dataSource);
using var con = GetConnection();
return(await con.QueryAsync <T>(new CommandDefinition(query, filter, cancellationToken : cancellationToken)).ConfigureAwait(false));
}
public static async Task <IPagedList <TResult> > ToPagedListAsync <TSource, TResult>(this IQueryable <TSource> source, IPagedFilter filter, Expression <Func <TSource, TResult> > map)
{
filter = filter ?? new PagedFilter();
var count = await source.LongCountAsync();
if (count == 0 || filter.Take == 0)
{
return(new PagedList <TResult>
{
Take = filter.Take,
Skip = filter.Skip,
Total = count,
Results = new List <TResult>()
});
}
var results = await source
.Skip(filter.Skip)
.Take(filter.Take)
.ToListAsync();
var func = map.Compile();
return(new PagedList <TResult>
{
Take = filter.Take,
Skip = filter.Skip,
Total = count,
Results = results.Select(func).ToList()
});
}
public static Task <IPagedList <TSource> > ToPagedListAsync <TSource>(this IQueryable <TSource> source, IPagedFilter filter)
{
return(source.ToPagedListAsync(filter, x => x));
}
public async Task <PagedCollection <T> > QueryPagedAsync <T>(string sql, IPagedFilter filter, bool useCount = true,
CancellationToken cancellationToken = default) where T : class
{
if (sql == null)
{
throw new ArgumentNullException(nameof(sql));
}
var orderByIndex = sql.LastIndexOf("ORDER BY", StringComparison.CurrentCultureIgnoreCase);
if (orderByIndex <= 0)
{
throw new SqlSyntaxException(
"ORDER BY Clause not found. To filter as paged you need to provide an ORDER BY Clause.");
}
var newSql = sql.Substring(0, orderByIndex);
var queryPaged = sql.AddPagination(filter, dataSource);
using var con = GetConnection();
if (!useCount)
{
var pagedResult =
await con.QueryAsync <T>(new CommandDefinition(queryPaged, filter,
cancellationToken : cancellationToken)).ConfigureAwait(false);
return(new PagedCollection <T>(pagedResult, filter.Page, filter.PageSize));
}
var queryCount = $"SELECT COUNT(1) FROM ({newSql}) C WITH(NOLOCK)";
var totalRecords = 0;
IEnumerable <T> queryResult = null;
if (dataSource.Provider == Provider.Oracle)
{
totalRecords = await con
.QuerySingleAsync <int>(new CommandDefinition(queryCount, filter,
cancellationToken : cancellationToken))
.ConfigureAwait(false);
queryResult = await con
.QueryAsync <T>(new CommandDefinition(queryPaged, filter, cancellationToken : cancellationToken))
.ConfigureAwait(false);
}
else
{
var query = $"{queryCount}; {queryPaged}";
var qResult = await con.QueryMultipleAsync(new CommandDefinition(query, filter,
cancellationToken : cancellationToken)).ConfigureAwait(false);
totalRecords = qResult.ReadFirst <int>();
queryResult = await qResult.ReadAsync <T>().ConfigureAwait(false);
}
return(new PagedCollection <T>(queryResult, filter.Page, filter.PageSize)
{
TotalRecords = totalRecords
});
}
protected async Task <IPagedList <TResult> > ApplyFilterAsync <TResult>(IQueryable <TResult> query, IPagedFilter filter)
{
var pagedList = new PagedList <TResult>(query, filter.Page, filter.PageSize, filter.PageIndexFrom);
return(await Task.FromResult(pagedList));
}
public static IPagedQueryResult <IEnumerable <TAny> > ToPagedQueryResult <TAny>(this IEnumerable <TAny> obj, IPagedFilter filter, int rowCount)
{
return(obj.ToPagedQueryResult(filter.PageIndex, filter.PageSize, rowCount));
}
public static IQueryable <TAny> GetPaged <TAny>(this IQueryable <TAny> query, IPagedFilter paged)
where TAny : class => GetPaged(query, paged.PageIndex, paged.PageSize);
