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?

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.

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