Linq to Entity comparing strings ignores spaces

Question

When using LINQ to an entity, string comparisons ignore spaces.

My table has a nchar (10) column, so any stored data, if it's not 10 characters, will fill the rest with blank spaces. Below I compare "ncharTextColumn" with the string "Four" . And even if ncharText is equal to "Four " this will lead to a match, and the variable "result" will contain 1 entry

 TestEntities1 entity = new TestEntities1(); var result = entity.Table_1.Where(e => e.ncharText == "Four"); 

Is there an explanation for this and a way around this, or will I have to call ToList for my request before any comparisons like this.

 var newList = result.ToList().Where(e => e.ncharText == "Four"); 

This code now correctly returns 0 records, since it takes into account spaces. However, accessing the list before comparing can lead to loading a large amount of memory into memory, which will not be used in the end.

Answer 1

This answer explains why.

SQL Server follows the ANSI / ISO SQL-92 specifications (Section 8.2, General Rules No. 3) on how to compare strings with spaces. The ANSI standard requires padding for character strings used in comparisons so that their lengths match before comparing them. padding directly affects the semantics of the WHERE and HAVING clauses of predicates and other Transact-SQL string mappings. For example, Transact-SQL considers the lines 'abc' and 'abc' to be equivalent for most comparison operations.

The only exception to this rule is the LIKE predicate. When the right side of the LIKE predicate expression has a trailing space value, SQL Server does not insert two values ​​into the same length before the comparison occurs. Because the purpose of a LIKE predicate, by definition, is to make simple row equality tests easier, this does not violate the section of the ANSI SQL-92 specification mentioned earlier.

Internally, LINQ simply creates SQL queries for your database.