实体框架和字符串键之间的关联
我是实体框架和 ORM 的新手。 在我参与的项目中，我们有一个遗留数据库， 它的所有键都是字符串，不区分大小写。
我们正在转换为 MSSQL 并希望使用 EF 作为 ORM， 但遇到了问题。
下面是一个说明我们问题的示例：
TableA 有一个主字符串键， TableB 有对此主键的引用。
在 LINQ 中，我们这样写：
var result = from t in context.TableB select t.TableA;
foreach( var r in result )
Console.WriteLine( r.someFieldInTableA );
如果 TableA 包含一个主键“A”，并且 TableB 包含引用 TableA 但在引用字段“a”和“A”中具有不同大小写的两行。
在我们的项目中，我们希望两行都出现在结果中，但只有这一行 与匹配的案例将最终在那里。
使用 SQL Profiler，我注意到两行都被选中。
有没有办法告诉实体框架键不区分大小写？
编辑：
我们现在已经使用 NHibernate 对此进行了测试，并得出结论：NHibernate 使用不区分大小写的键。 所以 NHibernate 对我们来说可能是更好的选择。
但是我仍然有兴趣找出是否有任何方法可以改变实体框架的行为。
感谢您的回答！
问题是，如果我们现在将该约束添加到数据库中， 遗留应用程序可能会因其构建方式而停止工作。 如果可能的话，对我们来说最好的办法就是改变 EF 的行为。 我猜这是不可能的，但我正在尝试。
问候，
Fredrik
编辑：我为自己的问题添加答案的原因是我在成为注册用户之前添加了这个问题，当我注册我的帐户时，我无法添加评论或编辑我的邮政。 现在帐户已合并。
I am new to Entity Framework, and ORM's for that mather.
In the project that I'm involed in we have a legacy database,
with all its keys as strings, case-insensitive.
We are converting to MSSQL and want to use EF as ORM,
but have run in to a problem.
Here is an example that illustrates our problem:
TableA has a primary string key,
TableB has a reference to this primary key.
In LINQ we write something like:
var result = from t in context.TableB select t.TableA;
foreach( var r in result )
Console.WriteLine( r.someFieldInTableA );
if TableA contains a primary key that reads "A", and TableB contains two rows that references TableA but with different cases in the referenceing field, "a" and "A".
In our project we want both of the rows to endup in the result, but only the one
with the matching case will end up there.
Using the SQL Profiler, I have noticed that both of the rows are selected.
Is there a way to tell Entity Framework that the keys are case insensitive?
Edit:
We have now tested this with NHibernate and come to the conclution that NHibernate works with case-insensitive keys. So NHibernate might be a better choice for us.
I am however still interested in finding out if there is any way to change the behaviour of Entity Framework.
Thanks for your answer!
Problem is that if we add that constraint to the database now,
the legacy application might stop working because of how it is built.
Best for us would be, if possible, to change the behavior of EF.
I'm guessing it is not possible, but I'm giving it a shot.
Regards,
Fredrik
edit: The reason why I added an answer to my own question was that I added this question before I was a registerd user, and when I had registred my account I couldn't add comments or edit my post. Now the accounts are merged.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
我认为您需要更改 SQL Server 中的架构，而不是 EF 中的架构。 这篇文章的答案，关于如何使列区分大小写，看起来可以解决问题：T-SQL：如何创建区分大小写的唯一键？
I think you need to make the change to the schema in SQL Server, not in EF. This post's answer, on how to make a column case-sensitive, looks like it will do the trick: T-SQL: How do I create a unique key that is case sensitive?
我知道这不是一个完美的解决方案，但在 LINQ 中为什么不自己加入呢。 EF 不起作用，因为 .Designer.cs 文件在执行联接时返回 objA.Equals(objB)。 .Equals 区分大小写。
var 结果 = 来自 context.TableB 中的 t1
在 t1.someFieldInTableB.ToUpper() 上将 t2 连接到 context.TableA 等于 t2.someFieldInTableA.ToUpper();
我知道这有些骇人听闻，但 LINQ to Entities 仍处于起步阶段，并且设计的对象类是出于特定原因而设计的，这些原因无法处理此类设计中的异常情况。
另一种选择是您可以使用 T4 模板创建自己的代码生成器。 由于所有内容都是公共分部类，因此您可以创建一个导航属性，该属性实际上执行您正在寻找的不区分大小写的比较。
不过，为了如实回答您的问题，没有“开箱即用”的方法让 EF 使用不区分大小写的搜索进行导航。
I know this isn't a perfect solution, but in LINQ why not do the join yourself. EF doesn't work because the .Designer.cs file returns objA.Equals(objB) when doing the join. .Equals is case sensitive.
var result = from t1 in context.TableB
join t2 in context.TableA on t1.someFieldInTableB.ToUpper() equals t2.someFieldInTableA.ToUpper();
Hackish I know, but LINQ to Entities is still in its infancy and the object classes that are designed are designed for specific reasons that do not handle exceptional cases in a design such as this.
Another alternative is that you can create your own code generator using T4 templates. Since everything is a public partial class you can create a navigation property that actually does the case insensitive comparisson that you are looking for.
To answer your question truthfully though, there is no "out of the box" way to get EF to do a navigation using case insensitive searching.
我想出了一个解决方法，在上下文从数据库中检索行后，在内存中“缝合”基于字符串的关联（提示：使用 context.[EntityTypeCollection].Local 属性。您可以在https://stackoverflow.com/a/12557796/62278查看我的答案
I came up with a workaround that "stitches up" the string based association in memory after the context has retrieved the rows from the database (hint: making using of the context.[EntityTypeCollection].Local property. You can see my answer at https://stackoverflow.com/a/12557796/62278
好吧，如果重写 Equals 方法就不行了
EF 中生成的域类是部分否？ 因此，用您自己的实现替换这些类的默认 Equals 实现相当容易（这当然会使其不区分大小写）
顺便说一句：这项技术可以追溯到 .NET 1.0
以及所有这些 .NET 3.5/4.0、Linq 和 Lambda暴力，人们往往忘记基本知识
Well, not if you override the Equals method
The generated domain classes in EF are partial no? So it's fairly easy to replace the default Equals implementation of these classes by your own implementations (which of course would render it case insensitive )
BTW : a technique dat dates back from .NET 1.0
With all this .NET 3.5/4.0, Linq and Lambda violence, people tend to forget about the basics
作为实体框架的替代方案，您可以使用 LINQ to SQL，它可以很好地处理涉及区分大小写排序规则的关系。 尽管此 ORM 不提供 EF 或 NHibernate 的所有灵活性，但在许多情况下它已经足够了。
我最近在官方 Microsoft Entity Framework 论坛上发布了一个主题：
http://social. msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/d4aa6880-31b3-4ff2-b7f5-e2694d76772e
As an alternative to the Entity Framework, you can use LINQ to SQL, which works well with relations involving case sensitive collations. Although this ORM does not offer all the flexibility of EF or NHibernate, it can be sufficient in many cases.
I've recently posted a thread on the official Microsoft Entity Framework forum:
http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/d4aa6880-31b3-4ff2-b7f5-e2694d76772e