实体框架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

请注意，这里的用户名为Caps it在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抛出空值，当我更改值在注册表格中的用户名列中的 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 ，因为你加载的用户实体与您的注册实体不相关。因此，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屋！