How can I return if an optional navigation property exists from an Entity Framework Query?

Question

I'm trying to return a boolean value using the following query:

var q = from inmate in context.Inmates
        select new
        {
            inmate.Id,
            IsCrazy = inmate.Certified != null
        };

IsCrazy should be true only when the optional Certified navigation property is not null. However, IsCrazy is always being returned as true, regardless of whether there's a link betweenInmate > Certified.

Using the above code and the following data:

Inmate { 1 } --> { Certified }
Inmate { 2 } --> NULL
Inmate { 3 } --> { Certified }

I was expecting the following results:

1, true
2, false
3, true

However, all the results come back true. What am I doing wrong?

I then tried to bring back the optional navigation property instead, but this appears to do an inner join and only returns the crazy inmates:

Inmate { 1 } --> { Certified }
Inmate { 3 } --> { Certified }
// Inmate 2 is missing

EDIT: Forgot to mention, I am using EF 4.0 Code First.

EDIT 2:

This is the SQL output

SELECT 
[Extent1].[Id] AS [Id], 
CASE WHEN (cast(1 as bit) <> cast(0 as bit)) 
THEN cast(1 as bit) WHEN (1 = 0) THEN cast(0 as bit) END AS [C1]
FROM [dbo].[Inmate] AS [Extent1]

Looks totally wrong to me; there's no mentioned of Certified whatsoever.

EDIT 3:

I tried the following code in LINQPad (Dropping the inmate thing, this is my actual code):

from i in Ingredients
join m in Meats 
    on new { i.IngId, i.VersionId } equals new { m.IngId, m.VersionId } into temp
from t in temp.DefaultIfEmpty()
select new
{
    IngId = i.IngId,
    IsMeat = t.MeatTypeId == null ? false : true
};

This will return all 3000 results with the correct true/false values. The same code in Entity Framework will return only the results which have the one-to-one relationship fulfilled.

This is the SQL generated by LINQPad:

-- Region Parameters
DECLARE @p0 Int SET @p0 = 0
DECLARE @p1 Int SET @p1 = 1
-- EndRegion
SELECT [t0].[IngId], 
    (CASE 
        WHEN ([t1].[MeatTypeId]) IS NULL THEN @p0
        ELSE @p1
     END) AS [IsMeat]
FROM [Ingredient] AS [t0]
LEFT OUTER JOIN [MeatIngredient] AS [t1] ON ([t0].[IngId] = [t1].[IngId]) 
    AND ([t0].[VersionId] = [t1].[VersionId])

This is the SQL generated by EF:

SELECT 
[Extent1].[IngId] AS [IngId], 
cast(1 as bit) AS [C1]
FROM  [dbo].[Ingredient] AS [Extent1]
INNER JOIN [dbo].[MeatIngredient] AS [Extent2] 
    ON ([Extent1].[VersionId] = [Extent2].[VersionId]) 
        AND ([Extent1].[IngId] = [Extent2].[IngId])

Answer 1

Don't know if this helps. but try

IsCrazy = !inmate.Certified.Equals(System.DBNull.Value)

Answer 2

Go ahead and try doing a left outer join :

var q = from inmate in context.Inmates
join c in Certified on inmate.cId equals c.Id into temp //replace with whatever you will join on
from temp in temp.DefaultIfEmpty()
select new { inmate.Id, IsCrazy = c.IsCertified != null }; //replace to what it is you want to check

It will be something like that. For a more specific example it might help to know a little bit more about the database structure here. Hope this helps though.

Answer 3

Well, I've managed to get it working as expected by thinking backwards. I.e. checking a nullable meat has an ingredient (or to still with the initial example, check that the certificate has a valid inmate):

var q = from i in context.Ingredients
        let m = i.Meat // AKA Certificate
        select new 
        {
            IngId = i.IngId,
            IsMeat = m.Ingredient != null 
        };

The SQL is nasty for such a simple query. It's about 4 times the size it would have been if I'd written the stored procedure myself. However, it runs in 57ms, so it's not much to worry about.