Paging Results
These scenarios demonstrate how to page resutls. Three styles of pagination are shown:
- Page and Page Size
- Skip and Take
- Keyset Pagination (i.e. Skip-Past)
Keyset pagination is a technique where the previous result is used to determine where to starting point for the next set of results. If an index is available, this can be significantly faster than using an offset. For more information see We need tool support for keyset pagination.
Scenario Prototype
public interface IPaginationScenario<TEmployeeSimple>
where TEmployeeSimple : class, IEmployeeSimple, new()
{
/// <summary>
/// Insert a collection of Employee rows.
/// </summary>
void InsertBatch(IList<TEmployeeSimple> employees);
/// <summary>
/// Finds employees with a given name, paging the results.
/// </summary>
/// <param name="lastName">The last name.</param>
/// <param name="pageSize">Size of the page.</param>
/// <param name="page">The page, numbered from zero.</param>
/// <remarks>Sort by FirstName, EmployeeKey</remarks>
IList<TEmployeeSimple> PaginateWithPageSize(string lastName, int page, int pageSize);
/// <summary>
/// Finds employees with a given name, paging the results.
/// This version uses "keyset pagination". See https://use-the-index-luke.com/no-offset for details
/// </summary>
/// <param name="lastName">The last name.</param>
/// <param name="skipPast">The last record in the previous set.</param>
/// <param name="take">The number of rows to take.</param>
/// <remarks>Sort by FirstName, EmployeeKey</remarks>
IList<TEmployeeSimple> PaginateWithSkipPast(string lastName, TEmployeeSimple? skipPast, int take);
/// <summary>
/// Finds employees with a given name, paging the results.
/// </summary>
/// <param name="lastName">The last name.</param>
/// <param name="skip">The number of rows to skip.</param>
/// <param name="take">The number of rows to take.</param>
/// <remarks>Sort by FirstName, EmployeeKey</remarks>
IList<TEmployeeSimple> PaginateWithSkipTake(string lastName, int skip, int take);
}
ADO.NET
public class PaginationScenario : SqlServerScenarioBase, IPaginationScenario<EmployeeSimple>
{
public PaginationScenario(string connectionString) : base(connectionString)
{ }
public void InsertBatch(IList<EmployeeSimple> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
var sql = new StringBuilder(@"INSERT INTO HR.Employee
(FirstName, MiddleName, LastName, Title, OfficePhone, CellPhone, EmployeeClassificationKey)
VALUES ");
for (var i = 0; i < employees.Count; i++)
{
if (i != 0)
sql.AppendLine(",");
sql.Append($"(@FirstName_{i}, @MiddleName_{i}, @LastName_{i}, @Title_{i}, @OfficePhone_{i}, @CellPhone_{i}, @EmployeeClassificationKey_{i})");
}
sql.AppendLine(";");
using (var con = OpenConnection())
using (var cmd = new SqlCommand(sql.ToString(), con))
{
for (var i = 0; i < employees.Count; i++)
{
cmd.Parameters.AddWithValue($"@FirstName_{i}", employees[i].FirstName);
cmd.Parameters.AddWithValue($"@MiddleName_{i}", (object?)employees[i].MiddleName ?? DBNull.Value);
cmd.Parameters.AddWithValue($"@LastName_{i}", employees[i].LastName);
cmd.Parameters.AddWithValue($"@Title_{i}", (object?)employees[i].Title ?? DBNull.Value);
cmd.Parameters.AddWithValue($"@OfficePhone_{i}", (object?)employees[i].OfficePhone ?? DBNull.Value);
cmd.Parameters.AddWithValue($"@CellPhone_{i}", (object?)employees[i].CellPhone ?? DBNull.Value);
cmd.Parameters.AddWithValue($"@EmployeeClassificationKey_{i}", employees[i].EmployeeClassificationKey);
}
cmd.ExecuteNonQuery();
}
}
public IList<EmployeeSimple> PaginateWithPageSize(string lastName, int page, int pageSize)
{
const string sql = @"SELECT e.EmployeeKey,
e.FirstName,
e.MiddleName,
e.LastName,
e.Title,
e.OfficePhone,
e.CellPhone,
e.EmployeeClassificationKey
FROM HR.Employee e
WHERE e.LastName = @LastName
ORDER BY e.FirstName,
e.EmployeeKey OFFSET @Skip ROWS FETCH NEXT @Take ROWS ONLY;";
var skip = page * pageSize;
using (var con = OpenConnection())
using (var cmd = new SqlCommand(sql, con))
{
cmd.Parameters.AddWithValue("@LastName", lastName);
cmd.Parameters.AddWithValue("@Skip", skip);
cmd.Parameters.AddWithValue("@Take", pageSize);
var results = new List<EmployeeSimple>();
using (var reader = cmd.ExecuteReader())
while (reader.Read())
results.Add(new EmployeeSimple(reader));
return results;
}
}
public IList<EmployeeSimple> PaginateWithSkipPast(string lastName, EmployeeSimple? skipPast, int take)
{
const string sqlA = @"SELECT e.EmployeeKey,
e.FirstName,
e.MiddleName,
e.LastName,
e.Title,
e.OfficePhone,
e.CellPhone,
e.EmployeeClassificationKey
FROM HR.Employee e
WHERE (e.LastName = @LastName)
ORDER BY e.FirstName,
e.EmployeeKey
OFFSET 0 ROWS FETCH NEXT @Take ROWS ONLY;";
const string sqlB = @"SELECT e.EmployeeKey,
e.FirstName,
e.MiddleName,
e.LastName,
e.Title,
e.OfficePhone,
e.CellPhone,
e.EmployeeClassificationKey
FROM HR.Employee e
WHERE (e.LastName = @LastName)
AND
(
(e.FirstName > @FirstName)
OR
(
e.FirstName = @FirstName
AND e.EmployeeKey > @EmployeeKey
)
)
ORDER BY e.FirstName,
e.EmployeeKey
OFFSET 0 ROWS FETCH NEXT @Take ROWS ONLY;";
var sql = (skipPast == null) ? sqlA : sqlB;
using (var con = OpenConnection())
using (var cmd = new SqlCommand(sql, con))
{
cmd.Parameters.AddWithValue("@LastName", lastName);
cmd.Parameters.AddWithValue("@Take", take);
if (skipPast != null)
{
cmd.Parameters.AddWithValue("@FirstName", skipPast.FirstName);
cmd.Parameters.AddWithValue("@EmployeeKey", skipPast.EmployeeKey);
}
var results = new List<EmployeeSimple>();
using (var reader = cmd.ExecuteReader())
while (reader.Read())
results.Add(new EmployeeSimple(reader));
return results;
}
}
public IList<EmployeeSimple> PaginateWithSkipTake(string lastName, int skip, int take)
{
const string sql = @"SELECT e.EmployeeKey,
e.FirstName,
e.MiddleName,
e.LastName,
e.Title,
e.OfficePhone,
e.CellPhone,
e.EmployeeClassificationKey
FROM HR.Employee e
WHERE e.LastName = @LastName
ORDER BY e.FirstName,
e.EmployeeKey OFFSET @Skip ROWS FETCH NEXT @Take ROWS ONLY;";
using (var con = OpenConnection())
using (var cmd = new SqlCommand(sql, con))
{
cmd.Parameters.AddWithValue("@LastName", lastName);
cmd.Parameters.AddWithValue("@Skip", skip);
cmd.Parameters.AddWithValue("@Take", take);
var results = new List<EmployeeSimple>();
using (var reader = cmd.ExecuteReader())
while (reader.Read())
results.Add(new EmployeeSimple(reader));
return results;
}
}
}
Chain
public class PaginationScenario : IPaginationScenario<EmployeeSimple>
{
readonly SqlServerDataSource m_DataSource;
public PaginationScenario(SqlServerDataSource dataSource)
{
m_DataSource = dataSource;
}
public void InsertBatch(IList<EmployeeSimple> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
m_DataSource.InsertBatch((IReadOnlyList<EmployeeSimple>)employees).Execute();
}
public IList<EmployeeSimple> PaginateWithPageSize(string lastName, int page, int pageSize)
{
return m_DataSource.From<EmployeeSimple>(new { lastName })
.WithSorting("FirstName", "EmployeeKey")
.WithLimits(page * pageSize, pageSize)
.ToCollection().Execute();
}
public IList<EmployeeSimple> PaginateWithSkipPast(string lastName, EmployeeSimple? skipPast, int take)
{
var link = (skipPast == null) ?
m_DataSource.From<EmployeeSimple>(new { lastName }) :
m_DataSource.From<EmployeeSimple>("LastName = @LastName AND ((FirstName > @FirstName) OR (FirstName = @FirstName AND EmployeeKey > @EmployeeKey))",
new { lastName, skipPast.FirstName, skipPast.EmployeeKey });
return link
.WithSorting("FirstName", "EmployeeKey")
.WithLimits(take)
.ToCollection().Execute();
}
public IList<EmployeeSimple> PaginateWithSkipTake(string lastName, int skip, int take)
{
return m_DataSource.From<EmployeeSimple>(new { lastName })
.WithSorting("FirstName", "EmployeeKey")
.WithLimits(skip, take)
.ToCollection().Execute();
}
}
Dapper
public class PaginationScenario : ScenarioBase, IPaginationScenario<EmployeeSimple>
{
public PaginationScenario(string connectionString) : base(connectionString)
{ }
public void InsertBatch(IList<EmployeeSimple> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
using (var con = OpenConnection())
con.Insert(employees);
}
public IList<EmployeeSimple> PaginateWithPageSize(string lastName, int page, int pageSize)
{
const string sql = @"SELECT e.EmployeeKey,
e.FirstName,
e.MiddleName,
e.LastName,
e.Title,
e.OfficePhone,
e.CellPhone,
e.EmployeeClassificationKey
FROM HR.Employee e
WHERE e.LastName = @LastName
ORDER BY e.FirstName,
e.EmployeeKey OFFSET @Skip ROWS FETCH NEXT @Take ROWS ONLY;";
using (var con = OpenConnection())
return con.Query<EmployeeSimple>(sql, new { lastName, Skip = page * pageSize, Take = pageSize }).ToList();
}
public IList<EmployeeSimple> PaginateWithSkipPast(string lastName, EmployeeSimple? skipPast, int take)
{
const string sqlA = @"SELECT e.EmployeeKey,
e.FirstName,
e.MiddleName,
e.LastName,
e.Title,
e.OfficePhone,
e.CellPhone,
e.EmployeeClassificationKey
FROM HR.Employee e
WHERE (e.LastName = @LastName)
ORDER BY e.FirstName,
e.EmployeeKey
OFFSET 0 ROWS FETCH NEXT @Take ROWS ONLY;";
const string sqlB = @"SELECT e.EmployeeKey,
e.FirstName,
e.MiddleName,
e.LastName,
e.Title,
e.OfficePhone,
e.CellPhone,
e.EmployeeClassificationKey
FROM HR.Employee e
WHERE (e.LastName = @LastName)
AND
(
(e.FirstName > @FirstName)
OR
(
e.FirstName = @FirstName
AND e.EmployeeKey > @EmployeeKey
)
)
ORDER BY e.FirstName,
e.EmployeeKey
OFFSET 0 ROWS FETCH NEXT @Take ROWS ONLY;";
string sql;
object param;
if (skipPast == null)
{
sql = sqlA;
param = new { lastName, take };
}
else
{
sql = sqlB;
param = new { lastName, take, skipPast.FirstName, skipPast.EmployeeKey };
}
using (var con = OpenConnection())
return con.Query<EmployeeSimple>(sql, param).ToList();
}
public IList<EmployeeSimple> PaginateWithSkipTake(string lastName, int skip, int take)
{
const string sql = @"SELECT e.EmployeeKey,
e.FirstName,
e.MiddleName,
e.LastName,
e.Title,
e.OfficePhone,
e.CellPhone,
e.EmployeeClassificationKey
FROM HR.Employee e
WHERE e.LastName = @LastName
ORDER BY e.FirstName,
e.EmployeeKey OFFSET @Skip ROWS FETCH NEXT @Take ROWS ONLY;";
using (var con = OpenConnection())
return con.Query<EmployeeSimple>(sql, new { lastName, skip, take }).ToList();
}
}
DbConnector
public class PaginationScenario : ScenarioBase, IPaginationScenario<EmployeeSimple>
{
public PaginationScenario(string connectionString) : base(connectionString)
{ }
public void InsertBatch(IList<EmployeeSimple> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
//Best approach for unlimited inserts since SQL server has parameter amount restrictions
//https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?redirectedfrom=MSDN&view=sql-server-ver15
DbConnector.Build<int?>(
sql: @$"INSERT INTO {EmployeeSimple.TableName}
(
CellPhone,
EmployeeClassificationKey,
FirstName,
LastName,
MiddleName,
OfficePhone,
Title
)
VALUES (
@{nameof(EmployeeSimple.CellPhone)},
@{nameof(EmployeeSimple.EmployeeClassificationKey)},
@{nameof(EmployeeSimple.FirstName)},
@{nameof(EmployeeSimple.LastName)},
@{nameof(EmployeeSimple.MiddleName)},
@{nameof(EmployeeSimple.OfficePhone)},
@{nameof(EmployeeSimple.Title)}
)",
param: employees[0],
onExecute: (int? result, IDbExecutionModel em) =>
{
//Set the command
DbCommand command = em.Command;
//Execute first row.
em.NumberOfRowsAffected = command.ExecuteNonQuery();
//Set and execute remaining rows.
for (int i = 1; i < employees.Count; i++)
{
var emp = employees[i];
command.Parameters[nameof(EmployeeSimple.CellPhone)].Value = emp.CellPhone ?? (object)DBNull.Value;
command.Parameters[nameof(EmployeeSimple.EmployeeClassificationKey)].Value = emp.EmployeeClassificationKey;
command.Parameters[nameof(EmployeeSimple.FirstName)].Value = emp.FirstName ?? (object)DBNull.Value;
command.Parameters[nameof(EmployeeSimple.LastName)].Value = emp.LastName ?? (object)DBNull.Value;
command.Parameters[nameof(EmployeeSimple.MiddleName)].Value = emp.MiddleName ?? (object)DBNull.Value;
command.Parameters[nameof(EmployeeSimple.OfficePhone)].Value = emp.OfficePhone ?? (object)DBNull.Value;
command.Parameters[nameof(EmployeeSimple.Title)].Value = emp.Title ?? (object)DBNull.Value;
em.NumberOfRowsAffected += command.ExecuteNonQuery();
}
return em.NumberOfRowsAffected;
}
)
.WithIsolationLevel(IsolationLevel.ReadCommitted)//Use a transaction
.Execute();
}
public IList<EmployeeSimple> PaginateWithPageSize(string lastName, int page, int pageSize)
{
const string sql = @"SELECT e.EmployeeKey,
e.FirstName,
e.MiddleName,
e.LastName,
e.Title,
e.OfficePhone,
e.CellPhone,
e.EmployeeClassificationKey
FROM HR.Employee e
WHERE e.LastName = @LastName
ORDER BY e.FirstName,
e.EmployeeKey OFFSET @Skip ROWS FETCH NEXT @Take ROWS ONLY;";
return DbConnector.ReadToList<EmployeeSimple>(sql, new { LastName = lastName, Skip = page * pageSize, Take = pageSize }).Execute();
}
public IList<EmployeeSimple> PaginateWithSkipPast(string lastName, EmployeeSimple? skipPast, int take)
{
const string sqlA = @"SELECT e.EmployeeKey,
e.FirstName,
e.MiddleName,
e.LastName,
e.Title,
e.OfficePhone,
e.CellPhone,
e.EmployeeClassificationKey
FROM HR.Employee e
WHERE (e.LastName = @LastName)
ORDER BY e.FirstName,
e.EmployeeKey
OFFSET 0 ROWS FETCH NEXT @Take ROWS ONLY;";
const string sqlB = @"SELECT e.EmployeeKey,
e.FirstName,
e.MiddleName,
e.LastName,
e.Title,
e.OfficePhone,
e.CellPhone,
e.EmployeeClassificationKey
FROM HR.Employee e
WHERE (e.LastName = @LastName)
AND
(
(e.FirstName > @FirstName)
OR
(
e.FirstName = @FirstName
AND e.EmployeeKey > @EmployeeKey
)
)
ORDER BY e.FirstName,
e.EmployeeKey
OFFSET 0 ROWS FETCH NEXT @Take ROWS ONLY;";
string sql;
object param;
if (skipPast == null)
{
sql = sqlA;
param = new { lastName, take };
}
else
{
sql = sqlB;
param = new { LastName = lastName, Take = take, skipPast.FirstName, skipPast.EmployeeKey };
}
return DbConnector.ReadToList<EmployeeSimple>(sql, param).Execute();
}
public IList<EmployeeSimple> PaginateWithSkipTake(string lastName, int skip, int take)
{
const string sql = @"SELECT e.EmployeeKey,
e.FirstName,
e.MiddleName,
e.LastName,
e.Title,
e.OfficePhone,
e.CellPhone,
e.EmployeeClassificationKey
FROM HR.Employee e
WHERE e.LastName = @LastName
ORDER BY e.FirstName,
e.EmployeeKey OFFSET @Skip ROWS FETCH NEXT @Take ROWS ONLY;";
return DbConnector.ReadToList<EmployeeSimple>(sql, new { LastName = lastName, Skip = skip, Take = take }).Execute();
}
}
Entity Framework 6
public class PaginationScenario : IPaginationScenario<Employee>
{
private Func<OrmCookbookContext> CreateDbContext;
public PaginationScenario(Func<OrmCookbookContext> dBContextFactory)
{
CreateDbContext = dBContextFactory;
}
public void InsertBatch(IList<Employee> employees)
{
using (var context = CreateDbContext())
{
context.Employee.AddRange(employees);
context.SaveChanges();
}
}
public IList<Employee> PaginateWithPageSize(string lastName, int page, int pageSize)
{
using (var context = CreateDbContext())
return context.Employee.Where(e => e.LastName == lastName)
.OrderBy(e => e.FirstName).ThenBy(e => e.EmployeeKey)
.Skip(page * pageSize).Take(pageSize).ToList();
}
[SuppressMessage("Globalization", "CA1307")]
public IList<Employee> PaginateWithSkipPast(string lastName, Employee? skipPast, int take)
{
using (var context = CreateDbContext())
{
if (skipPast == null)
{
return context.Employee.Where(e => e.LastName == lastName)
.OrderBy(e => e.FirstName).ThenBy(e => e.EmployeeKey)
.Take(take).ToList();
}
else
{
return context.Employee
.Where(e => (e.LastName == lastName) && (
(string.Compare(e.FirstName, skipPast.FirstName) > 0)
|| (e.FirstName == skipPast.FirstName && e.EmployeeKey > skipPast.EmployeeKey))
)
.OrderBy(e => e.FirstName).ThenBy(e => e.EmployeeKey)
.Take(take).ToList();
}
}
}
public IList<Employee> PaginateWithSkipTake(string lastName, int skip, int take)
{
using (var context = CreateDbContext())
return context.Employee.Where(e => e.LastName == lastName)
.OrderBy(e => e.FirstName).ThenBy(e => e.EmployeeKey)
.Skip(skip).Take(take).ToList();
}
}
Entity Framework Core
public class PaginationScenario : IPaginationScenario<Employee>
{
private Func<OrmCookbookContext> CreateDbContext;
public PaginationScenario(Func<OrmCookbookContext> dBContextFactory)
{
CreateDbContext = dBContextFactory;
}
public void InsertBatch(IList<Employee> employees)
{
using (var context = CreateDbContext())
{
context.Employees.AddRange(employees);
context.SaveChanges();
}
}
public IList<Employee> PaginateWithPageSize(string lastName, int page, int pageSize)
{
using (var context = CreateDbContext())
return context.Employees.Where(e => e.LastName == lastName)
.OrderBy(e => e.FirstName).ThenBy(e => e.EmployeeKey)
.Skip(page * pageSize).Take(pageSize).ToList();
}
[SuppressMessage("Globalization", "CA1307")]
public IList<Employee> PaginateWithSkipPast(string lastName, Employee? skipPast, int take)
{
using (var context = CreateDbContext())
{
if (skipPast == null)
{
return context.Employees.Where(e => e.LastName == lastName)
.OrderBy(e => e.FirstName).ThenBy(e => e.EmployeeKey)
.Take(take).ToList();
}
else
{
return context.Employees
.Where(e => (e.LastName == lastName) && (
(string.Compare(e.FirstName, skipPast.FirstName) > 0)
|| (e.FirstName == skipPast.FirstName && e.EmployeeKey > skipPast.EmployeeKey))
)
.OrderBy(e => e.FirstName).ThenBy(e => e.EmployeeKey)
.Take(take).ToList();
}
}
}
public IList<Employee> PaginateWithSkipTake(string lastName, int skip, int take)
{
using (var context = CreateDbContext())
return context.Employees.Where(e => e.LastName == lastName)
.OrderBy(e => e.FirstName).ThenBy(e => e.EmployeeKey)
.Skip(skip).Take(take).ToList();
}
}
LINQ to DB
public class PaginationScenario : IPaginationScenario<Employee>
{
public void InsertBatch(IList<Employee> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
using (var db = new OrmCookbook())
{
foreach (var employee in employees)
db.Insert(employee);
}
}
public IList<Employee> PaginateWithPageSize(string lastName, int page, int pageSize)
{
using (var db = new OrmCookbook())
return db.Employee.Where(e => e.LastName == lastName)
.OrderBy(e => e.FirstName).ThenBy(e => e.EmployeeKey)
.Skip(page * pageSize).Take(pageSize).ToList();
}
[SuppressMessage("Globalization", "CA1307")]
public IList<Employee> PaginateWithSkipPast(string lastName, Employee? skipPast, int take)
{
using (var db = new OrmCookbook())
{
if (skipPast == null)
{
return db.Employee.Where(e => e.LastName == lastName)
.OrderBy(e => e.FirstName).ThenBy(e => e.EmployeeKey)
.Take(take).ToList();
}
else
{
return db.Employee
.Where(e => (e.LastName == lastName) && (
(string.Compare(e.FirstName, skipPast.FirstName) > 0)
|| (e.FirstName == skipPast.FirstName && e.EmployeeKey > skipPast.EmployeeKey))
)
.OrderBy(e => e.FirstName).ThenBy(e => e.EmployeeKey)
.Take(take).ToList();
}
}
}
public IList<Employee> PaginateWithSkipTake(string lastName, int skip, int take)
{
using (var db = new OrmCookbook())
return db.Employee.Where(e => e.LastName == lastName)
.OrderBy(e => e.FirstName).ThenBy(e => e.EmployeeKey)
.Skip(skip).Take(take).ToList();
}
}
LLBLGen Pro
public class PaginationScenario : IPaginationScenario<EmployeeEntity>
{
public void InsertBatch(IList<EmployeeEntity> employees)
{
using(var toInsert = new EntityCollection<EmployeeEntity>(employees))
{
using(var adapter = new DataAccessAdapter())
{
adapter.BatchSize = employees?.Count ?? 0;
adapter.SaveEntityCollection(toInsert);
}
}
}
public IList<EmployeeEntity> PaginateWithPageSize(string lastName, int page, int pageSize)
{
using(var adapter = new DataAccessAdapter())
{
// we pass in page+1, as the tests start paging at page 0, but in LLBLGen Pro, a page number 0
// means paging isn't used.
return new LinqMetaData(adapter).Employee.Where(e => e.LastName == lastName)
.OrderBy(e => e.FirstName).ThenBy(e => e.EmployeeKey)
.TakePage(page + 1, pageSize).ToList();
}
}
[SuppressMessage("Globalization", "CA1307")]
public IList<EmployeeEntity> PaginateWithSkipPast(string lastName, EmployeeEntity? skipPast, int take)
{
using(var adapter = new DataAccessAdapter())
{
if(skipPast == null)
{
return new LinqMetaData(adapter).Employee.Where(e => e.LastName == lastName)
.OrderBy(e => e.FirstName).ThenBy(e => e.EmployeeKey)
.Take(take).ToList();
}
return new LinqMetaData(adapter).Employee
.Where(e => (e.LastName == lastName) && (
(string.Compare(e.FirstName, skipPast.FirstName) > 0)
|| (e.FirstName == skipPast.FirstName && e.EmployeeKey > skipPast.EmployeeKey))
)
.OrderBy(e => e.FirstName).ThenBy(e => e.EmployeeKey)
.Take(take).ToList();
}
}
public IList<EmployeeEntity> PaginateWithSkipTake(string lastName, int skip, int take)
{
using(var adapter = new DataAccessAdapter())
{
return new LinqMetaData(adapter).Employee.Where(e => e.LastName == lastName)
.OrderBy(e => e.FirstName).ThenBy(e => e.EmployeeKey)
.Skip(skip).Take(take).ToList();
}
}
}
NHibernate
public class PaginationScenario : IPaginationScenario<Employee>
{
readonly ISessionFactory m_SessionFactory;
public PaginationScenario(ISessionFactory sessionFactory)
{
m_SessionFactory = sessionFactory;
}
public void InsertBatch(IList<Employee> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
using (var session = m_SessionFactory.OpenSession())
{
foreach (var employee in employees)
session.Save(employee);
session.Flush();
}
}
public IList<Employee> PaginateWithPageSize(string lastName, int page, int pageSize)
{
using (var session = m_SessionFactory.OpenSession())
return session.Query<Employee>().Where(e => e.LastName == lastName)
.OrderBy(e => e.FirstName).ThenBy(e => e.EmployeeKey)
.Skip(page * pageSize).Take(pageSize).ToList();
}
[SuppressMessage("Globalization", "CA1307")]
public IList<Employee> PaginateWithSkipPast(string lastName, Employee? skipPast, int take)
{
using (var session = m_SessionFactory.OpenSession())
{
if (skipPast == null)
{
return session.Query<Employee>().Where(e => e.LastName == lastName)
.OrderBy(e => e.FirstName).ThenBy(e => e.EmployeeKey)
.Take(take).ToList();
}
else
{
return session.Query<Employee>()
.Where(e => (e.LastName == lastName) && (
(string.Compare(e.FirstName, skipPast.FirstName) > 0)
|| (e.FirstName == skipPast.FirstName && e.EmployeeKey > skipPast.EmployeeKey))
)
.OrderBy(e => e.FirstName).ThenBy(e => e.EmployeeKey)
.Take(take).ToList();
}
}
}
public IList<Employee> PaginateWithSkipTake(string lastName, int skip, int take)
{
using (var session = m_SessionFactory.OpenSession())
return session.Query<Employee>().Where(e => e.LastName == lastName)
.OrderBy(e => e.FirstName).ThenBy(e => e.EmployeeKey)
.Skip(skip).Take(take).ToList();
}
}
RepoDb
public class PaginationScenario : BaseRepository<EmployeeSimple, SqlConnection>,
IPaginationScenario<EmployeeSimple>
{
public PaginationScenario(string connectionString)
: base(connectionString, RDB.Enumerations.ConnectionPersistency.Instance)
{ }
public void InsertBatch(IList<EmployeeSimple> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
InsertAll(employees);
}
public IList<EmployeeSimple> PaginateWithPageSize(string lastName, int page, int pageSize)
{
var orderBy = OrderField.Parse(new
{
FirstName = Order.Ascending,
EmployeeKey = Order.Ascending
});
return BatchQuery(page,
pageSize,
orderBy,
e => e.LastName == lastName).AsList();
}
public IList<EmployeeSimple> PaginateWithSkipPast(string lastName, EmployeeSimple? skipPast, int take)
{
var orderBy = OrderField.Parse(new
{
FirstName = Order.Ascending,
EmployeeKey = Order.Ascending
});
var page = 0;
if (skipPast != null)
{
var lastNameField = new QueryField("LastName", lastName);
var firstNameField = new QueryField("FirstName", Operation.GreaterThan, skipPast.FirstName);
var firstNameAndEmployeeKeyFields = new QueryGroup(new[]
{
new QueryField("FirstName", skipPast.FirstName),
new QueryField("EmployeeKey", Operation.GreaterThan, skipPast.EmployeeKey)
});
var group = new QueryGroup(lastNameField,
new QueryGroup(firstNameField.AsEnumerable(),
firstNameAndEmployeeKeyFields.AsEnumerable(), Conjunction.Or));
return BatchQuery(page,
take,
orderBy,
group).AsList();
}
else
{
return BatchQuery(page,
take,
orderBy,
e => e.LastName == lastName).AsList();
}
}
public IList<EmployeeSimple> PaginateWithSkipTake(string lastName, int skip, int take)
{
var orderBy = OrderField.Parse(new
{
FirstName = Order.Ascending,
EmployeeKey = Order.Ascending
});
var page = skip / take;
return BatchQuery(page,
take,
orderBy,
e => e.LastName == lastName).AsList();
}
}
ServiceStack
public class PaginationScenario : IPaginationScenario<Employee>
{
private IDbConnectionFactory _dbConnectionFactory;
public PaginationScenario(IDbConnectionFactory dbConnectionFactory)
{
this._dbConnectionFactory = dbConnectionFactory;
}
public void InsertBatch(IList<Employee> employees)
{
using (var db = _dbConnectionFactory.OpenDbConnection())
{
db.InsertAll(employees);
}
}
public IList<Employee> PaginateWithPageSize(string lastName, int page, int pageSize)
{
using (var db = _dbConnectionFactory.OpenDbConnection())
{
var q = db.From<Employee>()
.Where(e => e.LastName == lastName)
.OrderBy(e => e.FirstName).ThenBy(e => e.Id)
.Skip(page * pageSize).Take(pageSize);
return db.Select<Employee>(q);
}
}
public IList<Employee> PaginateWithSkipPast(string lastName, Employee? skipPast, int take)
{
using (var db = _dbConnectionFactory.OpenDbConnection())
{
if (skipPast == null)
{
return db.Select<Employee>(db.From<Employee>()
.Where(e => e.LastName == lastName)
.OrderBy(e => e.FirstName).ThenBy(e => e.Id)
.Take(take));
}
return db.Select<Employee>(db.From<Employee>(new TableOptions { Alias = "e" })
.Where(@"
(e.LastName = @LastName)
AND
(
(e.FirstName > @FirstName)
OR
(
e.FirstName = @FirstName
AND e.EmployeeKey > @Id
)
)"
)
.OrderBy(e => e.FirstName).ThenBy(e => e.Id)
.Take(take), new
{
skipPast.LastName,
skipPast.FirstName,
skipPast.Id
});
}
}
public IList<Employee> PaginateWithSkipTake(string lastName, int skip, int take)
{
using (var db = _dbConnectionFactory.OpenDbConnection())
{
var q = db.From<Employee>()
.Where(e => e.LastName == lastName)
.OrderBy(e => e.FirstName).ThenBy(e => e.Id)
.Skip(skip).Take(take);
return db.Select<Employee>(q);
}
}
}