How do I build a dynamic Where clause with Dapper when passing in a model

Question

I have an example model that looks like this:

public class PersonModel
{
     public int Id {get; set;}
     public string FirstName {get; set;}
     public string Lastname {get; set;}
     public string City {get; set;}
}

In my repository I want to create a search method where I pass in my model - but not all fields will always be populated. I want to create a WHERE and AND based on if a field in the model is populated or not. If the field is not populated then I do not want to create a WHERE clause for it.

For example - if I pass in FirstName = "Bob" and City = "Boston" then I want my search to look like this:

SELECT * FROM PersonTable WHERE FirstName = @firstName AND City = @city

Since I did not pass in Id or LastName I don't want them added to the query. If I just pass in City = "Boston" then I want it to look like this:

SELECT * FROM PersonTable WHERE City = @city

My repo method would look something like this

using Dapper;
public List<PersonModel> Search(PersonModel model)
{
//db = DbConnection connection
    var selectSql = "SELECT * FROM PersonTable "; //build out where clause somehow
    return db.Query<PersonModel>(selectSql).ToList();
}

My question is how would I build this out in my repo method properly?

I'm assuming so, yes. However, I am looking for a decent example on how to do so. — Eldorian, Sep 29 '16 at 22:27

score 84 · Accepted Answer · edited Aug 20 '19 at 18:25

You can also use Dapper's SqlBuilder.

Note that you'll have to install the Dapper.SqlBuilder NuGet package since it doesn't come with Dapper's main distribution.

Here is an example:

    [Test]
    public void Test()
    {
        var model = new PersonModel {FirstName = "Bar", City = "New York"};

        var builder = new SqlBuilder();

        //note the 'where' in-line comment is required, it is a replacement token
        var selector = builder.AddTemplate("select * from table /**where**/");

        if (model.Id > 0)
            builder.Where("Id = @Id", new { model.Id });

        if (!string.IsNullOrEmpty(model.FirstName))
            builder.Where("FirstName = @FirstName", new { model.FirstName });

        if (!string.IsNullOrEmpty(model.Lastname))
            builder.Where("Lastname = @Lastname", new { model.Lastname });

        if (!string.IsNullOrEmpty(model.City))
            builder.Where("City = @City", new { model.City });

        Assert.That(selector.RawSql, Is.EqualTo("select * from table WHERE FirstName = @FirstName AND City = @City\n"));

        //var rows = sqlConnection.Query(selector.RawSql, selector.Parameters);
    }

You can find some examples here.

***SqlBuilder*** for this sentence: _SELECT * FROM TABLE1 WHERE id IN_ `(SELECT t2.id FROM TABLE2 as t2 , TABLE1 as t1 WHERE t1.id=t2.id AND /**where**/ )` ? — Kiquenet, Mar 15 '18 at 15:57
@tCode, just change the 'select *' to 'update' and .Query() to .Execute() — Void Ray, Apr 12 '19 at 15:39

score 21 · Answer 2 · edited Dec 24 '20 at 08:49

21

This should do the trick for you, clean and simple:

var selectSql = "SELECT * FROM PersonTable WHERE (@FirstName IS NULL OR FirstName =  @FirstName) AND (@LastName IS NULL OR LastName =  @LastName) AND (@City IS NULL OR City =  @City) AND (@Id IS NULL OR Id =  @Id) OPTION(RECOMPILE)";

return conn.Query<PersonModel>(selectSql, new
{
     model.FirstName,
     model.Lastname,
     model.City,
     Id = model.Id == 0? (int?)null: (int?)model.Id        
}).ToList();

edited Dec 24 '20 at 08:49

Hans Keﬆing

38,117
9
79
111

answered Sep 30 '16 at 07:15

JFM

753
12
16

5

This is cleaner than building your SQL with C# conditionals. Consider using OPTION(RECOMPILE), so that query optimisation can take account of the actual parameters supplied. – bbsimonbb Oct 03 '16 at 10:13
1

I would have chosen this as the accepted answer if I hadn't chosen to use Dapper.SqlBuilder. – Eldorian Oct 03 '16 at 15:58
1

And for `List` or `List` ***parameters*** ? – Kiquenet Mar 15 '18 at 15:52
Just to reiterate bbsimonbbs point, if you do not add `OPTION (RECOMPILE)` your query can be VERY slow in some instances. SQL will optimize for one execution path and only that path will run reasonably. – Marie Sep 10 '19 at 17:17
In regards of the OPTION(RECOMPILE) statement, thank you very much bbsimonbb and Marie for stating this, I've now added it to the post – JFM Sep 11 '19 at 19:01
The downside of this is that you have to recompile the query every time which is very inefficient. – Jon49 Dec 19 '19 at 17:21

