Entity Framework nvarchar Case Sensitivity on Foreign key

Question

I have fairly simple table structure as below and issue sounds strange to me. Though I have chosen to work around it but would like to take experts opinion.

I have two Tables

Users
UserName nvarchar(250) Primary Key
FirstName nvarchar(50)
LastName  nvarchar(50)

Registrations
Id BigInt PrimaryKey
User nvarchar(250) - Foreign to Users Table
Date - DateTime

Data I have is as follows.
Users
UserName FirstName LastName
a        Small     A 
b        Small     B

Registrations
Id       User      Date
1        A         1/1/12
2        B         1/1/12

Please note Case of User here is Caps it is valid in SQL, it accepts.

Now the Fun Part. I generated the EDMX, .Net 4.0 and Now I execute this code.

 using (EFTestEntities context = new EFTestEntities())
            {
                var item = context.Registrations.Where(id => id.Id == 1).FirstOrDefault();
                Response.Write(item.User1.LastName);
            }

It Just Breaks with Null Pointer Exception User1 Throws Null, When I change the Value of UserName Column in Registrations table to a instead of A it works.

This Link talks about somewhat Similar

This Link another similar issue

Please share your answers why is this behaviour, Collation of my DB is case-insentivity. Have you faced similar ?

Answer 1

The problem here is that your database is case insensitive but CLR (.NET) is not and in contrast to database it cannot be switched to case insensitive mode globally - you must do it per comparison.

When you call item.User1.LastName EF will trigger lazy loading - additional query is executed in the database to load a related user but when the user is materialized EF will start fixing and validating its relational model and here comes the problem - it compares strings with case sensitivity so according to this setting a is not equal to A and because of that your loaded User entity is not relation of your Registration entity. As a result EF will not fix up User1 property and it will remain null. Accessing LastName in such case will throw NullReferenceException.

There are only two solutions:

• Fix your database and make sure that this case difference will not appear in your data again
• If you are at the beginning of the project or if you have full control over the database redesign it. NVarChar primary keys and foreign keys are bad database design.

If neither of those choices is applicable for you, you should avoid using EF with such database.