Entity Framework and associations between string keys

Question

Hi, 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.

Answer 1

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: http://stackoverflow.com/questions/485359/t-sql-how-do-i-create-a-unique-key-that-is-case-sensitive

Answer 2

Hi Frank! 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.

Answer 3

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.

Answer 4

Hey buddy, I have a similar project with a table using string (nvarchar) key but the related objects in EF can not retrive the object. The global set retrive the object with the field to null. Any idea plz? (you have not this prob, what is you doing that im not)

Answer 5

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