11

A couple of years ago the following question was asked: Entity Framework nvarchar Case Sensitivity on Foreign key.

In short the answer was: EF uses the CLR to compare the keys of lazily loaded associations and always does that in a case sensitive manner, even though the database is set to a case insensitive collation.

Unfortunately, the project I'm currently involved with heavily uses VARCHAR primary key columns. The database collation is case insensitive.

Fixing the database design is really not an option, besides perhaps setting a CS collation on the primary key columns (but that would potentially break client applications).

So my question is two-fold:

  1. Does Entity Framework nowadays provide a directive or a setting of some kind, to instruct it to do case insensitive comparisons?
  2. If not, can a trigger be used to automatically change the foreign key to match the casing of the primary key? Or can you think of any other workarounds?

BTW: SQL Server 2008 R2 and Entity Framework version 6.

Community
  • 1
  • 1
AroglDarthu
  • 1,021
  • 8
  • 17
  • Thanks for asking the question, I run into this issue every few years as I migrate existing systems to EF. There's an issue on Github that I just commented on: https://github.com/aspnet/EntityFramework/issues/673 – StormRider01 Oct 07 '15 at 19:05

2 Answers2

1

without much analysis, here's a list of thing you can try:

  1. Write stored procedures and use the LOWER() Function, here's a link: http://www.w3schools.com/sql/sql_func_lcase.asp
  2. Use Model-first to map your database
  3. Get both Entities by 'ModelContext'.'Entity'.Find('PrimaryKey') and make the "association" using toLowerCase() Method in .NET
  4. Make a View in SQL Server if you are trying only to get data without altering registers
Christian Cuadros Betancur
  • 76
  • 9
  • First suggestion won't help. The database is already case insensitive. It is Entity Framework that does not respect the database collation. The correct records are retrieved from the database, but Entity Framework will drop records when it populates its entities. Dropping occurs because EF uses CLR string comparison on the keys, which is case sensitive by default. – AroglDarthu Sep 30 '15 at 13:16
  • Second suggestion would be nice. Unfortunately we cannot remodel the database. So making a Model-First representation would result in exactly the same model, having VARCHAR columns as primary keys. – AroglDarthu Sep 30 '15 at 13:19
  • Your third suggestion kind of defeats the whole purpose of using EF in the first place. It would be easier to just drop EF and retrieve/map all entities ourselves based on Stored Procedures. – AroglDarthu Sep 30 '15 at 13:21
  • In addition to your first suggestion: If you mean I should also retrieve all entities via this stored procedure than it might work. But then it is more likely to just drop EF and go the old-fashioned way, as it would require SPs for almost every table ;-( Same goes for using Views. – AroglDarthu Sep 30 '15 at 13:25
1

Now in EF Core it is possible to circumvent this problem by using a ValueComparer. See the following github issue comment: https://github.com/dotnet/EntityFramework.Docs/issues/2979#issuecomment-753344003

Docs: https://learn.microsoft.com/en-us/ef/core/modeling/value-comparers?tabs=ef5#key-comparers

AroglDarthu
  • 1,021
  • 8
  • 17