I have a question:
select '[' + p.Firstname + ']' from Person p where p.Firstname = 'Johanne'
There are other people with this first name in the table, and some of them have a trailing space after the value (bad insertion of the values, it will be corrected).
Why then does this query return the following information (I added the brackets to show the spaces):
[Johanne] [Johanne ] [Johanne ] [Johanne]
Could this be a configuration issue? The actual query, which is used in this example, is from entity framework version 6. How can I stop it?
I was able to make it work by utilizing EF6 and the
a process like this:
ctx.Person.FirstOrDefault(p => p.FirstName == "Johanne" && SqlFunctions.DataLength(p.FirstName) == "Johanne".Length);
When comparing strings with spaces, SQL Server adheres to the ANSI/ISO SQL-92 specification (Section 8.2, General rules #3). In order to ensure that the character strings used in comparisons have the same length before being compared, the ANSI standard mandates padding. The padding has a direct impact on other Transact-SQL string comparisons as well as the semantics of the WHERE and HAVING clause predicates. Transact-SQL, for instance, regards the strings "abc" and "abc" as being equal for the majority of comparison operations.
The LIKE predicate is the only one that deviates from this rule. The two values are not padded to the same length before the comparison when the right side of a LIKE predicate expression contains a value with a trailing space. This does not infringe on the earlier-mentioned provision of the ANSI SQL-92 specification because the LIKE predicate's function, by definition, is to facilitate pattern searches rather than straightforward string equality testing.
Watch: zzz-13 zzz
You may make a brand-new SQL view and map it to an EF object. When using EF, the view may include the length of the field(s) you know you want to match against in the select using DataLength, and you could filter on that in your where clause. As an alternative, you might develop a SQL Stored Procedure that does the appropriate comparison using LIKE without the use of wildcards, map it back to your code, and then call it from your Where statement in EF.
Create View MyCustomView AS SELECT [column1,column2,etc], DATALENGTH(FirstName) AS FirstNameLength FROM Person GO
Your EF would then be:
Persons.Where(p => p.FirstNameLength == "Johanne".Length && p.FirstName == "Johanne");
using a stored procedure
CREATE PROCEDURE [dbo].[GetPersons] firstName int = null AS BEGIN SET NOCOUNT ON; select [your fields here] from persons where FirstName like @firstName END
Make sure your mapping is accurate in C#, then
Directly modifying the sql in the DbContext prior to execution is also supported by EF6. You could create some custom code that, in certain situations, substitutes the = with like, but it might be simpler to try the suggestions above first.