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.
I’ve spent some time yesterday and today playing around with the new Entity Designer GUI and Framework. So far I’m impressed with the possibilities and bothered by simple things that could cause repetitive headaches. Here are a few suggestions that I’d offer to the Entity Designer team:
- Ignore the sysdiagrams table when importing by default. Those of us who actually want this can check this box when we import the tables. SQL2005 is kind enough to hide it in the System Tables folder, please do something similar.
- How do I view the XML code? The only time I can easily get access to it is when there is an error. I can’t view it by right clicking on the .edmx file. (Like one can with a Data Access Layer .xsd file.)
- Return types of Navigation Properties are not displayed at all in the designer and difficult to ascertain without plowing through generated code.
- Foreign key naming conventions are completely ignored for imported tables. Please don’t ignore them, or make it easier to see what they were.
To illustrate points three and four above, I’ve made myself a very simple database which might represent maternal genealogies, perhaps for someone studying Mitochondrial DNA.
As you can see, each person has a foreign key to another person who happens to be that person’s mother. Importing this into the Entity Designer via the wizard gives us two very cryptic columns called Person1 and Person2. Conceptually we might understand that each person has both a reference to her mother as well as a reference to all of her daughters. So one of these keys is a singular reference that we’d like to rename to “Mother” and another that we’d like to rename to “Children”. Examining these Navigation Properties in the designer shows:
Person 1
Person 2
Both of them have public getters and setters, both use the same Association, and neither of them list what type they return. To figure how to rename these, we must go to the generated c# code. (We can’t get to the xml code easily after all.)
So we can see from here that Person1 should be our Children/Daughters and Person2 should be Mother. Now we can rename these links appropriately.
Taking a look at a different example will illustrate my comment about ignoring foreign key names. This database is very simple with two tables as follows.
Importing this into the Entity Designer shows:
Now this is a bit easier than the previous problem to fix as the designer shows that the Association is liked to either “LastBrokenBy” or “LastFixedBy”. Is fixing all of these names part of the expected standard process of setting up the Entity Design?