views:

43

answers:

2

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

+1  A: 

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);
}
Håvard S
thx.. but o is not being recognized in VS .... lemme think on these lines;
effkay
Just updated it after a quick test, should work now.
Håvard S
Thanks.... for kind effort; Yes, it should work, but now there is another issue; EF is hidding the FK column in userOffices, is exposing it as collection. I am seeing what can be done.
effkay
+2  A: 

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.

Håvard S
thanks for the great help... worked as expected!
effkay