How to filter in Dapper using a collection with multiple properties?

Question

I am a bit new to Dapper and am trying to find a clean way to pass a filter parameter to SQL Query for a collection with more than one property.

My collection looks like this:

[{
    Prop1: 'A Value 1',
    Prop2: 'B Value 1'
},
{
    Prop1: 'A Value 2',
    Prop2: 'B Value 2'
}]

Which should result in a SQL Query looking something like this:

select *
from SampleTable
where
([ColumnA]='A Value 1' and [ColumnB]='B Value 1')
or ([ColumnA]='A Value 2' and [ColumnB]='B Value 2')

Note: Something like whats shown below will not work because the two properties PropA and PropB need to filter together.

string query = @"select *
                from SampleTable
                where [ColumnA] in (@PropA_Value)
                and [ColumnB] in (@PropB_Value)"

con.Query<T>(query, new{PropA_Value = PropA,PropB_Value = PropB}).AsList();

Answer 1

Modeling your filters as a class:

class MyFilterDefinition
{
    public string Prop1 { get; set; }
    public string Prop2 { get; set; }
}

Build that dynamic condition using DapperQueryBuilder would be as simple as this:

var myOptions = new List<MyFilterDefinition>()
{
    new MyFilterDefinition() { Prop1 = "A Value 1", Prop2 = "B Value 1" },
    new MyFilterDefinition() { Prop1 = "A Value 2", Prop2 = "B Value 2" }
}

var query = cn.QueryBuilder($@"
    select *
    from SampleTable
    /**where**/");

// by default multiple filters are combined with AND, so change to OR
query.FiltersType = Filters.FiltersType.OR; 

foreach (var option in myOptions)
    query.Where($"[ColumnA] = {option.Prop1} AND [ColumnB] = {option.Prop2}");

var results = query.Query<YourPOCO>();

The final result would be this:

    select *
    from SampleTable
    where ([ColumnA]=@p0 AND [ColumnB]=@p1) OR ([ColumnA]=@p2 AND [ColumnB]=@p3)

With the respective parameters @p0 = 'A Value 1', etc..

Disclaimer: I'm the author of DapperQueryBuilder