parameterized IN clauses

[mkaragits]

Hi,

Would it be possible to add parameterized IN clauses?
So that something like this would work:
connection.ReadAsync(@"SELECT * FROM Table WHERE id IN @list",
new { list = new[] { 1, 2, 3 } });

Thanks

[vbilopav]

Array types in PostgreSQL are handled properly and you can do pretty much something like that, but only if you're using PostgreSQL. In that case, your list or your array parameter will be mapped to PostgreSQL array type and you can do whatever from there ....

[mkaragits]

Yea, I am using MSSQL at the moment and switching is not an option, so that is what I am asking. Any chance you will implement it? It is a fairly common use case that is a pain in the ass to deal with normally.

[vbilopav]

How would that work?

For example, if we have a query with parameter @p like this select @p.

And that parameter is enumerable type, either list or an array, for example new[] { 1, 2, 3 }.

Then parameter parser would skip parameter @p and create three parameters instead of one: @p1, @p2, and @p3, with respective values 1, 2, and 3.

And then, in the last step that query string would have to be parsed from this select @p to this select @p1, @p2, @p2, basically, just search and replace the old parameter placeholder with a CSV list of new parameters.

And of course, only if the connection type isn't PostgreSQL.

[mkaragits]

Yes, more or less. Could cause hard to detect problems if someone accidentally uses another parameter that matches in name to what you expand to though. Perhaps give them randomized names instead of consecutive.

[vbilopav]

That looks like an interesting feature but I'm a little overwhelmed right now, I'll try to do it over the weekend, but I can't promise anything.

[mkaragits]

Good enough for me. Thanks!

[vbilopav]

@mkaragits here you go:
https://github.com/vb-consulting/Norm.net/blob/master/changelog.md#420-2022-04-07

Don't forget to ad a star on this repo and tell others .net developers about this package.

[mkaragits]

Thank you!