EF: why Include method is an anti-pattern IMHO? Part 5: many to many relationships

I recently blogged to explain why I found that Include method is an anti-pattern IMHO.

EF: Why Include method is an anti-pattern IMHO?

EF: Why Include method is an anti-pattern IMHO even with many to one navigation properties? 2/3

EF: Why Include method is an anti-pattern IMHO? 3/3

EF- Why Include method is an anti-pattern IMHO- Conclusion

But I didn’t show how to do it with many to many relationships.

I will do on this post.

This is my model:


I have 1000 products, 100 categories and 4497 associations in my DB.

I use a local DB what is better for EF Include. // Don’t forget it, it would be worse for EF Include method with SQL Azure or any far DB… Moreover, the bigger entities are, the worse it is for the EF Include. In my case, entities are really very very short

The “official” way to get the 100 first Products with their Categories is the following:

var products = context.Products.Include("Categories").Take(100).ToList();


Now my way is the following:

object categoriesLock = new object();
object productsCategoriesLock = new object();
Task productsCategoriesTask = new Task(() =>
        using (var productsCategoriesContext = new Many2ManyIncludeEntities())
            var productsCategoriesIds = productsCategoriesContext.Products.Take(100).SelectMany(p => p.Categories.Select(c => new { p.ProductId, c.CategoryId })).ToList();
            lock (productsCategoriesLock)
                var loadedProducts = context.ObjectStateManager.GetObjectStateEntries(EntityState.Unchanged).Select(ose => ose.Entity).OfType<Product>().ToList();
                var loadedCategories = context.ObjectStateManager.GetObjectStateEntries(EntityState.Unchanged).Select(ose => ose.Entity).OfType<Category>().ToList();
                foreach (var pc in productsCategoriesIds)
                    var product = loadedProducts.FirstOrDefault(p => p.ProductId == pc.ProductId);
                    var category = loadedCategories.FirstOrDefault(c => c.CategoryId == pc.CategoryId);
                    if (product != null && category != null)
Task categoriesTask = new Task(() =>
        lock (productsCategoriesLock)
            using (var categoriesContext = new Many2ManyIncludeEntities())
                categoriesContext.Categories.MergeOption = MergeOption.NoTracking;
                var categories = categoriesContext.Categories.Where(c => c.Products.Any(p => categoriesContext.Products.Take(100).Contains(p))).ToList();
                lock (categoriesLock)
                    foreach (var c in categories)
lock (categoriesLock)
    var products = context.Products.Take(100).ToList();
Task.WaitAll(categoriesTask, productsCategoriesTask);

So my code is really more complex but what about performance?


Even with best condition for EF way (local DB, very short entities), my way is really better: for the first execution, EF Include executes it on 835 ms when mine is 130 (6.42 faster). For the second an other ones, EF Include executes it on 342 ms vs 72 for mine (4.75 faster).

Now you have the choice between performance vs simplicity.

This entry was posted in 7671, 7674. Bookmark the permalink.

3 Responses to EF: why Include method is an anti-pattern IMHO? Part 5: many to many relationships

  1. Mickaël says:

    Please explain in a more detailled way how your code is working 🙂

  2. Fred says:

    Thanks !!

  3. Matthieu MEZIL says:

    @Mickaël : I want to execute my three queries in parallel. That’s why I use tasks and because ObjectContext is not Thread safe, I have to wait for products query before adding categories in the context and then, I have to wait for products and orders for creating associations between them. That’s why I use locks.

    @Fred: you’re welcome

Leave a Reply

Your email address will not be published. Required fields are marked *