Optimizing multiple LINQ to Entity Framework queries

I have 2 Entity Framework entity sets with many-to-many relationship (compromised of 3 DB tables). For the sake of the example let's say I have a Worker entity and a Company entity, where each worker can work at multiple companies and each company can have many workers.

I want to retrieve for each work开发者_运维知识库er all the companies that he/she works for. The straightforward way would be to create a query for each worker that will fetch the companies using a join between the association table and the companies table, But this results in a round trip to the DB for each worker.

I am sure this can be done in a better more optimized way. Any help will be appreciated.

Thank you.

If your joining table doesn't have any extra info (just the Id's of Worker and Company), you should have only two entities in your model: Worker and Company. If EF 4 the entity graph is eager loaded by default, so unless you enable LazyLoading by doing (context.ContextOptions.LazyLoadingEnabled = true;), you get your company lists whenever your query for the workers:

var workers = context.Workers.ToList();
// Companies already loaded - do something with them
var companiesForWorker0 = workers[0].Companies;  // Don't forget to check 
...                                              // for null in real code

You can also directly tell EF to eager load the companies when querying for workers (that would be necessary if LazyLoading is enabled):

var workers = context.Workers.Include("Companies").ToList();

Here is what I do in Linq2SQL and might work for you.

1. Do query #1.
2. Collect all the worker 'ids' in a list.
3. Use this list to pass to the secondary query (in other words where list.Contains(item)).

Now it should take only 2 queries.

You could probably combine them both into a single query with a bit more effort if needed.