Mixing
Dapper can't find POCO mapping from SELECT CASE query
I'm extracting information from a SQL Server database using Dapper. The POCO for the information in question is below.
public class Client
{
public string ShortName { get; set; }
public string ContactDetail { get; set; }
public string Street { get; set; }
public string District { get; set; }
public string Town { get; set; }
public string County { get; set; }
public string Postcode { get; set; }
}
When I extract the information for the above object using the query below all the information is mapped correctly, apart from the following line max(case when cd.Type = 1 OR cd.Type = 2 then cd.Detail end) as 'ContactDetail' I believe this may be because I am not simply extracting the data from a table column and I am instead doing some processing using the CASE clause beforehand and therefore Dapper cannot find the correct place to map the information onto.
I thought including the AS clause would help Dapper to find the correct mapping but this didn't actually work.
How can I change my query so that Dapper can map the ContactDetail data correctly?
select
tp.ShortName as 'ShortName',
max(case when cd.Type = 1 OR cd.Type = 2 then cd.Detail end) as 'ContactDetail',
tp.Street,
tp.District,
tp.Town,
tp.County,
tp.PostCode
... (rest of query snipped as unneeded)
I know the query works correctly as I can run it in SSMS and it returns the correct information.
c#
sql-server dapper asked Nov 20 '18 at 16:04
JakeManganJakeMangan
243
add a comment |
I'm extracting information from a SQL Server database using Dapper. The POCO for the information in question is below.
public class Client
{
public string ShortName { get; set; }
public string ContactDetail { get; set; }
public string Street { get; set; }
public string District { get; set; }
public string Town { get; set; }
public string County { get; set; }
public string Postcode { get; set; }
}
When I extract the information for the above object using the query below all the information is mapped correctly, apart from the following line max(case when cd.Type = 1 OR cd.Type = 2 then cd.Detail end) as 'ContactDetail' I believe this may be because I am not simply extracting the data from a table column and I am instead doing some processing using the CASE clause beforehand and therefore Dapper cannot find the correct place to map the information onto.
I thought including the AS clause would help Dapper to find the correct mapping but this didn't actually work.
How can I change my query so that Dapper can map the ContactDetail data correctly?
select
tp.ShortName as 'ShortName',
max(case when cd.Type = 1 OR cd.Type = 2 then cd.Detail end) as 'ContactDetail',
tp.Street,
tp.District,
tp.Town,
tp.County,
tp.PostCode
... (rest of query snipped as unneeded)
I know the query works correctly as I can run it in SSMS and it returns the correct information.
c#
sql-server dapper asked Nov 20 '18 at 16:04
JakeManganJakeMangan
243
add a comment |
I'm extracting information from a SQL Server database using Dapper. The POCO for the information in question is below.
public class Client
{
public string ShortName { get; set; }
public string ContactDetail { get; set; }
public string Street { get; set; }
public string District { get; set; }
public string Town { get; set; }
public string County { get; set; }
public string Postcode { get; set; }
}
When I extract the information for the above object using the query below all the information is mapped correctly, apart from the following line max(case when cd.Type = 1 OR cd.Type = 2 then cd.Detail end) as 'ContactDetail' I believe this may be because I am not simply extracting the data from a table column and I am instead doing some processing using the CASE clause beforehand and therefore Dapper cannot find the correct place to map the information onto.
I thought including the AS clause would help Dapper to find the correct mapping but this didn't actually work.
How can I change my query so that Dapper can map the ContactDetail data correctly?
select
tp.ShortName as 'ShortName',
max(case when cd.Type = 1 OR cd.Type = 2 then cd.Detail end) as 'ContactDetail',
tp.Street,
tp.District,
tp.Town,
tp.County,
tp.PostCode
... (rest of query snipped as unneeded)
I know the query works correctly as I can run it in SSMS and it returns the correct information.
c#
sql-server dapper asked Nov 20 '18 at 16:04
JakeManganJakeMangan
243
I'm extracting information from a SQL Server database using Dapper. The POCO for the information in question is below.
public class Client
{
public string ShortName { get; set; }
public string ContactDetail { get; set; }
public string Street { get; set; }
public string District { get; set; }
public string Town { get; set; }
public string County { get; set; }
public string Postcode { get; set; }
}
When I extract the information for the above object using the query below all the information is mapped correctly, apart from the following line max(case when cd.Type = 1 OR cd.Type = 2 then cd.Detail end) as 'ContactDetail' I believe this may be because I am not simply extracting the data from a table column and I am instead doing some processing using the CASE clause beforehand and therefore Dapper cannot find the correct place to map the information onto.
I thought including the AS clause would help Dapper to find the correct mapping but this didn't actually work.
How can I change my query so that Dapper can map the ContactDetail data correctly?
select
tp.ShortName as 'ShortName',
max(case when cd.Type = 1 OR cd.Type = 2 then cd.Detail end) as 'ContactDetail',
tp.Street,
tp.District,
tp.Town,
tp.County,
tp.PostCode
... (rest of query snipped as unneeded)
I know the query works correctly as I can run it in SSMS and it returns the correct information.
c#
sql-server dapper c#
sql-server dapper asked Nov 20 '18 at 16:04
JakeManganJakeMangan
243
asked Nov 20 '18 at 16:04
JakeManganJakeMangan
243
asked Nov 20 '18 at 16:04
JakeManganJakeMangan
243
asked Nov 20 '18 at 16:04
JakeManganJakeMangan
243
asked Nov 20 '18 at 16:04
JakeManganJakeMangan
243
243
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
Dapper doesn't have any interest or knowledge of what is inside the query - it only cares about the shape of the results. So: whatever is happening - it is part of the query.
My hunches would be:
- the data genuinely is missing and/or
null
- something to do with
nullhandling and null-coalescing, perhaps with themaxcomplicating things
Note that you can't just compare to SSMS output, because SSMS and ADO.NET (SqlClient) can have different SET option defaults, which can make significant yet subtle differences to some queries.
To investigate it properly, I'd really need a reproducible example (presumably with fake data); without that, we're kinda guessing.
To reiterate, though:
I strongly suspect that if you execute the same query via ExecuteReader or similar, you'll find that the value coming back in that position is indeed null (or DBNull).
answered Nov 21 '18 at 10:32
Marc Gravell♦Marc Gravell
781k19421382544
add a comment |
I'm pretty sure the issue is that if you're using the object mapper in Dapper (i.e. Client in <>) then it will simply look for those columns in the matching table. The "ContactDetail" does not exist (it's derived), so it will not find it.
You have a couple of options here, depending on your data and use case.
Get the raw data from Dapper, and then use Linq (or similar) to derive the ContactDetail using logic within the program.
Write a stored procedure based on your query, and use Dapper to run it
Use Dapper's Query command to run your SQL statement, instead of trying to directly map the object.
Let me know if you get stuck on this, or require further explanation.
answered Nov 20 '18 at 17:15
Phil SPhil S
1789
add a comment |
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%2f53396970%2fdapper-cant-find-poco-mapping-from-select-case-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Dapper doesn't have any interest or knowledge of what is inside the query - it only cares about the shape of the results. So: whatever is happening - it is part of the query.
My hunches would be:
- the data genuinely is missing and/or
null
- something to do with
nullhandling and null-coalescing, perhaps with themaxcomplicating things
Note that you can't just compare to SSMS output, because SSMS and ADO.NET (SqlClient) can have different SET option defaults, which can make significant yet subtle differences to some queries.
To investigate it properly, I'd really need a reproducible example (presumably with fake data); without that, we're kinda guessing.
To reiterate, though:
I strongly suspect that if you execute the same query via ExecuteReader or similar, you'll find that the value coming back in that position is indeed null (or DBNull).
answered Nov 21 '18 at 10:32
Marc Gravell♦Marc Gravell
781k19421382544
add a comment |
Dapper doesn't have any interest or knowledge of what is inside the query - it only cares about the shape of the results. So: whatever is happening - it is part of the query.
My hunches would be:
- the data genuinely is missing and/or
null
- something to do with
nullhandling and null-coalescing, perhaps with themaxcomplicating things
Note that you can't just compare to SSMS output, because SSMS and ADO.NET (SqlClient) can have different SET option defaults, which can make significant yet subtle differences to some queries.
To investigate it properly, I'd really need a reproducible example (presumably with fake data); without that, we're kinda guessing.
To reiterate, though:
I strongly suspect that if you execute the same query via ExecuteReader or similar, you'll find that the value coming back in that position is indeed null (or DBNull).
answered Nov 21 '18 at 10:32
Marc Gravell♦Marc Gravell
781k19421382544
add a comment |
Dapper doesn't have any interest or knowledge of what is inside the query - it only cares about the shape of the results. So: whatever is happening - it is part of the query.
My hunches would be:
- the data genuinely is missing and/or
null
- something to do with
nullhandling and null-coalescing, perhaps with themaxcomplicating things
Note that you can't just compare to SSMS output, because SSMS and ADO.NET (SqlClient) can have different SET option defaults, which can make significant yet subtle differences to some queries.
To investigate it properly, I'd really need a reproducible example (presumably with fake data); without that, we're kinda guessing.
To reiterate, though:
I strongly suspect that if you execute the same query via ExecuteReader or similar, you'll find that the value coming back in that position is indeed null (or DBNull).
answered Nov 21 '18 at 10:32
Marc Gravell♦Marc Gravell
781k19421382544
Dapper doesn't have any interest or knowledge of what is inside the query - it only cares about the shape of the results. So: whatever is happening - it is part of the query.
My hunches would be:
- the data genuinely is missing and/or
null
- something to do with
nullhandling and null-coalescing, perhaps with themaxcomplicating things
Note that you can't just compare to SSMS output, because SSMS and ADO.NET (SqlClient) can have different SET option defaults, which can make significant yet subtle differences to some queries.
To investigate it properly, I'd really need a reproducible example (presumably with fake data); without that, we're kinda guessing.
To reiterate, though:
I strongly suspect that if you execute the same query via ExecuteReader or similar, you'll find that the value coming back in that position is indeed null (or DBNull).
answered Nov 21 '18 at 10:32
Marc Gravell♦Marc Gravell
781k19421382544
answered Nov 21 '18 at 10:32
Marc Gravell♦Marc Gravell
781k19421382544
answered Nov 21 '18 at 10:32
Marc Gravell♦Marc Gravell
781k19421382544
answered Nov 21 '18 at 10:32
Marc Gravell♦Marc Gravell
781k19421382544
781k19421382544
add a comment |
add a comment |
I'm pretty sure the issue is that if you're using the object mapper in Dapper (i.e. Client in <>) then it will simply look for those columns in the matching table. The "ContactDetail" does not exist (it's derived), so it will not find it.
You have a couple of options here, depending on your data and use case.
Get the raw data from Dapper, and then use Linq (or similar) to derive the ContactDetail using logic within the program.
Write a stored procedure based on your query, and use Dapper to run it
Use Dapper's Query command to run your SQL statement, instead of trying to directly map the object.
Let me know if you get stuck on this, or require further explanation.
answered Nov 20 '18 at 17:15
Phil SPhil S
1789
add a comment |
I'm pretty sure the issue is that if you're using the object mapper in Dapper (i.e. Client in <>) then it will simply look for those columns in the matching table. The "ContactDetail" does not exist (it's derived), so it will not find it.
You have a couple of options here, depending on your data and use case.
Get the raw data from Dapper, and then use Linq (or similar) to derive the ContactDetail using logic within the program.
Write a stored procedure based on your query, and use Dapper to run it
Use Dapper's Query command to run your SQL statement, instead of trying to directly map the object.
Let me know if you get stuck on this, or require further explanation.
answered Nov 20 '18 at 17:15
Phil SPhil S
1789
add a comment |
I'm pretty sure the issue is that if you're using the object mapper in Dapper (i.e. Client in <>) then it will simply look for those columns in the matching table. The "ContactDetail" does not exist (it's derived), so it will not find it.
You have a couple of options here, depending on your data and use case.
Get the raw data from Dapper, and then use Linq (or similar) to derive the ContactDetail using logic within the program.
Write a stored procedure based on your query, and use Dapper to run it
Use Dapper's Query command to run your SQL statement, instead of trying to directly map the object.
Let me know if you get stuck on this, or require further explanation.
answered Nov 20 '18 at 17:15
Phil SPhil S
1789
I'm pretty sure the issue is that if you're using the object mapper in Dapper (i.e. Client in <>) then it will simply look for those columns in the matching table. The "ContactDetail" does not exist (it's derived), so it will not find it.
You have a couple of options here, depending on your data and use case.
Get the raw data from Dapper, and then use Linq (or similar) to derive the ContactDetail using logic within the program.
Write a stored procedure based on your query, and use Dapper to run it
Use Dapper's Query command to run your SQL statement, instead of trying to directly map the object.
Let me know if you get stuck on this, or require further explanation.
answered Nov 20 '18 at 17:15
Phil SPhil S
1789
answered Nov 20 '18 at 17:15
Phil SPhil S
1789
answered Nov 20 '18 at 17:15
Phil SPhil S
1789
answered Nov 20 '18 at 17:15
Phil SPhil S
1789
1789
add a comment |
add a comment |
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%2f53396970%2fdapper-cant-find-poco-mapping-from-select-case-query%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
