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...

Is DOCUMENT_TYPE the column the EntityType property is mapped to? It looks like you muddied the waters when you cleaned up the generated SQL. — zinglon
– zinglon, Commented Feb 18, 2011 at 15:06
Of what type is the MyEntities property and of what type is EntityType.CDA? — Steven
– Steven, Commented Feb 18, 2011 at 15:17
zinglon : I muddied the waters, when I translated from my query to a more readable example for the post, DOCUMENT_TYPE is the actual column, and it's mapped correctly, I've edited the question to make it more clear. — Master Morality
– Master Morality, Commented Feb 18, 2011 at 15:18
Steven : yeah I messed up when I was writing the post I've fixed it. MyEntities is a table, MyEntities.EntityType is a string EntityType is a static class and EntityType.CDA is a constant string. basically MyEntities.EntityType can be any of n values, but only a handful are important in the scope of this query. — Master Morality
– Master Morality, Commented Feb 18, 2011 at 15:22

Amy B · Accepted Answer · 2011-02-18 15:50:25Z

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 }

Doing it this way keeps the generated SQL free of funky null handling. I think I like it better than my solutions. +1

zinglon · Accepted Answer · 2011-02-18 15:53:29Z

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.

joshcomley · Accepted Answer · 2011-02-18 15:21:25Z

I would have IsTypeCDA as a get only property and select into that class:

public class SomeName { public string EntityType { get; set; } public bool IsTypeCDA { get { return EntityType == EntityType.CDA; } } }

...

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

Not a bad solution, however the resulting IQueryable can be composed in query, which uses that value, and since those get properties can't be translated to SQL it blows up.

Collectives™ on Stack Overflow

Linq to SQL Strange SQL Translation

3 Answers 3

1 Comment

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Related