如何在使用Dapper时通过传递模型构建动态Where子句

Question

如何在使用Dapper时通过传递模型构建动态Where子句

45

我有一个长这样的示例模型：

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

我的仓库方法可能看起来像这样

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

我的问题是如何正确地在我的存储库方法中构建这个东西？

- Eldorian

你可以有条件地附加where子句，对吧？ - Shyju

1

我认为是这样，是的。但是，我正在寻找一个不错的例子来说明如何做到这一点。 - Eldorian

6个回答

21

这应该对你有帮助，干净而简单：

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

- JFM

5

使用C#条件语句构建SQL查询较为繁琐。建议考虑使用"OPTION(RECOMPILE)"，以便查询优化可以考虑实际提供的参数。请注意，此方法比使用条件语句更加简洁有效。 - bbsimonbb

1

如果我没有选择使用Dapper.SqlBuilder，我会选择这个作为被接受的答案。 - Eldorian

1

那么对于 List<int> 或 List<string> 的参数呢？ - Kiquenet

再次强调bbsimonbbs的观点，如果您不添加OPTION (RECOMPILE)，在某些情况下，您的查询可能会非常慢。SQL将为一个执行路径进行优化，只有该路径才能合理运行。 - Marie

关于OPTION(RECOMPILE)语句，非常感谢bbsimonbb和Marie的指出，我已经将其添加到帖子中。 - JFM

这样做的缺点是每次都需要重新编译查询，非常低效。 - Jon49

8

DapperQueryBuilder 是一个替代 Dapper.SqlBuilder 的工具，但更易于使用：

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>() 调用 Dapper 传递基础 SQL 和参数，基础查询是完全参数化的 SQL（例如 WHERE FirstName = @p0 AND LastName = @p1）。参数会从字符串插值中自动捕获（但是对 SQL 注入是安全的）。

--

可选语法
类似于 Dapper.SqlBuilder，您还可以使用 /**where**/ 语法（它将自动连接条件，无需手动使用 AND 或 1==1 技巧）：

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

免责声明：本人是该库的作者

- drizin

更简单的方法对我不起作用。编译器说无法从字符串隐式转换为QueryBuilder。我在这里缺少什么... - ClownCoder

1

@ClownCoder，你要么没有使用最新的NuGet包，要么（最有可能的情况）你正在尝试附加字符串而不是FormattableString（字符串插值，请注意$符号）。 - drizin

确实。在写这个评论的时候，我使用的是旧版软件包（1.1.0），最新版本是1.2.4。非常感谢！ - ClownCoder

这不容易受到 SQL 注入攻击吗？ - taiji123

1

@taiji123 不，它是安全的。该库正是为此而设计 - 它不是常规字符串插值。请查看文档。https://github.com/Drizin/DapperQueryBuilder/ - drizin

0

你可以使用ExpressionExtensionSQL库。该库将lambda表达式转换为where子句，并可与dapper和ADO一起使用。

- Felipe Spinelli

0

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

- FakeisMe

0

这个语法代码对我起作用，没有遇到任何问题。

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

- hardik patel

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Void Ray · Accepted Answer

您也可以使用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);
    }

您可以在这里找到一些示例。