实体框架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中，现在我执行此code。

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

这只是打破了空指针异常用户1抛出空，当我更改注册用户名表列的值，以 代替的 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.

这<一href="http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/af3359a9-99bb-4e02-94dd-5b9a6a9cf0db">Link关于有点类似谈判

这<一href="http://stackoverflow.com/questions/7421919/entity-framework-$c$c-first-case-sensitivity-on-string-pk-fk-relationships">Link另一个类似的问题

请分享你的答案为什么这种行为，我的数据库的排序规则是大小写insentivity。你有没有遇到类似的？

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不会修复了用户1 属性，它会保持为空。访问名字在这种情况下会抛出的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屋！