The predicate system in Dapper Extensions provides a simple way to build dynamic SQL WHERE clauses. In the examples below, we use the following model:

public class Employee
{
    public int EmployeeId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public bool IsActive { get; set; }
    public DateTime CreatedAt { get; set; }
}

Simple FieldPredicate

Create a FieldPredicate with a generic type and pass it to GetList. This predicate uses strong typing.

Example: Get all active employees.

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    var predicate = Predicates.Field<Employee>(e => e.IsActive, Operator.Eq, true);
    IEnumerable<Employee> results = connection.GetList<Employee>(predicate);
}

Generated SQL:

SELECT 
    [Employee].[EmployeeId],
    [Employee].[FirstName],
    [Employee].[LastName],
    [Employee].[IsActive],
    [Employee].[CreatedAt]
FROM [Employee]
WHERE ([Employee].[IsActive] = @IsActive_0)

IN Clause

Pass a IEnumerable as the value to generate a WHERE x IN (...) clause. (TODO: Add example.)

Compound Predicates (Predicate Groups)

Use PredicateGroup to combine multiple predicates with AND or OR. Each group implements IPredicate, so groups can be nested.

Example: Find active employees with last name starting with "Br".

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    var group = new PredicateGroup
    {
        Operator = GroupOperator.And,
        Predicates = new List<IPredicate>
        {
            Predicates.Field<Employee>(e => e.IsActive, Operator.Eq, true),
            Predicates.Field<Employee>(e => e.LastName, Operator.Like, "Br%")
        }
    };
    IEnumerable<Employee> results = connection.GetList<Employee>(group);
}

Generated SQL:

SELECT 
    [Employee].[EmployeeId],
    [Employee].[FirstName],
    [Employee].[LastName],
    [Employee].[IsActive],
    [Employee].[CreatedAt]
FROM [Employee]
WHERE (([Employee].[IsActive] = @IsActive_0) 
       AND ([Employee].[LastName] LIKE @LastName_1))

Multiple Compound Predicates

Chain multiple groups to create complex conditions.

Example: (Active and last name like "Br%") OR (inactive and first name NOT like "Pa%").

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    var mainGroup = new PredicateGroup { Operator = GroupOperator.Or, Predicates = new List<IPredicate>() };

    var groupA = new PredicateGroup
    {
        Operator = GroupOperator.And,
        Predicates = new List<IPredicate>
        {
            Predicates.Field<Employee>(e => e.IsActive, Operator.Eq, true),
            Predicates.Field<Employee>(e => e.LastName, Operator.Like, "Br%")
        }
    };
    mainGroup.Predicates.Add(groupA);

    var groupB = new PredicateGroup
    {
        Operator = GroupOperator.And,
        Predicates = new List<IPredicate>
        {
            Predicates.Field<Employee>(e => e.IsActive, Operator.Eq, false),
            Predicates.Field<Employee>(e => e.FirstName, Operator.Like, "Pa%", true)  // NOT operator
        }
    };
    mainGroup.Predicates.Add(groupB);

    IEnumerable<Employee> results = connection.GetList<Employee>(mainGroup);
}

Generated SQL:

SELECT 
    [Employee].[EmployeeId],
    [Employee].[FirstName],
    [Employee].[LastName],
    [Employee].[IsActive],
    [Employee].[CreatedAt]
FROM [Employee]
WHERE 
    ((([Employee].[IsActive] = @IsActive_0) AND ([Employee].[LastName] LIKE @LastName_1)) 
     OR (([Employee].[IsActive] = @IsActive_2) AND ([Employee].[FirstName] NOT LIKE @FirstName_3)))

PropertyPredicate

(TODO: Add explanation and example.)

Exists Predicate

Use Predicates.Exists with a sub-predicate to create an EXISTS clause.

var subPred = Predicates.Field<User>(u => u.Email, Operator.Eq, "someone@somewhere.com");
var existsPred = Predicates.Exists<User>(subPred);
var existingUser = connection.GetList<User>(existsPred).FirstOrDefault();