MVC4 Navigation Properties with composite keys

Question

I have created an MVC4 web application using EF-database-first. The tables have composite keys [ID, Name, EffDate], and no foreign keys defined in the database: For example, Department partial class:

[MetadataType(typeof(DepartmentMetadata))]
public partial class Department
{
    public int DeptID { get; set; }
    public string DeptName { get; set; }
    public System.DateTime EffDate { get; set; }
    public string Status { get; set; }
    public string RevenueAccount { get; set; }
}

Department metadata class:

public class DepartmentMetadata
{
    [Required]
    public int DeptID { get; set; }

    [Required]
    [Display(Name = "Department Name")]
    public string DeptName { get; set; }

    [Required]
    [Display(Name = "Effective Date")]
    [DisplayFormat(DataFormatString = "{0:dd/MM/yyyy}", NullDisplayText = "--", ConvertEmptyStringToNull = true)]
    public System.DateTime EffDate { get; set; }

    [Required]
    public string Status { get; set; }

    [Display(Name = "Revenue Account")]
    [StringLength(10)]
    public string RevenueAccount { get; set; }
}

The Allocation table, that refers to the Department table. It also has a composite key [DeptID, ProjectID, BillableUnitID, EffDate]. If I could, I would declare the DeptID field a foreign key ...but I don't control the database, and more importantly I believe T-SQL won't allow foreign keys to part of a composite key:

[MetadataType(typeof(AllocationMetadata))]
public partial class Allocation
{
    public int DeptID { get; set; }
    public int ProjectID { get; set; }
    public int BillableUnitID { get; set; }
    public System.DateTime EffDate { get; set; }
    public string Status { get; set; }
    public decimal Allocation1 { get; set; }
}

This works, but I get a column of DeptID numbers. What I would like to have is a column of department names.

A previous question directed me to virtual navigation properties, so I added them:

[MetadataType(typeof(AllocationMetadata))]
public partial class Allocation
{
    [ForeignKey("Department")]
    public int DeptID { get; set; }
    public int ProjectID { get; set; }
    public int BillableUnitID { get; set; }
    public System.DateTime EffDate { get; set; }
    public string Status { get; set; }
    public decimal Allocation1 { get; set; }

    public virtual Department Department { get; set; } /* navigation property */
}

The code in the AllocationController for Index is: public ActionResult Index() { return View(db.Allocation.Include(a => a.Department).ToList()); }

When I click on the link to Allocation Index view, I get this error message (after I Stop Debugging):

Server Error in '/' Application.

A specified Include path is not valid. The EntityType 'KC_BillableUnit_TESTModel.Allocation' does not declare a navigation property with the name 'Department'.

Stack trace [InvalidOperationException: A specified Include path is not valid. The EntityType 'KC_BillableUnit_TESTModel.Allocation' does not declare a navigation property with the name 'Department'.]
System.Data.Objects.Internal.ObjectFullSpanRewriter.ConvertSpanPath(SpanPathInfo parentInfo, List`1 navPropNames, Int32 pos) +8355128
System.Data.Objects.Internal.ObjectFullSpanRewriter..ctor(DbCommandTree tree, DbExpression toRewrite, Span span) +256 ....continues....

I've tried various combinations of annotations, but all result in the same error.

How can I get my Allocation list to show Department names instead of DeptID numbers?

Answer 1

Off course you can! I think the problem is that your declared the navigation property just in one side (Allocation), however you must declare that at both sides (Department too).

The following must resolve your problem:

[MetadataType(typeof(DepartmentMetadata))]
public partial class Department
{
    public Department()
    {
        this.Allocations = new HashSet<Allocation>();
    }

    // properties ...

    public virtual ICollection<Allocation> Allocations { get; set; }
}