drizin · Answer 3 · 2023-02-01T22:02:01.193

DapperQueryBuilder is an alternative to Dapper.SqlBuilder but much easier to use:

var query = cn.QueryBuilder($"SELECT * FROM PersonTable WHERE 1=1");

if (model.Id > 0) 
    query += $"AND Id = {model.Id}";

if (!string.IsNullOrEmpty(model.FirstName))
    query += $"AND FirstName = {model.FirstName}";

if (!string.IsNullOrEmpty(model.Lastname))
    query += $"AND Lastname = {model.Lastname}";

if (!string.IsNullOrEmpty(model.City))
    query += $"AND City = {model.City}";


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

Query<Person>() will invoke Dapper passing the underlying SQL and parameters - and the underlying query is fully parametrized SQL (WHERE FirstName = @p0 AND LastName = @p1, etc). Parameters are automatically captured from the string interpolation (but it's safe against SQL injection).

--

ALTERNATIVE SYNTAX
Similar to Dapper.SqlBuilder you can also use the /**where**/ syntax (which will automatically join the conditions, no need to manually use AND or 1==1 trick):

var query = cn.QueryBuilder($@"
    SELECT * 
    FROM PersonTable
   /**where**/
");

if (model.Id > 0)
    query.Where($"Id = {model.Id}");

if (!string.IsNullOrEmpty(model.FirstName))
    query.Where($"FirstName = {model.FirstName}");

if (!string.IsNullOrEmpty(model.Lastname))
    query.Where($"Lastname = {model.Lastname}");

if (!string.IsNullOrEmpty(model.City))
    query.Where($"City = {model.City}");


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

Disclaimer: I'm the author of the library

Simpler way is not working for me. Compiler says cannot implicitly convert from string to QueryBuilder. What am I missing here... — ClownCoder, Mar 31 '22 at 02:14
@ClownCoder either you're not using the latest nuget package, or (most probable) you're trying to append strings instead of FormattableString (string interpolation, note the $ sign). — drizin, Apr 01 '22 at 00:06
indeed. I was using an old package (1.1.0) by the time of writing this comment, last version is 1.2.4. Thanks a lot! — ClownCoder, Apr 01 '22 at 01:55
@taiji123 nope, it's safe. The library is exactly for that - it's not regular string interpolation. Check the docs. https://github.com/Drizin/DapperQueryBuilder/ — drizin, May 02 '23 at 22:36

score 0 · Answer 4 · edited Feb 14 '17 at 12:29

bool isFirstWhereSet = false;
bool isCityWhereSet = false;
string sqlQuery = "SELECT * FROM PersonTable "  ;
if (! String.IsNullOrEmpty(model.FirstName ))
{
sqlQuery  =sqlQuery  + "WHERE FirstName =@FirstName" ;
isFirstWhereSet = true;
}

if (! String.IsNullOrEmpty(model.City))
{
isCityWhereSet  = true ;
if (! isFirstWhereSet )
sqlQuery  = sqlQuery  + " WHERE City = @city";
else
sqlQuery  = sqlQuery  + " AND City = @city";
}



if (isFirstWhereSet == true && isCityWhereSet == true )
 return db.Query<PersonModel>(sqlQuery , new { FirstName = model.FirstName  , City = mode.City}).ToList();
else if (isFirstWhereSet == true && isCityWhereSet  == false)
 return db.Query<PersonModel>(sqlQuery , new { FirstName = model.FirstName }).ToList();
else if (isFirstWhereSet == false && isCityWhereSet  == true)
 return db.Query<PersonModel>(sqlQuery , new { City= model.City}).ToList();
else
{
 return db.Query<PersonModel>(sqlQuery).ToList();
}

score 0 · Answer 5 · answered Feb 28 '19 at 02:22

0

You can use the ExpressionExtensionSQL library. This library converts lambda expressions to where clauses, and can be used with dapper and ADO.

answered Feb 28 '19 at 02:22

Felipe Spinelli

11
1

score 0 · Answer 6 · answered Aug 28 '23 at 19:40

This syntax code worked for me and not facing any issue.

var builder = new SqlBuilder();
if (Condition)
{
    builder.Where($"LOWER(ColumnName) LIKE @SubjectId", new { Value = Class.Obj  });
}
if (Condition)
{
    builder.Where($"LOWER(Domain) = @Domain", new { Domain = Obj[1] });
}

var selector = builder.AddTemplate("SELECT * FROM TableName /**where**/");

using (var connection = new SqlConnection(connectionString))
{
    var results = await connection.QueryAsync(selector.RawSql, selector.Parameters);
                return results.ToList();
}

How do I build a dynamic Where clause with Dapper when passing in a model

6 Answers6

Linked