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?
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.