How to supply v. large WHERE clauses using dapper
I'm struggling to find answers online for this so excuse my lack of research, I've gone straight to the question at hand.
I am building up the following query in C# using dapper;
SELECT BookName
FROM dbo.Books
WHERE {predicates}
Currently I have a private function which takes a key value list of column and value and converts it into a bunch of where clauses. For example;
var searchTerms = new List<KeyValuePair<string, string>>{
new KeyValuePair("ReferenceId", "42"),
new KeyValuePair("AuthorId", "1")
};
This will be converted into;
SELECT BookName
FROM dbo.Books
WHERE ReferenceId = 41
AND AuthorId = 1
So this is working and it's "Ok".
The Problem At Hand
The user could and has entered into the UI, 10'000 Id's. The UI and server have no issue handling this but SQL server isn't happy with me, I get the following;
The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request
Now I knew this limitation existed and I also know the limitation exists when using the IN statement. The question is, how do I get around it?
My first thought was to pass in json and change the query to something like this;
SELECT BookName
FROM dbo.Books
WHERE ReferenceId IN (
SELECT UpcId FROM OPENJSON(@json) WITH(UpcId bigint '$')
)
As far as I can tell this solution would work but given my original scenario, what if the user pastes in 10'000 Author Id's.
I'm expecting at least one response to be "Simply your query" and I wish this was possible. We are talking about a 13 million row table with 20+ columns, most of which are important to someone.
An Elegant solution I would like...
Ideally, and I am not sure if this is possible...
I would quite like to pass the data in as json and populate the following declared table;
DECLARE @booksToFind TABLE (Column nvarchar(max), Value nvarchar(max))
Now I know how to populate this table, but I have absolutely no idea if it would be possible to create a query to use those values as columns and generate what is the equivalent where clause in an elegant query!
c# sql dapper
asked Nov 14 '18 at 22:04
HeberdaHeberda
580523
|
show 1 more comment
I'm struggling to find answers online for this so excuse my lack of research, I've gone straight to the question at hand.
I am building up the following query in C# using dapper;
SELECT BookName
FROM dbo.Books
WHERE {predicates}
Currently I have a private function which takes a key value list of column and value and converts it into a bunch of where clauses. For example;
var searchTerms = new List<KeyValuePair<string, string>>{
new KeyValuePair("ReferenceId", "42"),
new KeyValuePair("AuthorId", "1")
};
This will be converted into;
SELECT BookName
FROM dbo.Books
WHERE ReferenceId = 41
AND AuthorId = 1
So this is working and it's "Ok".
The Problem At Hand
The user could and has entered into the UI, 10'000 Id's. The UI and server have no issue handling this but SQL server isn't happy with me, I get the following;
The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request
Now I knew this limitation existed and I also know the limitation exists when using the IN statement. The question is, how do I get around it?
My first thought was to pass in json and change the query to something like this;
SELECT BookName
FROM dbo.Books
WHERE ReferenceId IN (
SELECT UpcId FROM OPENJSON(@json) WITH(UpcId bigint '$')
)
As far as I can tell this solution would work but given my original scenario, what if the user pastes in 10'000 Author Id's.
I'm expecting at least one response to be "Simply your query" and I wish this was possible. We are talking about a 13 million row table with 20+ columns, most of which are important to someone.
An Elegant solution I would like...
Ideally, and I am not sure if this is possible...
I would quite like to pass the data in as json and populate the following declared table;
DECLARE @booksToFind TABLE (Column nvarchar(max), Value nvarchar(max))
Now I know how to populate this table, but I have absolutely no idea if it would be possible to create a query to use those values as columns and generate what is the equivalent where clause in an elegant query!
c# sql dapper
asked Nov 14 '18 at 22:04
HeberdaHeberda
580523
1
It feels like something went horribly wrong in your DB design, that you think you need to do a WHERE with 2.1 thousand parameters. Either you did not properly resolve a N:M relations. Or you maneuvered yourself deep into "XY Problem" territory. meta.stackexchange.com/a/66378
– Christopher
Nov 14 '18 at 22:13
2
You could make a temp table and insert those ids into that table. Then you would inner join the ids.
– penleychan
Nov 14 '18 at 22:15
@Christopher this is a contrived example somewhat, but a well designed db could still have 6 million book Id's and at least one other property, so just look at it from that angle? and I want to say where the Id in is a list of 5 thousand. penleychan That solution works really well when there is only one fixed column being searched, at the moment this is my best option and I will be using it though :)
– Heberda
Nov 14 '18 at 22:26
@Heberda: If you got 6 million book ID's, it is a IN clause with a single table valued parameter. Alternatively: There is no point retriving that much data anyway. The user can not work with anything past 100 fields of data. And counts around 25 fields/page are way more feasible. And retreiving bulk data to then do filtering in teh UI is a terrible idea. If you actually do search for 2100 Book ID's, then you can not display the results in one go anyway. So you might as well do paging on the searched for book ID's/the query in general.
– Christopher
Nov 14 '18 at 22:31
It is paged don't worry, only 10 rows are actually pulled out of SQL. This is just to support filtering, and by filtering I mean the user can paste in X column values :sigh:
– Heberda
Nov 14 '18 at 23:05
|
show 1 more comment
I'm struggling to find answers online for this so excuse my lack of research, I've gone straight to the question at hand.
I am building up the following query in C# using dapper;
SELECT BookName
FROM dbo.Books
WHERE {predicates}
Currently I have a private function which takes a key value list of column and value and converts it into a bunch of where clauses. For example;
var searchTerms = new List<KeyValuePair<string, string>>{
new KeyValuePair("ReferenceId", "42"),
new KeyValuePair("AuthorId", "1")
};
This will be converted into;
SELECT BookName
FROM dbo.Books
WHERE ReferenceId = 41
AND AuthorId = 1
So this is working and it's "Ok".
The Problem At Hand
The user could and has entered into the UI, 10'000 Id's. The UI and server have no issue handling this but SQL server isn't happy with me, I get the following;
The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request
Now I knew this limitation existed and I also know the limitation exists when using the IN statement. The question is, how do I get around it?
My first thought was to pass in json and change the query to something like this;
SELECT BookName
FROM dbo.Books
WHERE ReferenceId IN (
SELECT UpcId FROM OPENJSON(@json) WITH(UpcId bigint '$')
)
As far as I can tell this solution would work but given my original scenario, what if the user pastes in 10'000 Author Id's.
I'm expecting at least one response to be "Simply your query" and I wish this was possible. We are talking about a 13 million row table with 20+ columns, most of which are important to someone.
An Elegant solution I would like...
Ideally, and I am not sure if this is possible...
I would quite like to pass the data in as json and populate the following declared table;
DECLARE @booksToFind TABLE (Column nvarchar(max), Value nvarchar(max))
Now I know how to populate this table, but I have absolutely no idea if it would be possible to create a query to use those values as columns and generate what is the equivalent where clause in an elegant query!
c# sql dapper
asked Nov 14 '18 at 22:04
HeberdaHeberda
580523
I'm struggling to find answers online for this so excuse my lack of research, I've gone straight to the question at hand.
I am building up the following query in C# using dapper;
SELECT BookName
FROM dbo.Books
WHERE {predicates}
Currently I have a private function which takes a key value list of column and value and converts it into a bunch of where clauses. For example;
var searchTerms = new List<KeyValuePair<string, string>>{
new KeyValuePair("ReferenceId", "42"),
new KeyValuePair("AuthorId", "1")
};
This will be converted into;
SELECT BookName
FROM dbo.Books
WHERE ReferenceId = 41
AND AuthorId = 1
So this is working and it's "Ok".
The Problem At Hand
The user could and has entered into the UI, 10'000 Id's. The UI and server have no issue handling this but SQL server isn't happy with me, I get the following;
The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request
Now I knew this limitation existed and I also know the limitation exists when using the IN statement. The question is, how do I get around it?
My first thought was to pass in json and change the query to something like this;
SELECT BookName
FROM dbo.Books
WHERE ReferenceId IN (
SELECT UpcId FROM OPENJSON(@json) WITH(UpcId bigint '$')
)
As far as I can tell this solution would work but given my original scenario, what if the user pastes in 10'000 Author Id's.
I'm expecting at least one response to be "Simply your query" and I wish this was possible. We are talking about a 13 million row table with 20+ columns, most of which are important to someone.
An Elegant solution I would like...
Ideally, and I am not sure if this is possible...
I would quite like to pass the data in as json and populate the following declared table;
DECLARE @booksToFind TABLE (Column nvarchar(max), Value nvarchar(max))
Now I know how to populate this table, but I have absolutely no idea if it would be possible to create a query to use those values as columns and generate what is the equivalent where clause in an elegant query!
c# sql dapper
c# sql dapper
asked Nov 14 '18 at 22:04
HeberdaHeberda
580523
asked Nov 14 '18 at 22:04
HeberdaHeberda
580523
asked Nov 14 '18 at 22:04
HeberdaHeberda
580523
asked Nov 14 '18 at 22:04
HeberdaHeberda
580523
asked Nov 14 '18 at 22:04
HeberdaHeberda
580523
580523
1
It feels like something went horribly wrong in your DB design, that you think you need to do a WHERE with 2.1 thousand parameters. Either you did not properly resolve a N:M relations. Or you maneuvered yourself deep into "XY Problem" territory. meta.stackexchange.com/a/66378
– Christopher
Nov 14 '18 at 22:13
2
You could make a temp table and insert those ids into that table. Then you would inner join the ids.
– penleychan
Nov 14 '18 at 22:15
@Christopher this is a contrived example somewhat, but a well designed db could still have 6 million book Id's and at least one other property, so just look at it from that angle? and I want to say where the Id in is a list of 5 thousand. penleychan That solution works really well when there is only one fixed column being searched, at the moment this is my best option and I will be using it though :)
– Heberda
Nov 14 '18 at 22:26
@Heberda: If you got 6 million book ID's, it is a IN clause with a single table valued parameter. Alternatively: There is no point retriving that much data anyway. The user can not work with anything past 100 fields of data. And counts around 25 fields/page are way more feasible. And retreiving bulk data to then do filtering in teh UI is a terrible idea. If you actually do search for 2100 Book ID's, then you can not display the results in one go anyway. So you might as well do paging on the searched for book ID's/the query in general.
– Christopher
Nov 14 '18 at 22:31
It is paged don't worry, only 10 rows are actually pulled out of SQL. This is just to support filtering, and by filtering I mean the user can paste in X column values :sigh:
– Heberda
Nov 14 '18 at 23:05
|
show 1 more comment
1
It feels like something went horribly wrong in your DB design, that you think you need to do a WHERE with 2.1 thousand parameters. Either you did not properly resolve a N:M relations. Or you maneuvered yourself deep into "XY Problem" territory. meta.stackexchange.com/a/66378
– Christopher
Nov 14 '18 at 22:13
2
You could make a temp table and insert those ids into that table. Then you would inner join the ids.
– penleychan
Nov 14 '18 at 22:15
@Christopher this is a contrived example somewhat, but a well designed db could still have 6 million book Id's and at least one other property, so just look at it from that angle? and I want to say where the Id in is a list of 5 thousand. penleychan That solution works really well when there is only one fixed column being searched, at the moment this is my best option and I will be using it though :)
– Heberda
Nov 14 '18 at 22:26
@Heberda: If you got 6 million book ID's, it is a IN clause with a single table valued parameter. Alternatively: There is no point retriving that much data anyway. The user can not work with anything past 100 fields of data. And counts around 25 fields/page are way more feasible. And retreiving bulk data to then do filtering in teh UI is a terrible idea. If you actually do search for 2100 Book ID's, then you can not display the results in one go anyway. So you might as well do paging on the searched for book ID's/the query in general.
– Christopher
Nov 14 '18 at 22:31
It is paged don't worry, only 10 rows are actually pulled out of SQL. This is just to support filtering, and by filtering I mean the user can paste in X column values :sigh:
– Heberda
Nov 14 '18 at 23:05
1
1
It feels like something went horribly wrong in your DB design, that you think you need to do a WHERE with 2.1 thousand parameters. Either you did not properly resolve a N:M relations. Or you maneuvered yourself deep into "XY Problem" territory. meta.stackexchange.com/a/66378
– Christopher
Nov 14 '18 at 22:13
It feels like something went horribly wrong in your DB design, that you think you need to do a WHERE with 2.1 thousand parameters. Either you did not properly resolve a N:M relations. Or you maneuvered yourself deep into "XY Problem" territory. meta.stackexchange.com/a/66378
– Christopher
Nov 14 '18 at 22:13
2
2
You could make a temp table and insert those ids into that table. Then you would inner join the ids.
– penleychan
Nov 14 '18 at 22:15
You could make a temp table and insert those ids into that table. Then you would inner join the ids.
– penleychan
Nov 14 '18 at 22:15
@Christopher this is a contrived example somewhat, but a well designed db could still have 6 million book Id's and at least one other property, so just look at it from that angle? and I want to say where the Id in is a list of 5 thousand. penleychan That solution works really well when there is only one fixed column being searched, at the moment this is my best option and I will be using it though :)
– Heberda
Nov 14 '18 at 22:26
@Christopher this is a contrived example somewhat, but a well designed db could still have 6 million book Id's and at least one other property, so just look at it from that angle? and I want to say where the Id in is a list of 5 thousand. penleychan That solution works really well when there is only one fixed column being searched, at the moment this is my best option and I will be using it though :)
– Heberda
Nov 14 '18 at 22:26
@Heberda: If you got 6 million book ID's, it is a IN clause with a single table valued parameter. Alternatively: There is no point retriving that much data anyway. The user can not work with anything past 100 fields of data. And counts around 25 fields/page are way more feasible. And retreiving bulk data to then do filtering in teh UI is a terrible idea. If you actually do search for 2100 Book ID's, then you can not display the results in one go anyway. So you might as well do paging on the searched for book ID's/the query in general.
– Christopher
Nov 14 '18 at 22:31
@Heberda: If you got 6 million book ID's, it is a IN clause with a single table valued parameter. Alternatively: There is no point retriving that much data anyway. The user can not work with anything past 100 fields of data. And counts around 25 fields/page are way more feasible. And retreiving bulk data to then do filtering in teh UI is a terrible idea. If you actually do search for 2100 Book ID's, then you can not display the results in one go anyway. So you might as well do paging on the searched for book ID's/the query in general.
– Christopher
Nov 14 '18 at 22:31
It is paged don't worry, only 10 rows are actually pulled out of SQL. This is just to support filtering, and by filtering I mean the user can paste in X column values :sigh:
– Heberda
Nov 14 '18 at 23:05
It is paged don't worry, only 10 rows are actually pulled out of SQL. This is just to support filtering, and by filtering I mean the user can paste in X column values :sigh:
– Heberda
Nov 14 '18 at 23:05
|
show 1 more comment
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53309413%2fhow-to-supply-v-large-where-clauses-using-dapper%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53309413%2fhow-to-supply-v-large-where-clauses-using-dapper%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
It feels like something went horribly wrong in your DB design, that you think you need to do a WHERE with 2.1 thousand parameters. Either you did not properly resolve a N:M relations. Or you maneuvered yourself deep into "XY Problem" territory. meta.stackexchange.com/a/66378
– Christopher
Nov 14 '18 at 22:13
2
You could make a temp table and insert those ids into that table. Then you would inner join the ids.
– penleychan
Nov 14 '18 at 22:15
@Christopher this is a contrived example somewhat, but a well designed db could still have 6 million book Id's and at least one other property, so just look at it from that angle? and I want to say where the Id in is a list of 5 thousand. penleychan That solution works really well when there is only one fixed column being searched, at the moment this is my best option and I will be using it though :)
– Heberda
Nov 14 '18 at 22:26
@Heberda: If you got 6 million book ID's, it is a IN clause with a single table valued parameter. Alternatively: There is no point retriving that much data anyway. The user can not work with anything past 100 fields of data. And counts around 25 fields/page are way more feasible. And retreiving bulk data to then do filtering in teh UI is a terrible idea. If you actually do search for 2100 Book ID's, then you can not display the results in one go anyway. So you might as well do paging on the searched for book ID's/the query in general.
– Christopher
Nov 14 '18 at 22:31
It is paged don't worry, only 10 rows are actually pulled out of SQL. This is just to support filtering, and by filtering I mean the user can paste in X column values :sigh:
– Heberda
Nov 14 '18 at 23:05