¿Cómo construyo una cláusula Where dinámica con Dapper al pasar en un modelo?

.net c#-4.0 dapper sql

Pregunta

Tengo un modelo de ejemplo que se ve así:

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

En mi repositorio quiero crear un método de búsqueda donde paso en mi modelo, pero no todos los campos se llenarán siempre. Quiero crear un WHERE y AND en función de si un campo en el modelo está ocupado o no. Si el campo no está lleno, no quiero crear una cláusula WHERE para él.

Por ejemplo, si paso FirstName = "Bob" y City = "Boston", entonces quiero que mi búsqueda se vea así:

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

Como no pasé Id o LastName, no quiero que se agreguen a la consulta. Si solo paso en City = "Boston", entonces quiero que se vea así:

SELECT * FROM PersonTable WHERE City = @city

Mi método repo se parecería a esto

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

Mi pregunta es ¿cómo construiré esto en mi método repo correctamente?

Respuesta aceptada

También puedes usar SqlBuilder de Dapper .

Aquí hay un ejemplo:

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

Puedes encontrar algunos ejemplos aquí!


Respuesta popular

Esto debería hacer el truco para ti, limpio y 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)";

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


Licencia bajo: CC-BY-SA with attribution
No afiliado con Stack Overflow
¿Es esto KB legal? Sí, aprende por qué
Licencia bajo: CC-BY-SA with attribution
No afiliado con Stack Overflow
¿Es esto KB legal? Sí, aprende por qué