如何使用具有多个属性的集合过滤Dapper?
问题描述:
我对Dapper有点新,并且正试图找到一种干净的方式将过滤器参数传递给具有多个属性的集合的SQL Query。如何使用具有多个属性的集合过滤Dapper?
我的收藏是这样的:
[{
Prop1: 'A Value 1',
Prop2: 'B Value 1'
},
{
Prop1: 'A Value 2',
Prop2: 'B Value 2'
}]
应导致SQL查询看起来像这样其中:
select *
from SampleTable
where
([ColumnA]='A Value 1' and [ColumnB]='B Value 1')
or ([ColumnA]='A Value 2' and [ColumnB]='B Value 2')
注:事情是什么如下所示不会起作用,因为这两个属性PropA和PropB需要一起过滤。
string query = @"select *
from SampleTable
where [ColumnA] in (@PropA_Value)
and [ColumnB] in (@PropB_Value)"
con.Query<T>(query, new{PropA_Value = PropA,PropB_Value = PropB}).AsList();
答
可以动态使用下面的辅助类生成过滤字符串:
public static class DapperHelper
{
private const string SingleTupleFormat = " [{0}] = '{1}' {2}";
private const string AndString = "AND";
private const string OrString = "OR";
private static string ToSqlTuple(List<Dictionary<string, string>> filters)
{
string filterParam = string.Empty;
foreach (var filter in filters)
{
//Construct single tuple
string tuple = filter.ToList().Aggregate(string.Empty,
(current, pair) => current + String.Format(SingleTupleFormat, pair.Key, pair.Value, AndString));
//Concatenate tuples by OR, string.Format to combine the different filters
filterParam += string.Format(" ({0}) {1}", tuple.TrimEnd(AndString), OrString);
}
return filterParam.TrimEnd(OrString);
}
public static string TrimEnd(this string source, string value)
{
if (!source.EndsWith(value))
return source;
return source.Remove(source.LastIndexOf(value));
}
}
用法:
string query = @"select *
from SampleTable
where @where";
List<Dictionary<string, string>> filters = new List<Dictionary<string, string>>() {
new Dictionary<string, string>(){{"ColumnA", "A Value 1"},{"ColumnB", "A Value 2"}},
new Dictionary<string, string>(){{"ColumnA", "B Value 1"},{"ColumnB", "B Value 2"}}
};
var tuple = DapperHelper.ToSqlTuple(filters);
query = query.Replace("@where", string.IsNullOrEmpty(tuple) ? "1=1" : tuple); //Use 1=1 if tuple is empty or null
var data = con.Query<T>(query).AsList();
查询字符串看起来像:
select *
from SampleTable
where ([ColumnA] = 'A Value 1' AND [ColumnB] = 'A Value 2')
OR ([ColumnA] = 'B Value 1' AND [ColumnB] = 'B Value 2')
谢谢,我觉得这真的很有帮助。虽然不是Dapper内置的解决方案,但这绝对是可重复使用的,也是一个不错的解决方案。我认为要解决这个问题的另一件事是检查SQL注入,因为我们没有使用传递参数的默认Dapper方法。 – m0g
是的,我的解决方案只是展示如何构建过滤器字符串,而不考虑SQL注入等其他功能。 – Yared
此链接可能也有帮助; https://github.com/tmsmith/Dapper-Extensions/wiki/Predicates 请参阅'多个复合谓词(谓词组)'部分 – Yared