Entity Framework nvarchar Case Sensitive Foreign Key

Question

I have a pretty simple table structure as shown below and the problem sounds strange to me. Although I decided to get around this, I would like to accept the opinion of experts.

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 

Note that Case of User is here Caps, it is valid in SQL, it accepts.

Now for the interesting part. I created EDMX, .Net 4.0 and now I am executing 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 a Null error. Pointer User1 Null throws when I change the value of the ValueName Column column in the registration table a instead of A.

This link talks about several similar

This link is another similar issue.

Please share your answers why this behavior, Collation of my database is random. Have you encountered this?

Answer 1

The problem is that your database is not case-sensitive, but CLR (.NET) does not exist and, unlike a database, it cannot be switched to case-insensitive mode on a global scale - you must do this for comparison.

When you call item.User1.LastName EF starts lazy loading - an additional request is made in the database to load the associated user, but when the user materializes, EF starts fixing and checking its relational model, and here there is a problem - it compares strings with sensitivity case-sensitive, therefore, according to this parameter, a not equal to a , and because of this, your loaded User object is not a relation of your Registration object. As a result, EF will not fix the User1 property, and it will remain null. Access to LastName in this case will throw a NullReferenceException .

There are only two solutions:

• Correct your database and make sure that the difference in this case will no longer appear in your data.
• If you are at the beginning of the project or have full control over the redesign of the database. NVarChar primary keys and foreign keys are poor database design.

If none of these options apply to you, you should avoid using EF with such a database.