I have a table named
[User]. Below is a simple query to find one user from the table:
SELECT [Username] ,[FirstName] ,[LastName] ,[EmailAddress] ,[IsStaff] ,[ExternalID] ,[LastLogin] FROM [dbo].[User] WHERE [Username] = 'pskalhaq' -- 1 --WHERE [Username] = 'pskalhaq' -- 2
When I run this query I get a single record back, which is what I expect. When I comment out the
WHERE clause I've labelled as "1" and uncomment the clause labelled as "2", I get no results. This is bizarre because the values between the inverted commas are identical.
I discovered this problem because a web application I've written which uses Entity Framework to get a record from another table,
[Appointment], was throwing an error due to there not being a related record in the
[User] table, even though the
[Appointment] table contains a non-nullable foreign key joining to the
[User] table. This seemed impossible. I ran the equivalent query in SSMS (2892 is the ID of the appointment record that was giving me trouble):
SELECT a.[ID] AS [AppointmentID] ,a.[StudentUsername] -- This is the foreign key in the Appointment table ,u.[Username] -- This is the primary key in the User table FROM [dbo].[Appointment] a JOIN [dbo].[User] u ON u.[Username] = a.[StudentUsername] WHERE a.[ID] = 2892
This returned a result, as expected. So the
[Appointment] record DOES have a linked
[User] record. At this point I decided this must be a bug in Entity Framework (spoiler: It seems not to be, that's why I'm posting here and not on Stack Overflow!).
But bizarrely, if I copy the value from either the
[StudentUsername] or the
[Username] column in the results of the query and paste the value into the
WHERE clause in my first query above, I get no results from the
[User] table. The same is true if I manually type the username into the query.
At this point I thought I was losing my mind, so I ran a query on the
[User] table to return all records sorted by username:
SELECT [Username] FROM [dbo].[User] ORDER BY [Username]
I scrolled down to where my seemingly-missing user should be and lo and behold, the record was right there! So I copied the value from the
[Username] column in the results of that query, pasted it into my first query, and suddenly that query returned the single user result, as expected.
What is going on here? Is this some kind of bug in SQL Server? I say SQL Server and not SSMS because there seems a strong possibility that this same issue is what's causing Entity Framework to also say the Appointment has no associated User. How can the same query return no results unless I happen to copy the value for the
WHERE clause from the results of another query on the same table?!
P.S. In case it's relevant, both the
[User].[Username] columns are
NVARCHAR(200). The database is running on SQL Server 2016 SP2.