c# - 从 SQL Server 中的 SELECT 获取 bool 值到 C# 中的 bool 值？

Question

我的 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], 

在我的 ASP.NET DTO 中我有:

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; }
}

它给我一个错误，我将不胜感激。

错误是:

message=The specified cast from a materialized 'System.Int32' type to the 'System.Boolean' type is not valid.

Answer 1

最简单的方法(SQL Server 端)是将值 0 和 1 转换为 BIT数据类型:

SELECT  A.CompletedDate,
  CASE
    WHEN (@AdminTestId IS NULL AND @UserTestId IS NULL) 
    THEN CAST(0 AS BIT)
    WHEN (@AdminTestId = temp.AdminTestId AND @UserTestId = A.UserTestId) 
    THEN CAST(1 AS BIT)
    WHEN (@AdminTestId = temp.AdminTestId AND @UserTestId IS NULL) 
    THEN CAST(1 AS BIT)
   ELSE CAST(0 AS BIT)
  END AS [Current], 

或一次完整的表达:

SELECT  A.CompletedDate,
    CAST((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 BIT) AS [Current], 

---

SQL Server Data Type Mappings :

╔═════════════════════════════════╦═════════════════════╦═══════════════════════╗
║ SQL Server Database Engine type ║ .NET Framework type ║ SqlDbType enumeration ║
╠═════════════════════════════════╬═════════════════════╬═══════════════════════╣
║ bit                             ║ Boolean             ║ Bit                   ║
╚═════════════════════════════════╩═════════════════════╩═══════════════════════╝