Entity Framework nvarchar Case Sensitivity on Fore

问题:

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 ?

回答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.

回答2:

@Ladislav has described the issue perfectly well but there is another approach that some people might be able to use. Individual columns in your tables can be made case sensitive so they behave in the same way as .Net;

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

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

Note the collate added to the column definitions. Now (assuming you have a foreign key defined) the database will enforce case consistency just for the UserName between both tables. You might need to prepare your data beforehand by setting all to upper/lower, and will probably need to drop and recreate indexes, constraints etc if using alter table.

This is much lower impact than trying to change collation at the database level but still requires some thought and testing because depending on what other collations are in effect you might run into collation conflicts that need to be handled.

There are many collations available so one of the other case sensitive options might be more suitable depending on your circumstances.