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?

Alcides

You can conditionally append the where clause. rite ? – Boozy

I'm assuming so, yes. However, I am looking for a decent example on how to do so. – Alcides

Answer 1

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.

Zaffer

Applies too to List<int> or List<string> parameters ? – Orthogonal

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**/ ) ? – Orthogonal

@tCode, just change the 'select *' to 'update' and .Query() to .Execute() – Zaffer

Answer 2

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();

Pow

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. – Fredette

I would have chosen this as the accepted answer if I hadn't chosen to use Dapper.SqlBuilder. – Alcides

And for List<int> or List<string> parameters ? – Orthogonal

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. – Idoux

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 – Pow

The downside of this is that you have to recompile the query every time which is very inefficient. – Depressed

Answer 3

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

Skedaddle

Simpler way is not working for me. Compiler says cannot implicitly convert from string to QueryBuilder. What am I missing here... – Cognation

@Cognation 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). – Skedaddle

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! – Cognation

Isn't this vulnerable to SQL injection attacks? – Ubiquitarian

@Ubiquitarian nope, it's safe. The library is exactly for that - it's not regular string interpolation. Check the docs. github.com/Drizin/DapperQueryBuilder – Skedaddle

Answer 4

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();
}

Cleanse

Answer 5

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

Rodgers

Answer 6

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();
}

Deplore