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