Query joined tables c# code first

Question

I have these tables i have made in c# using code first approach.

Employee class:

public int id { get; set; }
public string name { get; set; }

Department class:

public int id { get; set; }
public string deptName { get; set; }
public IQueryable<Employee> { get; set; }

This generates a DepartmentID in my Employee table in my sql database. I cannot however access this field in c# as DepartmentID is not a field in the employee class/model.

My question is how do i access this variable. I wish to do some various joins etc but am struggling with this.

Answer 1

You can certainly expose the foreign key, but it is not necessarily needed. The beauty of EF is you don't need joins.

First I would clean up your classes:

public class Employee
{
    public int ID { get; set; }
    public string Name { get; set; }

    // Exposed FK. By convention, EF know this is a FK.
    // EF will add one if you omit it.
    public int DepartmentID { get; set; }  
    // Navigation properties are how you access the related (joined) data
    public virtual Department Department { get; set; }  
}

public class Department
{
    public int ID { get; set; }
    public string Name { get; set; }

    public virtual ICollection<Employee> Employees { get; set; }
}

Now you can query your data easily:

var employeeWithDepartment = context.Employee
      .Include(e => e.Department)
      .FirstOrDefault(e => e.ID = 123);

var employeeName = employeeWithDepartment.Name;
var departmentName = employeeWithDepartment.Department.Name;
... etc.

var departmentWithListOfEmployees = context.Departments
     .Include(d => d.Employees)
     .Where(d => d.Name == "Accounting")
     .ToList();

... build table or something
foreach (var employee in departmentWithListOfEmployees.Employees)
{
     <tr><td>@employee.ID</td><td>@employee.Name</td>
}
... close table