EntityFramework - LINQ Join help

Question

Hello,

I have following schema:

Clients - ClientId Users - UserId Offices - OfficeId

ClientOffices - ClientId, OfficeId UserOffices - UserId, OfficeId

Bold entities are EntityFramework Entities.

Now I need to write a function which accepts UserId and returns a list of Clients - who also belong to the offices to which user belongs.

E.g., Say ABC Inc. works with London Office, XYZ Inc. works with New York office. User "Yakoon" works only for Lond Office.

The linq statement when executed should only return ABC Inc. If "Yakoon" belongs to New York office as well, then it should return btoh ABC and XYZ Inc.

Thanks

Answer 1

How about this:

public IEnumerable<Client> GetClientsForUser(int id)
{
  return (from u in userOffices 
    where u.UserId == id
    join o in clientOffices on u.OfficeId equals o.OfficeId
    join c in clients on o.ClientId equals c.ClientId
    select c);
}

Answer 2

The entity framework equivalent approach would be something like:

public IEnumerable<Client> GetClientsForUser(int id)
{
  return (from u in objectContext.Users
            where u.UserId == id
          from o in u.Offices
          from c in o.Clients
          select c);

}

Make sure you have primary keys on your UserOffices and ClientOffices tables so EF will recognize them as junction tables.