Entity Framework Code First Case Sensitivity on string PK/FK Relationships

Question

I have a fairly simple composite one to many relationship defined using POCO/Fluent API, one column of which is a string.

I've discovered that the data in this column in our database is inconsistent in terms of case ie 'abb', 'ABB' - this is our main ERP system and is fed by a variety of sources which are mainly beyond our control.

This is leading to problems using EF code first when joining to related tables as the join is silently ignored by EF when the case of PK/FK is different even though SQL Profiler shows the correct SQL being executed and results returned.

I'm using WCF so have lazy loading and proxy creation turned off and am eager loading required related entities using Include. eg.

var member = context.Member.Include(m => m.Audits).First(m => m.Id == id); 

Are there any solutions to this outside of amending the database schema?

Answer 1

EF Insensitive join comparison

Hi I'm having the same problem (although not wit code first, but with a generated model)

The cause is that EF makes a case-sensitive comparison of the key fields, and it doesn'n find the related objects.

I'm guessing the problem lies in the "EDM Relationship Manager" and maybe there's a possibility of overriding this behavior.

I found a simple workaround for this: lower casing the related properties:

    [EdmScalarPropertyAttribute(EntityKeyProperty=true, IsNullable=false)]
    [DataMemberAttribute()]
    public global::System.String id
    {
        get
        {
            return _id.ToLower(); // **<- here**
        }
        set
        {
            if (_id != value)
            {
                OnidChanging(value);
                ReportPropertyChanging("id");
                _id = StructuralObject.SetValidValue(value, false);
                ReportPropertyChanged("id");
                OnidChanged();
            }
        }
    }
    private global::System.String _id;
    partial void OnidChanging(global::System.String value);
    partial void OnidChanged();

It actually works, but, of course, it's a lame workoround. I'm sticking to it for a while util I (or somebody) comes out with a better solution.

Good Luck!