composite in statement using dapper

Question

Suppose I have 2 tables in 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'

Suppose I want to yield the following result

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

I understand concatenate the string like this is one of the worst thing to do, but can I ask if there is a more elegant way to optimize this statement and can be use in Dapper?

Many thanks

It would've been better if SQL Server supported tuples, but it doesn't: http://stackoverflow.com/questions/8006901/using-tuples-in-sql-in-clause — Lasse V. Karlsen, Apr 01 '16 at 09:03

score 3 · Accepted Answer · answered Apr 01 '16 at 10:17

For the dapper side of things, I'm going to assume the candidate locations are in some kind of list or array, for example:

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

Then you can use:

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

Dapper will do the expansion for you automatically.

As for making it efficient; you could create a computed persisted indexed column, assuming the two fields are on the same table:

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

Now you can efficiently query against xy which is an indexed version of the calculated value.

If the columns are on different tables, it would be better to split the two things at the input and test separately; dapper can't help you with this, so you'd need to use something like a StringBuilder and DynamicParameters (dapper) to construct the query, but you'd want something like:

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

This at least allows the indexes on colA and colB to be useful.

composite in statement using dapper

1 Answers1