I have this code in my SELECT:
SELECT A.CompletedDate,
CASE
WHEN (@AdminTestId IS NULL AND @UserTestId IS NULL) THEN 0
WHEN (@AdminTestId = temp.AdminTestId AND @UserTestId = A.UserTestId) THEN 1
WHEN (@AdminTestId = temp.AdminTestId AND @UserTestId IS NULL) THEN 1
ELSE 0
END AS [Current],
and in my ASP.NET DTO I have:
public partial class GetTestsDTO
{
public bool? GradePass { get; set; }
// the following line is what is giving the error. If I remove
// the line it will not try to convert the data and there is no
// error. Note that I want to put this into a bool as web.api
// then passes it to the client as a bool
public bool Current { get; set; }
public DateTime? CompletedDate { get; set; }
}
It's giving me an error and I would appreciate some help.
The error is:
message=The specified cast from a materialized 'System.Int32' type to the 'System.Boolean' type is not valid.
4条答案
按热度按时间gxwragnw1#
The easiest way (SQL Server side) is to convert values
0
and1
toBIT
datatype:or entire expression at once:
SQL Server Data Type Mappings :
ar5n3qh52#
As noted in the comments you are returning an integer. You need to return a
bit
, which ASP.NET will understand as a Boolean.Or as pointed out Damien_The_Unbeliever in the comments below, you could wrap the whole
CASE
statement in aCONVERT
.You could also use
CAST(1 AS bit)
andCAST(0 AS bit)
in replace ofCONVERT(bit, 1)
andCONVERT(bit, 0)
respectively (see T-SQL Cast versus Convert for more information).You could also perform the conversion client-side:
ewm0tg9j3#
As the previous comments suggest, SQL cannot return the "boolean" value, and you are not returning it either.
My suggestion/solution (that I use, and have seen used everywhere I worked) is to simply recover that Integer in your object, and then use a 'map' function that will transform that Integer to a Boolean.
The (simple) method will do something like this (attention; Java version ahead):
Best of luck.
9nvpjoqh4#
below too works for me :-
I tried returning Boolean in like 'true' / 'false' and it too worked.
Environ:
C# - Dapper
SQL :-
Sql Server 2012
Example:-
CASE WHEN (@AdminTestId IS NULL AND @UserTestId IS NULL) THEN 'false'
WHEN (@AdminTestId = temp.AdminTestId AND @UserTestId = A.UserTestId) THEN 'true'
Both worked
'true' or 'TRUE'
OR'false' or 'FALSE'
-- both the cases.So besides the accepted answer, which is a working answer, this also worked for me, yes, it may be attributed to Dapper as well, if, in case, its doesn't work in normal ADO.NET