CRUD Reading Many Entities

// Synchronous
IEnumerable<TEntity> GetRange<TEntity>(string conditions, object parameters = null, int? commandTimeout = null);

// Asynchronous
Task<IEnumerable<TEntity>> GetRangeAsync<TEntity>(string conditions, object parameters = null, int? commandTimeout = null, CancellationToken cancellationToken = default);

Gets all the entities in the table which match the conditions. The conditions should start with a WHERE clause.

// Synchronous
IEnumerable<TEntity> GetRange<TEntity>(object conditions, int? commandTimeout = null);

// Asynchronous
Task<IEnumerable<TEntity>> GetRangeAsync<TEntity>(object conditions, int? commandTimeout = null, CancellationToken cancellationToken = default);

Gets all the entities in the table which match all the conditions. The conditions should be an object whose properties match those of TEntity. All properties defined on the conditions will be combined with an AND clause. If the value of a property is null then the SQL generated will check for IS NULL.

// Synchronous
IEnumerable<TEntity> GetAll<TEntity>(int? commandTimeout = null);

// Asynchronous
Task<IEnumerable<TEntity>> GetAllAsync<TEntity>(int? commandTimeout = null, CancellationToken cancellationToken = default);

Gets all the entities in the table.

📝 Async version is available

Examples

Given the POCO class:

[Table("Users")]
public class UserEntity
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
}

Get all the users over the age of 18

var users = database.GetRange<UserEntity>("WHERE Age > @MinAge", new { MinAge = 18 });

MS-SQL 2012 +

SELECT [Id], [Name], [Age]
FROM [Users]
WHERE Age > @MinAge

PostgreSQL

SELECT id, name, age
FROM user
WHERE age > @MinAge

Get all the users without a known name who are 18 years old

var users = database.GetRange<UserEntity>(new { Name = (string)null, Age = 18 });

MS-SQL 2012 +

SELECT [Id], [Name], [Age]
FROM [Users]
WHERE [Name] IS NULL AND [Age] = @Age

PostgreSQL

SELECT id, name, age
FROM user
WHERE name IS NULL AND age = @Age

Get all users:

var users = database.GetAll<UserEntity>();

MS-SQL 2012 +

SELECT [Id], [Name], [Age]
FROM [Users]

PostgreSQL

SELECT id, name, age
FROM user

Getting a specific page of entities

// Synchronous
PagedList<TEntity> GetPage<TEntity>(IPageBuilder pageBuilder, string conditions, string orderBy, object parameters = null, int? commandTimeout = null);
PagedList<TEntity> GetPage<TEntity>(IPageBuilder pageBuilder, object conditions, string orderBy, int? commandTimeout = null);

// Asynchronous
Task<PagedList<TEntity>> GetPageAsync<TEntity>(IPageBuilder pageBuilder, string conditions, string orderBy, object parameters = null, int? commandTimeout = null, CancellationToken cancellationToken = default);
Task<PagedList<TEntity>> GetPageAsync<TEntity>(IPageBuilder pageBuilder, object conditions, string orderBy, int? commandTimeout = null, CancellationToken cancellationToken = default);

Gets a page of entities which match the conditions.

Examples

Given the POCO class:

[Table("Users")]
public class UserEntity
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
}

Get the third page of all the users over the age of 18

var pager = new PageIndexPageBuilder(3, 10);
var users = database.GetPage<User>(pager, "WHERE Age > @Age", "Age ASC", new { Age = 10 });

MS-SQL 2012 +

SELECT [Id], [Name], [Age]
FROM [Users]
WHERE Age > @Age
ORDER BY Age ASC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY

PostgreSQL

SELECT id, name, age
FROM user
WHERE Age > @Age
ORDER BY Age ASC
LIMIT 10 OFFSET 20