Predicates

The predicate system in Dapper Extensions is very simple to use. In the examples below we will use the following model:

public class Person
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string PreferredName { get; set; }
    public string LastName { get; set; }
    public bool Active { get; set; }
    public DateTime DateCreated { get; set; }
}

Simple FieldPredicate Operation

To create a simple predicate, just create a FieldPredicate and pass it to the query operation. FieldPredicate expects a generic type which allows for strong typing.

In the example below, we are returning all Persons where the Active value is equal to true.

Code

using (SqlConnection cn = new SqlConnection(_connectionString))
{
    cn.Open();
    var predicate = Predicates.Field<Person>(f => f.Active, Operator.Eq, true);
    IEnumerable<Person> list = cn.GetList<Person>(predicate);
    cn.Close();
}

Generated SQL

SELECT 
   [Person].[Id]
 , [Person].[FirstName]
 , [Person].[PreferredName]
 , [Person].[LastName]
 , [Person].[Active]
 , [Person].[DateCreated] 
FROM [Person]
WHERE ([Person].[Active] = @Active_0)

IN Clause TODO: Demonstrate that you can pass an IEnumerable as the value to acheive WHERE x IN ('a','b') functionality

Compound Predicate (Predicate Group)

Compound predicates are achieved through the use of predicate groups. For each predicate group, you must choose an operator (AND/OR). Each predicate that is added to the group will be joined with the specified operator.

Multiple predicate groups can be joined together since each predicate group implements IPredicate.

In the example below, we create a predicate group with an AND operator:

Code

using (SqlConnection cn = new SqlConnection(_connectionString))
{
    cn.Open();
    var pg = new PredicateGroup { Operator = GroupOperator.And, Predicates = new List<IPredicate>() };
    pg.Predicates.Add(Predicates.Field<Person>(f => f.Active, Operator.Eq, true));
    pg.Predicates.Add(Predicates.Field<Person>(f => f.LastName, Operator.Like, "Br%"));
    IEnumerable<Person> list = cn.GetList<Person>(pg);
    cn.Close();
}

Generated SQL

SELECT 
   [Person].[Id]
 , [Person].[FirstName]
 , [Person].[PreferredName]
 , [Person].[LastName]
 , [Person].[Active]
 , [Person].[DateCreated] 
FROM [Person] 
WHERE (([Person].[Active] = @Active_0) 
      AND ([Person].[LastName] LIKE @LastName_1))

Multiple Compound Predicates (Predicate Group)

Since each predicate groups implement IPredicate, you can chain them together to create complex compound predicates.

In the example below, we create two predicate groups and then join them together with a third predicate group:

Code

using (SqlConnection cn = new SqlConnection(_connectionString))
{
    cn.Open();
    var pgMain = new PredicateGroup { Operator = GroupOperator.Or, Predicates = new List<IPredicate>() };

    var pga = new PredicateGroup { Operator = GroupOperator.And, Predicates = new List<IPredicate>() };
    pga.Predicates.Add(Predicates.Field<Person>(f => f.Active, Operator.Eq, true));
    pga.Predicates.Add(Predicates.Field<Person>(f => f.LastName, Operator.Like, "Br%"));
    pgMain.Predicates.Add(pga);

    var pgb = new PredicateGroup { Operator = GroupOperator.And, Predicates = new List<IPredicate>() };
    pgb.Predicates.Add(Predicates.Field<Person>(f => f.Active, Operator.Eq, false));
    pgb.Predicates.Add(Predicates.Field<Person>(f => f.FirstName, Operator.Like, "Pa%", true /* NOT */ ));
    pgMain.Predicates.Add(pgb);

    IEnumerable<Person> list = cn.GetList<Person>(pgMain);
    cn.Close();
}

Generated SQL

SELECT 
   [Person].[Id]
 , [Person].[FirstName]
 , [Person].[PreferredName]
 , [Person].[LastName]
 , [Person].[Active]
 , [Person].[DateCreated] 
FROM [Person] 
WHERE 
   ((([Person].[Active] = @Active_0) AND ([Person].[LastName] LIKE @LastName_1)) 
   OR (([Person].[Active] = @Active_2) AND ([Person].[FirstName] NOT LIKE @FirstName_3)))

Property Predicate

Property Predicates allow you to compare two table values.

In the example below, we are returning all Persons where the FirstName value is equal to the PreferredName value.

using (SqlConnection cn = new SqlConnection(_connectionString))
{
    cn.Open();
    var predicate = Predicates.Property<Person, Person>(f => f.FirstName, Operator.Eq, p => p.PreferredName);
    IEnumerable<Person> list = cn.GetList<Person>(predicate);
    cn.Close();
}

Generated SQL

SELECT 
   [Person].[Id]
 , [Person].[FirstName]
 , [Person].[PreferredName]
 , [Person].[LastName]
 , [Person].[Active]
 , [Person].[DateCreated] 
FROM [Person] 
WHERE ([Person].[FirstName] = [Person].[PreferredName]) 

Exists Predicate

TODO: Update this example to use the same model as the other examples.

var subPred = Predicates.Field<User>(u => u.Email, Operator.Eq, "[email protected]");
var existsPred = Predicates.Exists<User>(subPred);
var existingUser = cn.GetList<User>(existsPred , null, tran).FirstOrDefault();