-
-
Notifications
You must be signed in to change notification settings - Fork 518
Best way to handle WhereIn clauses for queries with large number of parameters
#622
-
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!
Beta Was this translation helpful? Give feedback.
All reactions
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<>)
Replies: 1 comment 4 replies
-
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<>)
Beta Was this translation helpful? Give feedback.
All reactions
-
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.
Beta Was this translation helpful? Give feedback.
All reactions
-
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); });
Beta Was this translation helpful? Give feedback.
All reactions
-
👍 2
-
Sounds good. Appreciate the reply!
Beta Was this translation helpful? Give feedback.
All reactions
-
👍 1
-
An overload can be added too in the library itself if not present already
Beta Was this translation helpful? Give feedback.