public PagingSql PageQuery <TEntity>(long skip, long take, string orderByQuery = null, string filterQuery = null)
where TEntity : class
{
var tableInfo = GetTableInfo <TEntity>();
var tableName = tableInfo.TableName;
var primaryKey = tableInfo.Key;
if (string.IsNullOrWhiteSpace(orderByQuery))
{
orderByQuery = $"ORDER BY {primaryKey}";
}
var pagingSql = new PagingSql();
if (string.IsNullOrWhiteSpace(filterQuery))
{
pagingSql.Items = $"SELECT * FROM {tableName} {orderByQuery} OFFSET {skip} ROWS FETCH NEXT {take} ROWS ONLY";
pagingSql.Count = $"SELECT CAST(COUNT(*) AS Int) AS total FROM {tableName}";
}
else
{
pagingSql.Items = $"SELECT DISTINCT {tableName}.* FROM {tableName} {filterQuery} {orderByQuery} OFFSET {skip} ROWS FETCH NEXT {take} ROWS ONLY";
pagingSql.Count = $"SELECT CAST(COUNT(DISTINCT {tableName}.{primaryKey}) AS Int) AS total FROM {tableName} {filterQuery}";
}
return(pagingSql);
}
public PagingSql PageQuery <TEntity>(long skip, long take, string orderByQuery = null, string filterQuery = null)
where TEntity : class
{
TableInfo tableInfo = GetTableInfo <TEntity>();
if (string.IsNullOrWhiteSpace(orderByQuery))
{
orderByQuery = string.Format("ORDER BY {0}", tableInfo.Key);
}
var pagingSql = new PagingSql();
if (string.IsNullOrWhiteSpace(filterQuery))
{
pagingSql.Items = string.Format("SELECT * FROM {0} {1} OFFSET {2} ROWS FETCH NEXT {3} ROWS ONLY", tableInfo.TableName, orderByQuery, skip, take);
pagingSql.Count = string.Format("SELECT CAST(COUNT(*) AS Int) AS total FROM {0}", tableInfo.TableName);
}
else
{
pagingSql.Items = string.Format("SELECT DISTINCT {0}.* FROM {0} {1} {2} OFFSET {3} ROWS FETCH NEXT {4} ROWS ONLY", tableInfo.TableName, filterQuery, orderByQuery, skip, take);
pagingSql.Count = string.Format("SELECT CAST(COUNT(DISTINCT TestTable.Id) AS Int) AS total FROM {0} {1}", tableInfo.TableName, filterQuery);
}
return(pagingSql);
}
public void PageQuery_FilterBySpecified_PassedFilterQueryIsUsed()
{
const string expectedItemsSql = "SELECT DISTINCT TestTable.* FROM TestTable WHERE BY Name like 'J%' ORDER BY Id OFFSET 2 ROWS FETCH NEXT 5 ROWS ONLY";
const string expectedCountSql = "SELECT CAST(COUNT(DISTINCT TestTable.Id) AS Int) AS total FROM TestTable WHERE BY Name like 'J%'";
IQueryBuilder queryBuilder = GetQueryBuilder();
PagingSql pagingSql = queryBuilder.PageQuery <TestEntityWithAutoIncreamentId>(2, 5, filterQuery: "WHERE BY Name like 'J%'");
Assert.AreEqual(expectedItemsSql, ReplaceNextLineAndTab(pagingSql.Items));
Assert.AreEqual(expectedCountSql, ReplaceNextLineAndTab(pagingSql.Count));
}
public void PageQuery_OrderBySpecified_PassedOrderByIsUsed()
{
const string expectedItemsSql = "SELECT * FROM TestTable ORDER BY Name OFFSET 2 ROWS FETCH NEXT 5 ROWS ONLY";
const string expectedCountSql = "SELECT CAST(COUNT(*) AS Int) AS total FROM TestTable";
IQueryBuilder queryBuilder = GetQueryBuilder();
PagingSql pagingSql = queryBuilder.PageQuery <TestEntityWithAutoIncreamentId>(2, 5, orderByQuery: "ORDER BY Name");
Assert.AreEqual(expectedItemsSql, ReplaceNextLineAndTab(pagingSql.Items));
Assert.AreEqual(expectedCountSql, ReplaceNextLineAndTab(pagingSql.Count));
}
