Best way to handle WhereIn clauses for queries with large number of parameters

[Meigs2]

Hi!

I'm currently in the process of re-writing some report generation using SqlKata. I'm first running one query to get the items for which the report specified, however, after this first query is completed I have to continue building up the properties of the report object, so I'm running additional queries based off the result of the first. For example, I might have to fetch all of the notes for all the customers in the report. (We're not using Dapper, just raw ADO.NET code, so something along the lines of QueryMultiple is not possible)

The other queries I created use WhereIn given a list of ID's for the items returned from the report and I'm setting the parameters generated by the compiler's NamedBindings with Parameters.AddWithValue. However SQL Server has an upper limit on the number of parameters a query can have. So I'm exploring alternatives to make the secondary queries work.

One easy solution would be to simply use the compilation's ToString() to execute the query, given the result is entirely internal, however as specified in the documentation, you shouldn't use the ToString() method as it opens yourself up to SQL Injection.

Another quick solution would be to re-use the first query and join the result of that query on the others, however the queries can take a while to run, and re-running the original just to join additional data is not ideal.

Is there an "officially" supported way to safely replace parameters generated by the compiler? I'd assume not. If there is no way, what approach would you suggest one takes in this situation?

Thanks!

[ahmad-moussawi]

Hello, why not using the WhereIn(Query) overload?
this way you don't have the hard limit limitation of SQL Server for the WhereIn(List<>)

[Meigs2]

I was considering that, however if the original query takes a long time to execute, wouldn't subsequent queries using WhereIn(Query) take at least the same amount of time as the original (ignoring query caching, etc) plus the time to perform the lookup? I'm only ever using the ID's returned from the original "filtering" query, so I'd like to simply re-use the data we've already fetched from DB. I'm running about 6-10 additional queries off the result of the original.

[ahmad-moussawi]

Yes in that case, this is not the best way to go, I faced the same scenario previously in EF, where I have a lot of ids to check for,
In that case splitting the WhereIn is the best thing you can do.

var ids = new int [] {};
var chunks = ids.SplitInChunkOf(500);  / / or whatever number here

var query = db.Query("Table");

query.Where(q => {
  foreach(chunks as chunk) query.orWhereIn("col", chunk);
});

[Meigs2]

Sounds good. Appreciate the reply!

[mnsrulz]

An overload can be added too in the library itself if not present already