How do I write an SQL query that has nested filters.
如何编写具有嵌套过滤器的SQL查询。
Info: 2 search filters
信息:2个搜索过滤器
1st filter: Id, Name, Date
2nd filter: depending on first, for Id: exact, range; for Name: exact, like; for Date: exact, range.
第一个过滤器:Id,Name,Date 2nd filter:取决于第一个,Id:exact,range; for name:exact,like;对于日期:确切,范围。
In LINQ code its was done something like:
在LINQ代码中它的完成如下:
theList = somelistFromDb;
case filter1
case "Id"
if filter2 == "exact"
theList.where(x => x == searchkey);
else if filter 2 == "range"
theList.where(x => x >= searchkey && x<=searchkey2);
case "Name"
if filter2 == "exact"
theList.where(x => x == searchkey);
else if filter2 == "like"
theList.where(x => x.contains("searchkey));
...
How do I convert the above LINQ Pseudocode to SQL?
如何将上述LINQ伪代码转换为SQL?
3 个解决方案
#1
1
SELECT
...
WHERE
(:filterParam='Id' AND <all the Id filter conditions> here)
OR
(:filterParam='Name' AND <all the Name filter conditions> here)
#2
1
select * from [Table] where
((@filter1='Id') and
((filter2='exact' and [Table].[Id]=@searchkey) OR
(filter2='range' and [Table].[Id]>=@searchkey and [Table].[Id]<=@searchkey2) ))
OR
((@filter1='Name') and
.....
#3
1
Writing a single TSQL query that satisfies or excludes all the conditions in one go is usually very suboptimal - it leads to terrible query plans. Trying to do all the thinking in TSQL is... somewhat ugly - TSQL simply isn't a great language for it.
编写一个满足或排除所有条件的TSQL查询通常非常不理想 - 它会导致糟糕的查询计划。试图在TSQL中做所有的想法是......有点难看 - TSQL根本不是一个很好的语言。
So: the way I would usually do this is by building the query in C#, for example:
所以:我通常这样做的方法是在C#中构建查询,例如:
static void AppendFilter(StringBuilder filter, string clause)
{
filter.Append(filter.Length == 0 ? " where " : " and ").Append(clause);
}
StringBuilder filter = new StringBuilder();
if(/* some condition */)
AppendFilter(filter, "row.Foo = @foo");
if(/* some condition */)
AppendFilter(filter, "row.Bar > @bar");
// ...
string tsql = "select * from SomeTable row" + filter.ToString();
// pack params, exec
This:
- extends to any number of filters
- generates appropriate TSQL per filter combination, for optimal query-plans
- is simple to maintain
扩展到任意数量的过滤器
为每个过滤器组合生成适当的TSQL,以获得最佳查询计划
很容易维护
Personally, I'd also use dapper to do the execution, as that has (basic) parameter analysis built in, allowing simple:
就个人而言,我也使用dapper来执行,因为它具有内置的(基本)参数分析,允许简单:
var rows = conn.Query<SomeType>(tsql, new { foo, bar, ... }).ToList();
(yet it will still send only the necessary parameters)
(但它仍然只发送必要的参数)
An alternative, though, is to add the parameters in each if
.
但是,另一种方法是在每个if中添加参数。
#1
1
SELECT
...
WHERE
(:filterParam='Id' AND <all the Id filter conditions> here)
OR
(:filterParam='Name' AND <all the Name filter conditions> here)
#2
1
select * from [Table] where
((@filter1='Id') and
((filter2='exact' and [Table].[Id]=@searchkey) OR
(filter2='range' and [Table].[Id]>=@searchkey and [Table].[Id]<=@searchkey2) ))
OR
((@filter1='Name') and
.....
#3
1
Writing a single TSQL query that satisfies or excludes all the conditions in one go is usually very suboptimal - it leads to terrible query plans. Trying to do all the thinking in TSQL is... somewhat ugly - TSQL simply isn't a great language for it.
编写一个满足或排除所有条件的TSQL查询通常非常不理想 - 它会导致糟糕的查询计划。试图在TSQL中做所有的想法是......有点难看 - TSQL根本不是一个很好的语言。
So: the way I would usually do this is by building the query in C#, for example:
所以:我通常这样做的方法是在C#中构建查询,例如:
static void AppendFilter(StringBuilder filter, string clause)
{
filter.Append(filter.Length == 0 ? " where " : " and ").Append(clause);
}
StringBuilder filter = new StringBuilder();
if(/* some condition */)
AppendFilter(filter, "row.Foo = @foo");
if(/* some condition */)
AppendFilter(filter, "row.Bar > @bar");
// ...
string tsql = "select * from SomeTable row" + filter.ToString();
// pack params, exec
This:
- extends to any number of filters
- generates appropriate TSQL per filter combination, for optimal query-plans
- is simple to maintain
扩展到任意数量的过滤器
为每个过滤器组合生成适当的TSQL,以获得最佳查询计划
很容易维护
Personally, I'd also use dapper to do the execution, as that has (basic) parameter analysis built in, allowing simple:
就个人而言,我也使用dapper来执行,因为它具有内置的(基本)参数分析,允许简单:
var rows = conn.Query<SomeType>(tsql, new { foo, bar, ... }).ToList();
(yet it will still send only the necessary parameters)
(但它仍然只发送必要的参数)
An alternative, though, is to add the parameters in each if
.
但是,另一种方法是在每个if中添加参数。