How can I return if there is an additional navigation property from an Entity Framework query?

Question

I am trying to return a boolean using the following query:

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

IsCrazyshould trueonly be if the optional navigation property is Certifiednot null. However, it IsCrazyalways returns as true, regardless of whether there is a connection between Inmate > Certified.

Using the code above and the following data:

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

I expected the following results:

1, true
2, false
3, true

However, all the results are correct. What am I doing wrong?

Then I tried to return an optional navigation property, but this seems to make the inner join and return only crazy prisoners:

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

EDIT: Forgot to mention, I use EF 4.0 code first.

EDIT 2:

This is 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]

; Certified.

3:

LINQPad (Droping the inmate thing, ):

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

3000 true/false. Entity Framework , - .

SQL, 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])

SQL, 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

, , , . ( - , , ):

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

SQL . 4 , , . , 57 , .

Answer 2

, .

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

Answer 3

:

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

. . , .