Access Object through foreign key c#

Question

I have the following code and would like to know if there is a way to refactor in order to remove duplicated logic.

This results current user with eager loading.

  var currentEmployee = RosterContext.Employees
                    .Where(e => e.User.Id == id)
                    .Include(e => e.Job.Department).FirstOrDefault();

.

var job = RosterContext.Employees.Where(e=>e.Job.Department.Id == currentEmployee.Job.DepartmentId).ToList();

I created another same context which compares the first line of code to result all employee names who work in same department. My question is, as I am using two linq expression that uses the same context (Employees) am i able to combine both linq queries into one? It may become a long linq expression but it should serve on getting the current user object followed by comparing user object to get all employees that share the same department id?

Answer 1

It makes sense to try an ORM framework, such as Entity Framework or NHibernate. ORM framewok will model database FK relationship as a scalar property on one side and vector property (collection) on the other side of the relationship. For instance Department would have a collection property Jobs, and a Job entity would have a scalar Department property. DB queries with joins on FK become just dependency property navigation, for example - to access the list of employees in current department you would just return something like employee.Department.Employees - that is, assuming your entities are all loaded (which is rather simple to achieve in EF, using include statement)

Answer 2

EF Code First supports relationships out of the box. You can either use the conventions or explicitly specify the relationship (for example, if the foreign key property is named something weird). See here for example: https://msdn.microsoft.com/en-us/data/hh134698.aspx

When you've configured your models right, you should be able to access department like so:

var currentUser = _unitOfWork.Employee.GetEmployeeByID(loggedInUser.GetUser(user).Id);
var job = currentUser.Job;
var department = job.Department;

// or

var department = _unitOfWork.Employee.GetEmployeeByID(loggedInUser.GetUser(user).Id).Job.Department;

To show all employees that work in the same department:

var coworkers = department.Jobs.SelectMany(j => j.Employees);

Update

To use eager loading with a single repository class (you shouldn't need multiple repository classes in this instance, and therefore don't need to use Unit of Work):

public class EmployeeRepository {
    private readonly MyContext _context = new MyContext(); // Or whatever...

    public IList<Employee> GetCoworkers(int userId) {
        var currentEmployee = _context.Employees
            .Where(e => e.UserId == userId)
            .Include(e => e.Job.Department)     // Use eager loading; this will fetch Job and Department rows for this user
            .FirstOrDefault();

        var department = currentEmployee.Job.Department;
        var coworkers = department.Jobs.SelectMany(j => j.Employees);
        return coworkers;
    }
}

And call it like so...

var repo = new EmployeeRepository();
var coworkers = repo.GetCoworkers(loggedInUser.GetUser(user).Id);

You probably would be able to make the repository query more efficient by selecting the job and department of the current user (like I've done) and then the related jobs and employees when coming back the other way. I'll leave that up to you.

Answer 3

In Entity Framework you have the using clause to attach children. So for example in pure EF you could do:

var department = context.Department.Include(d => d.Jobs).First(d => d.DepartmentId == departmentId);

https://msdn.microsoft.com/en-us/library/gg671236%28v=vs.103%29.aspx#Anchor_1

With a repository, you may need to do something like this:

EF Including Other Entities (Generic Repository pattern)