Entity Framework 4.1 Fluent mapping Foreign Key and the Foreign object with a string key

Question

I am moving from an EDMX mapping to the EF 4.1 DbContext and Fluent mapping and I am wanting to map both a string foreign key and the foreign object using the fluent API. I have an Employee with an Optional Office. I would like both the OfficeId and the Office Object in the Employee class (This is all read only, and I do not need to be able to save these objects). Objects with int keys work fine, but I have tried several with string keys and get the same result - the OfficeId field populates, but the Office object comes back as null. Chekcking in SQL profiler the data is being queried, but the office object is not being populated.

public partial class Employee
{
    public int Id { get; set; }
    // snip irrelevant properties
    public Office Office { get; set; }  // this is (incorrectly) always null
    public string OfficeId { get; set; }
    public WorkGroup WorkGroup { get; set; } // this one with the int key is fine
    public int? WorkGroupId { get; set; }
    // snip more properties
}

public partial class Office
{
    public string Id { get; set; }
    public string Description { get; set; }
}

public partial class WorkGroup
{
    public int Id { get; set; }
    public string Code { get; set; }
}

After feedback from Ladislav below, I map it like this in the OnModelCreating

modelBuilder.Entity<Employee>().HasKey(d => d.Id).ToTable("Employee", "ExpertQuery");
modelBuilder.Entity<Office>().HasKey(d => d.Id).ToTable("Office", "ExpertQuery");
modelBuilder.Entity<WorkGroup>().HasKey(d => d.Id).ToTable("WorkGroup", "ExpertQuery");

modelBuilder.Entity<Employee>()
    .HasOptional(a => a.Office)
    .WithMany()
    .Map(x => x.MapKey("OfficeId")); // this one does not work
modelBuilder.Entity<Employee>()
            .HasOptional(e => e.WorkGroup)
            .WithMany()
            .HasForeignKey(e => e.WorkGroupId); // this one works fine

I assume there is some subtlety with string keys that I am missing ? I am querying it as follows :

var employees = expertEntities.Employees.Include("Office").Include("WorkGroup").Take(10).ToList();

If I omit the OfficeId field from Employee, and set up the mapping like this :

modelBuilder.Entity<Employee>()
    .HasOptional(e => e.BusinessEntity)
    .WithMany()
    .Map(x => x.MapKey("OfficeId"));

Then the office object is populated, but I need the OfficeId field in the Employee object.

Answer 1

Well, I found the issue - it's a data issue - the primary key string values were space padded and the foreign key values were not (!). Although SQL joins the tables correctly (ignoring the padding) and fetches the correct data, it appears that EF will not correlate it back into the the correct objects as .NET is fussier than SQL about trailing blanks.

Answer 2

Your customized mapping just conflicts because of the fact that you have already introduced a OfficeId property of string type. See what happens if you remove the OfficeId property from your Employee definition, or change it to int type.

Answer 3

That is not correct mapping. If you have FK property you cannot use Map and MapKey. That is for scenarios where you don't have that property. Try this:

modelBuilder.Entity<Employee>()
            .HasOptional(a => a.Office)
            .WithMany()
            .HasForeignKey(a => a.OfficeId);

Also first part of your mapping with mapping entities to table is most probably incorrect. Map is used for inheritance and entity splitting scenarios. You are looking for ToTable:

modelBuilder.Entity<Employee>().HasKey(d => d.Id).ToTable("ExpertQuery.Employee");

Also if your ExpertQuery is database schema and not part of table name it should look like:

modelBuilder.Entity<Employee>().HasKey(d => d.Id).ToTable("Employee", "ExpertQuery");