sql-server - 使用 dapper 在语句中组合

Question

假设我在 sql2008 中有 2 个表

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

对于简洁的一面，我将假设候选位置位于某种列表或数组中，例如:

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有用。