MVC EF - Inner Join

Question

I checked the questions that may already have my answer but it seems that none of them addresses the issue I have:

var semesters = db.Semesters.Where(e => e.ID == semester).ToList();
var semestersbyfaculty = db.SemestersByFaculty.Where(e => e.FacultyID == id).ToList();

How do I inner join these two queries like if I do the following in SQL:

SELECT 
   Fac.*, Sem.SemesterText
FROM 
   SemestersByFaculty AS Fac
INNER JOIN
   Semesters AS Sem
ON
   Fac.SemesterID = Sem.ID
WHERE id
   Fac.FacultyID = id

Answer 1

Inner Join

The following example shows a simple inner equijoin.

var query = from fac in db.SemesterByFaculty
            join sem in db.Semester on fac.SemesterID equals sem.ID
            where fac.FacultyID == id
            select new { Faculty = fac, SemesterText = sem.SemesterText };

For more information, see How to: Perform Inner Joins (C# Programming Guide).

UPDATE:

from comments

Models

class MyModel {
    public MitModel.SemestersByFacul‌​ty Faculty{ get; set; }
    public string SemesterText { get; set; }
}

class MyViewModel {
    public List<MyModel> SemesterFaculties { get; set; }
}

Action:

public ActionResult SomeAction(string id) {

    var query = from fac in db.SemesterByFaculty
                join sem in db.Semester on fac.SemesterID equals sem.ID
                where fac.FacultyID == id
                select new MyModel{ Faculty = fac, SemesterTest = sem.SemesterTest };

    var viewModel = new MyViewModel { SemesterFaculties = query.ToList() };
    return View(viewModel);
}

View

@Model MyViewModel

Answer 2

Suppose you have following fields in SemestersByFaculty

class SemestersByFaculty
{
    string FacultyName;
    int FacultyID;
    int SemesterID;
}

Note : If you have more fields in SemestersByFaculty class then you can list them in new{} in query below:

var query = semesters.Join(semestersbyfaculty,
                                sem => sem.ID,
                                fac => fac.SemesterID,
                                (sem, fac) =>
                                    new { facName = fac.FacultyName, facId = fac.FacultyID,semText = sem.SemesterText }).Where(e=> e.fac.FacultyId = id);

Now , what you have got yourself is an enumerable. You can iterate over it and retrieve the values. It will be like:

foreach (var obj in query){

   Console.writeln("{0}-{1}-{2}",obj.facName,obj.facId,obj.semText);
}