Entity Framework 6 - mapping keys of different types

I came across an interesting problem the other day.  I was creating and entity model over an existing database.  In the database the Primary Key on one table in the relationship is a Decimal and the related Foreign Key is an Int with no enforced referential integrity on the database.

My first attempt at mapping my entities to the database was not successful and I ended up with the following Exception : "The specified cast from a materialized 'System.Decimal' type to the 'System.Int32' type is not valid."


Here is what my original entities and mapping files looked like:

public class JobMap : EntityTypeCategoryuration
{
    public JobMap()
    {
        // Primary Key
        this.HasKey(p => p.JobId);

        // Properties

        // table and column mappings
        this.ToTable("job");
        this.Property(p => p.JobId).HasColumnName("job_id");
        this.Property(p => p.JobCategoryId).HasColumnName("job_category_id");
        this.Property(p => p.JobCode).HasColumnName("job_code");


        this.HasRequired(t => t.JobCategory) 
            .WithMany(t => t.Jobs)
            .HasForeignKey(d => new { d.JobCategoryId }); 
    }
}

public class Job
{
    public decimal JobId { get; set; }
    public int JobCategoryId { get; set; }
    public string JobCode { get; set; }
}

public class JobCategoryMap : EntityTypeCategoryuration
{
 public JobCategoryMap()
 {
  // Primary Key
  this.HasKey(s => s.JobCategoryId);

  // Properties
  // Table and Column mappings
  this.ToTable("job_category");

  this.Property(p => p.JobCategoryId).HasColumnName("job_category_id");
  this.Property(p => p.Name).HasColumnName("name");
  this.HasOptional(p => p.Jobs).WithRequired()
                    .Map(x => x.MapKey("job_category_id"));
 }
}

public class JobCategory
{
    public int JobCategoryId { get; set; }
    public int Name { get; set; }
    public virtual ICollection Jobs { get; set; }
}

It took a fair bit of bashing of the head to figure out how to get things to work, but in the end the solution turned out to be very simple. A small change to one of the mapping classes.

public class JobCategoryMap : EntityTypeCategoryuration
{
 public JobCategoryMap()
 {
  // Primary Key
  this.HasKey(s => s.JobCategoryId);

  // Properties
  // Table and Column mappings
  this.ToTable("job_category");

  this.Property(p => p.JobCategoryId).HasColumnName("job_category_id")
                    .HasColumnType("decimal");
  this.Property(p => p.Name).HasColumnName("name");
  this.HasOptional(p => p.Jobs).WithRequired()
                    .Map(x => x.MapKey("job_category_id"));
 }
}

Notice the addition of the

.HasColumnType("decimal")

. It took me a while to figure this out so I thought I'd blog about it in the off chance someone else is looking to solve the same problem.