Get index or use pair list in mysql?

.net dapper mysql

Question

I want to do something like the below where ls is a pair (id and value to match blah). Maybe if i could get the index of the list match I can just use the list normally and grab the id in code

select @id from table1 where blah in @ls

Accepted Answer

Dapper is a very thin veneer over SQL. The only thing it adds as a syntax change is IN expansion from:

x in @foo

to

x in (@foo0, @foo1, @foo2, @foo3)

However, I don't think your query can be written like that. The first step, then, is to write your query in regular SQL. If this was SQL-Server, I would be thinking:

  • a UDF that turns a delimited string input into a tabular output
  • an inner join

for example:

select #x.id
from dbo.MyMagicUdf(@s) #x -- has columns id and value
inner join table1 t on t.blah = #x.value -- or whatever the join is

But to repeat: the first step is to write it in SQL for your RDBMS. Once you have that working, it should be a breeze to get dapper to work with it.




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why