LINQ EndsWith

From the bottom of the Entity Framework Beta 3 docs on Query Execution, “The EndsWith method can also return different results because SQL Server considers two strings to be equal if they only differ in trailing white space, whereas the CLR considers them to be not equal.” That’s not exactly clear, so I spent some time trying out different scenarios.

Case One:

Database Field ColorName : ‘Blue   ’ // 3 spaces

SQL Query: … WHERE ColorName LIKE ‘%e   ’
Returns 1
SQL Query: … WHERE ColorName LIKE ‘%e’
Returns 0
SQL Query: … WHERE ColorName = ‘blue’
Returns 1
SQL Query: … WHERE ColorName = ‘blue   ’
Returns 1

LINQ Query … where c.ColorName.EndsWith(‘e’) select c;
Returns 0
LINQ Query … where c.ColorName.EndsWith(‘e   ’) select c;
Returns 0
LINQ Query … where c.ColorName.EndsWith(‘ ’) select c;
Returns 1
LINQ Query … where c.ColorName.Trim().EndsWith(‘e’) select c;
Returns 1
LINQ Query … where c.ColorName.Trim().EndsWith(‘e   ’) select c;
Returns 1

Case Two:

Database Field ColorName : ‘Blue’

SQL Query: … WHERE ColorName LIKE ‘%e   ’
Returns 0
SQL Query: … WHERE ColorName LIKE ‘%e’
Returns 1
SQL Query: … WHERE ColorName = ‘blue’
Returns 1
SQL Query: … WHERE ColorName = ‘blue   ’
Returns 1

LINQ Query … where c.ColorName.EndsWith(‘e’) select c;
Returns 1
LINQ Query … where c.ColorName.EndsWith(‘e   ’) select c;
Returns 1
LINQ Query … where c.ColorName.EndsWith(‘ ‘) select c;
Returns 1
LINQ Query … where c.ColorName.Trim().EndsWith(‘e’) select c;
Returns 1
LINQ Query … where c.ColorName.Trim().EndsWith(‘e   ’) select c;
Returns 1

So basically if you want to find a string that actually ends with a ‘ ’ using LINQ to Entities, best to use something other than EndsWith. This is certain to cause some major headaches for someone, so hopefully it gets fixed before the EF is out of beta.

This entry was posted in ASP.Net. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>