CODEWITHSUNDEEP
sql-serverdapper
user3034931
user3034931

Reputation: 152

composite in statement using dapper

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

Upvotes: 4

Views: 280

Answers (1)

Marc Gravell
Marc Gravell

Reputation: 1063734

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.

Upvotes: 3

Related Questions