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!

Answer 2

I came up with a workaround that manually "stitches up" the association after the context has retrieved the appropriate rows from the database. Translated to your problem it would be along these lines:

//Your original query
var members = context.Member.Include(m => m.Audits).First(m => m.Id == id); 

//The "stitch up" code that should probably be moved to a method of the data context.
var membersWithoutAudits = context.Members.Local.Where(m => !m.Audits.Any()).ToList();
foreach (var nextMember in membersWithoutAudits) {
  //Now we can populate the association using whatever logic we like
  nextMember.Audits = context.Audits.Local.Where(a => a.MemberId.ToLower() == nextMember.Id.ToLower()).ToList();
}

Notice how we use the context.[DbSet].Local property to ensure that we do all the "stitch up" in memory without making any further SQL calls. I also fetch the members without audits as a performance optimization so we are not re-doing the work of EF's association (in the cases where it did work). But you could just as easily remap every "member" instance.