Как построить динамическое предложение Where с Dapper при передаче в модель

У меня есть примерная модель, которая выглядит так:

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

В моем репозитории я хочу создать метод поиска, в который я передаю свою модель, но не все поля всегда будут заполнены. Я хочу создать WHERE и AND на основе того, заполнено ли поле в модели или нет. Если поле не заполнено, я не хочу создавать для него предложение WHERE.

Например, если я передаю FirstName = "Bob" и City = "Boston", тогда я хочу, чтобы мой поиск выглядел так:

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

Поскольку я не прошел в Id или LastName, я не хочу, чтобы они добавили запрос. Если я просто перейду в City = "Boston", тогда я хочу, чтобы это выглядело так:

SELECT * FROM PersonTable WHERE City = @city

Мой метод repo будет выглядеть примерно так.

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

Мой вопрос в том, как правильно создать это в моем методе репо?

Ответ 1

Вы также можете использовать Dapper SqlBuilder.

Обратите внимание, что вам придется установить пакет Dapper.SqlBuilder NuGet, поскольку он не входит в основной дистрибутив Dapper.

Вот пример:

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

Вы можете найти несколько примеров здесь.

Ответ 2

Это должно помочь вам, просто и понятно:

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

Ответ 3

bool isFirstWhereSet = false;
bool isCityWhereSet = false;
string sqlQuery = "SELECT * FROM PersonTable "  ;
if (! String.IsNullOrEmpty(model.FirstName ))
{
sqlQuery  =sqlQuery  + "WHERE FirstName [email protected]" ;
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();
}

Ответ 4

Вы можете использовать библиотеку ExpressionExtensionSQL. Эта библиотека преобразует лямбда-выражения в предложения where и может использоваться с dapper и ADO.