CRUD Counting

You can count how many entities match the search conditions by calling the Count method. When conditions is null, then it returns how many entities are in the table. Otherwise, conditions should begin with a WHERE clause.

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

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

For convenience, there is an overload which generates the WHERE clause for you, when you are looking for an equality match. All properties defined on the conditions object 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
int Count<TEntity>(object conditions, int? commandTimeout = null);

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

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 total number of users

var numUsers = database.Count<User>();
// or
numUsers = await database.CountAsync<User>();

MS-SQL 2012 +

SELECT COUNT(*)
FROM [Users]

PostgreSQL

SELECT COUNT(*)
FROM user

Get the number of users over 18 years old

var numUsers = database.Count<User>("WHERE Age > @MinAge", new { MinAge = 18 });
// or
numUsers = await database.CountAsync<User>("WHERE Age > @MinAge", new { MinAge = 18 });

MS-SQL 2012 +

SELECT COUNT(*)
FROM [Users]
WHERE Age > @MinAge

PostgreSQL

SELECT COUNT(*)
FROM user
WHERE age > @MinAge

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

var numUsers = database.Count<User>(new { Name = (string)null, Age = 18 });
// or
var numUsers = await database.CountAsync<User>(new { Name = (string)null, Age = 18 });

MS-SQL 2012 +

SELECT COUNT(*)
FROM [Users]
WHERE [Name] IS NULL AND [Age] = @Age

PostgreSQL

SELECT COUNT(*)
FROM user
WHERE name IS NULL AND age = @Age