EDMX generates Nullable bool instead of bool

Question

I have written a simple stored procedure that returns one select.

When I have updated my EDMX model and added the new stored procedure I wondered to see that the Result that was returned had a Nullable bool (in the stored procedure I always return 0 or 1).

Why does it generates Nullable bool and not bool? And how can I change the stored procedure so that it will generate bool?

Here is the stored procedure (this is not the real stored procedure, its just to demonstrate the problem):

ALTER PROCEDURE [dbo].[TestBool]
AS
BEGIN
    SET NOCOUNT ON;

    SELECT  Title,
            CONVERT(BIT, IIF([Address] = 'America', 1, 0)) IsAmerica,
            CONVERT(BIT, IIF(Duration > 100, 1, 0)) IsLong
    FROM    dbo.Events
    WHERE   UserId > 10
END

Address is not null while Duration is null (but this does not matter because both of the values are generated to Nullable bool).

Here is an image of the EDMX result.

Thanks in advance :)

Answer 1

The use of Convert or Cast alone does not guarantee an non-nullable output on the edmx. You need to wrap the whole thing with isnull() as shown below:

isnull(cast(case when myBooleanField is not null then 1 else 0 end as bit),0) as IsBooleanValue