MVC4 how to load related data without Navigation P

Question

I an fairly new to MVC, and have created an MVC4 application using EF-database-first. The database does not contain foreign key definitions and I can't add them (I don't own the database). Here are two example classes from the database:

public partial class Allocation
{
    public int AllocID { get; set; }
    public int DeptID { get; set; }
    public decimal AllocationPercent { get; set; }
}

public partial class Department
{
    public int DeptID { get; set; }
    public string DeptName { get; set; }
    public string Account { get; set; }
}

The default Allocation Index page shows the department ID. I want to show the department name instead. How can I do this without navigation properties?

I tried

public class AllocationController : Controller
{
    private Entities db = new Entities();

    //
    // GET: /Allocation/

    public ActionResult Index()
    {
        return View(db.Allocation.Include(d => d.DeptID).ToList());
    }
...

but this gives an error ("A specified Include path is not valid. The EntityType 'TESTModel.Allocation' does not declare a navigation property with the name 'DeptID'.")...

I'm not sure how to code eager-loading or explicit-loading without navigation properties either, which prompted this question. Efficiency-wise, I don't believe it matters which way I load the related information, so any help in any direction would be appreciated.

Answer 1

The database does not have to have definitions, as long as the fields are there and the entities have been placed in the database with referential integrity in mind. All you need to do is let entity framework know about the relationship. This is done with the virtual keyword to create "Navigational Properties".

public partial class Allocation
{
 public int AllocID { get; set; }
 public int DeptID { get; set; }
 public decimal AllocationPercent { get; set; }
 public virtual Department Department { get; set; } /* this is your nav property */
}

public partial class Department
{
 public int DeptID { get; set; }
 public string DeptName { get; set; }
 public string Account { get; set; }
}

Now you can do:

db.Allocation.Include(a => a.Department).ToList()

There may be an error which requires you to use a foreign key definition (although I do not think so). If this is the case, you will need to decorate your navigation property like this

[ForeignKey("DeptID")]
public virtual Department Department { get; set; }

You may also try it this way:

 public int AllocID { get; set; }
 [ForeignKey("Department")]
 public int DeptID { get; set; }
 public decimal AllocationPercent { get; set; }
 public virtual Department Department { get; set; }

Answer 2

With navigation properties, Travis J's answer is what you need. If you don't want to use navigation properties, assuming your DB context has a set called Departments, you could do smth like this:

var deptId = db.Allocation.DeptID;
var departments = db.Departments.Where(p => p.DeptID == deptId);
return View(departments.ToList());