实体框架 nvarchar 外键区分大小写 [英] Entity Framework nvarchar Case Sensitivity on Foreign key

问题描述

我有如下相当简单的表结构，问题对我来说听起来很奇怪.虽然我选择了解决它，但想听取专家的意见.

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.

我有两张桌子

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

请注意这里的用户案例是大写，它在 SQL 中是有效的，它接受.

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

现在是有趣的部分.我生成了 EDMX、.Net 4.0，现在我执行此代码.

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);
            }

它只是因空指针异常而中断 User1 抛出 Null，当我将注册表中 UserName 列的值更改为 a 而不是 A 时，它可以工作.

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.

这个链接 谈到有点相似

这个链接另一个类似问题

请分享您的答案，为什么会出现这种行为，我的数据库的整理不区分大小写.你遇到过类似的吗?

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

推荐答案

这里的问题是您的数据库不区分大小写，但 CLR (.NET) 不是，与数据库相比，它不能全局切换到不区分大小写模式 -你必须每次比较都这样做.

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.

当您调用 item.User1.LastName EF 将触发延迟加载 - 在数据库中执行附加查询以加载相关用户，但当用户具体化时，EF 将开始修复和验证其关系模型，问题来了 - 它比较区分大小写的字符串，因此根据此设置 a 不等于 A 并且因此您加载的 User 实体不是您的 Registration 实体的关系.因此，EF 不会修复 User1 属性，它将保持为空.在这种情况下访问 LastName 将抛出 NullReferenceException.

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.

只有两种解决方案:

• 修复您的数据库并确保这种大小写差异不会再次出现在您的数据中
• 如果您处于项目的开始阶段，或者您可以完全控制数据库，请重新设计它.NVarChar 主键和外键是糟糕的数据库设计.

• 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.

如果这两种选择都不适合您，您应该避免将 EF 用于此类数据库.

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

这篇关于实体框架 nvarchar 外键区分大小写的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！