Pulling related data in MVC 3 from separate database

Question

Being new to MVC and the Entity Framework I've had quite a few problems performing complex data queries. I'm using a Database First approach I need to understand the best way to pull related data from a separate database. I have connected to the databases and created the edmx files as well as the models from the existing databases. I will simplify the problem example: If I have one table with employee information:

Employee_Information(EmployeeID, FullName, Address, Phone)

Then I have a second (in another database)

Employee_Achievements(EmployeeID, Achievement, DateAchieved)

How can I tie these two together to pull the employee's information (name, address, etc.) in a view? My initial thought was do a join and then pass that to the view:

var employee = from emp in db.EmployeeAchievements
join empInfo in emp_db.EmployeeInformation
emp.EmployeeID == empInfo.EmployeeID

The problem I have with this is that I'm already using a ViewModel with multiple models being passed to the view. I need the ability to tie the information in for several employees at a time with something like this in the view:

@for (int i = 0; i < model.EmployeeAchievements.Count; i++)
{
    <tr valign="top">
        <td>
            <div class="editor-field">
                @Html.EditorFor(model => model.EmployeeAchievements[i].EmployeeID)
                @Html.ValidationMessageFor(model => model.EmployeeAchievements[i].EmployeeID)
            </div>
        </td>
// Display employee name next

Possible use of the ViewBag? Or is there a way that I can associate the foreign key across databases? Keep in mind this is Database First so the edmx and model files are generated. I haven't been able to find any good examples of what I'm trying to do here. I would appreciate any help and can provide more detail if needed.

Answer 1

You can build a class, that have got all the fields you need: id, name, achievement (or list of them if you have got one-to-many), date and so on - everything you need. So, for example, this class is named EmployeeAchievement. Then, you just modify your current ViewModel to have field of a type List<EmployeeAchievement> named EmplAch. Then, you have to build your view model:

YourViewModel model = new ViewModel();
model.EmplAch = (from emp in db.EmployeeAchievements
                 join empInfo in emp_db.EmployeeInformation
                 emp.EmployeeID == empInfo.EmployeeID
                 select new EmployeeAchievement 
                 {
                     EmployeeID = emp.EmployeeID,
                     AchievementId = emp.id,
                     Date = emp.DateAchieved,
                     Achievement = emp.Achievement
                 });

So, in your view you can use:

@for (int i = 0; i < model.EmplAch.Count; i++)
{
    <tr valign="top">
        <td>
            <div class="editor-field">
                @Html.EditorFor(model => model.EmplAch[i].EmployeeID)
                @Html.ValidationMessageFor(model => model.EmplAch[i].EmployeeID)
            </div>
        </td>
// Display employee name next
}