Entity Framework varchar foreign key case insensitive

Question

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.

Answer 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

Answer 2

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