Linq to SQL Strange SQL Translation

Question

I have a simple query that is generating some odd SQL translations, which is blowing up my code when the object is saturated.

from x in DataContext.MyEntities
select new 
{
    IsTypeCDA = x.EntityType == "CDA" 
}

I would expect this query should translate to:

SELECT (CASE WHEN [t0].[EntityType] = @p1 THEN 1 ELSE 0 END) as [IsTypeCDA]
...

Instead I get this :

SELECT 
(CASE 
    WHEN @p1 = [t0].[EntityType] THEN 1
    WHEN NOT (@p1 = [t0].[EntityType]) THEN 0
    ELSE NULL
 END) AS [IsTypeCDA]
... 

Since I'm saturating a POCO where IsTypeCDA is a bool, it blows up stating I can't assign null to bool.

Any thoughts?

Edit: fixed the property names so they make sense...

Answer 1

from x in DataContext.MyEntities
select new
{
  IsTypeCDA = x.EntityType == null 
}

c# interpretation (false) or sql interpretation (null)?

This runs in sql so sql interpretation. That's why the funky translation - the operation does return a nullable bool.

Use this query to punt that nullable bool into a plain old bool.

from x in DataContext.MyEntities
select new
{
  IsTypeCDA = ((bool?)(x.EntityType == "CDA")) ?? false
}

Answer 2

Linq to SQL does the "strange" comparison because database values can be NULL. Unfortunately, it doesn't seem to do well translating the tri-valued comparison result. At first blush, I'd wonder why any of your entities have NULL for their EntityType. Modifying your schema to disallow NULL values would be the most straightforward solution.

However, assuming the table is defined this way for legitimate business reasons you can work around it in a couple ways.

First off, you could coalesce the NULLs into a placeholder value.

from x in DataContext.MyEntities
select new 
{
    IsTypeCDA = (x.EntityType ?? "") == "CDA" 
}

Alternately, using String.Equals generates a more thorough comparison that handles NULLs.

from x in DataContext.MyEntities
select new 
{
    IsTypeCDA = string.Equals(x.EntityType, "CDA")
}

Neither of these will generate the simple SQL you were expecting, but they'll both get the job done.