Optimizing multiple LINQ to Entity Framework queries

Question

Hello,

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 worker 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.

Answer 1

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.

Answer 2

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();