使用dapper在语句中合成

Question

假设我在sql2008中有两个表

create table table1 (
location varchar(10), 
facility varchar(10),
msgid int,
col5 varchar(20)
)
create table table2 (
msgid int,
name varchar(10),
col3 varchar(20),
col4 varchar(20)
)
insert into table1 (msgid, location, facility, col5) 
select 1, 'F1', 'L1', 'xyz'
union select 2,  'F1', L1', 'abc'
union select 3,  'F1', L1', 'abcd'
union select 4,  'F1', L2', 'abce'
union select 5,  'F2', L1', 'abcf'

insert into table2 (msgid, name, col3, col4)
select 1, 'x', 'dsd','fwefrwe'
union select 2, 'x', 'dsd1','fwefrwe1'
union select 3, 'y', 'dsd2','fwefrwe2'
union select 4, 'z', 'dsd3','fwefrwe3'
union select 5, 'a', 'dsd4','fwefrwe4'

假设我想得到以下结果

select col3,col4,col5 from table1 inner join table2 where name+'^'+facility+'^'+location in ('x^F1^L1', 'z^F1^L2')

我知道像这样连接字符串是最糟糕的事情之一，但我能问一下是否有更优雅的方法来优化这个语句，并且可以在Dapper中使用吗？

非常感谢

Answer 1

对于dapper方面，我将假设候选位置在某种列表或数组中，例如：

var locations = new[] { "x^F1^L1", "z^F1^L2" };

然后你可以使用：

var rows conn.Query<SomeType>(@"
    select col3,col4,col5 from table1 inner join table2
    where name+'^'+facility+'^'+location in @locations",
        new { locations }).AsList();

Dapper会自动为您进行扩展。

至于使其高效；您可以创建一个计算的持久化索引列，假设这两个字段在同一个表上：

create table SomeTable (x nvarchar(20) not null, y nvarchar(20) not null);
alter table SomeTable add xy as x + ':' + y persisted;
create nonclustered index  SomeTable_xy on SomeTable(xy);

现在，您可以有效地查询xy，它是计算值的索引版本。

如果列在不同的表上，最好在输入处拆分这两个内容，并分别进行测试；dapper无法帮助您实现这一点，因此您需要使用类似StringBuilder和DynamicParameters（dapper）的东西来构建查询，但您需要类似的东西

where (x.ColA = @a0 and y.ColB = @b0)
or (x.ColA = @a1 and y.ColB = @b1)
...
or (x.ColA = @a42 and y.ColB = @b42)

这至少允许colA和colB上的索引是有用的。