Entity Framework 4 Code First Many-to-Many relationship slow

Question

I have a Category object and a Product object. They have a many-to-many relationship, so the CategoryProduct table gets created when the database gets initialized. In the OnModelCreating method I have the following code to map the relationship

modelBuilder.Entity<Category>()
    .HasMany( c => c.Products )
    .WithMany( i => i.Categories )
    .Map( t => t.MapLeftKey( "CategoryId" )
    .MapRightKey( "ProductId" )
    .ToTable( "CategoryProducts" ));

The CategoryProducts table gets loaded correctly and everything's good. But when I am actually debugging the site, it takes an extremely long time to navigate to a category. For example, there is an 'Accessories' category that has over 1400 products. The code will grab everything for the category selected, but it will lazy load the products when it needs them. When it is lazy loading the products is when it takes the long time ( obviously ). I need to know how I can speed this up. Does anyone have any suggestions?

Thanks a lot

EDIT: Here are the Category and Product classes

public class Category : WebPage
    {
        private int _count = -1;
        public bool IsFeatured { get; set; }
        public virtual Category Parent { get; set; }

        public virtual List<Category> Children { get; set; }
        public virtual List<Product> Products { get; set; }    
        public virtual List<Discount> Discounts { get; set; }
}

public class Product : WebPage
    {
        public string Sku { get; set; }
        public string Details { get; set; }
        public string AdditionalDetails { get; set; }    

        public virtual List<Category> Categories { get; set; }
        public virtual Brand Brand { get; set; }
}

EDIT: Code doing the query

public virtual IEnumerable<TEntity> Get(
            Expression<Func<TEntity, bool>> filter = null,
            Func<IQueryable<TEntity>, IOrderedQueryable<TEntity>> orderBy = null,
            string includeProperties = "", int? limit = null)
        {
            IQueryable<TEntity> query = dbSet;

            if (filter != null)
            {
                query = query.Where(filter);
            }

            foreach (var includeProperty in includeProperties.Split
                (new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
            {
                query = query.Include(includeProperty);
            }

            if (limit != null && limit.HasValue && limit.GetValueOrDefault() > 0)
            {
                query = query.Take(limit.Value);
            }

            if (orderBy != null)
            {
                return orderBy(query).ToList();
            }
            else
            {
                return query;
            }
        }

The issue is not in here, it is in where you use `CategoryProducts`, you should show more code — cuongle, Jan 28 '13 at 16:03
Can't tell much without seeing code, but if you are just pulling down data for read only purposes, you can apply `.AsNoTracking()` to your reads to speed things up a bit — Thewads, Jan 28 '13 at 16:08
What code would you like to see? I'll put the `Category` and `Product` classes up — Aarron H, Jan 28 '13 at 16:09
I think the "Include method" http://msdn.microsoft.com/en-us/library/bb738708.aspx might be what you are looking for, by using include you are basically telling entity-framework to retrieve the desired fields in the first query execution and not lazy-load them — Bahador Izadpanah, Jan 28 '13 at 16:14
It depends on your use-case, generally if those properties are frequently accessed it's a good idea to include them otherwise there will be some database access for each one, would you check to see if that helps in your case? the number of records you mentioned is not that much IMHO.exactly how much time do you mean by "long time"? are you checking that time in the debugger watch? cause if that's the case it might be normal. — Bahador Izadpanah, Jan 28 '13 at 16:27
Ok I will test it out with the 'Include'. With that, should I remove the virtual tag from the List in the category class? — Aarron H, Jan 28 '13 at 16:32
@BahadorIzadpanah And to answer your questions, 'long time' i mean like 2 minutes+. — Aarron H, Jan 28 '13 at 16:36
@AarronH I've just found out that apparently using the virtual keyword will enable lazy loading by default so removing it might have the same effect but I'm not sure about that sorry, more info [here](http://stackoverflow.com/questions/5597760/what-effects-can-the-virtual-keyword-have-in-entity-framework-4-1-poco-code-fi) — Bahador Izadpanah, Jan 28 '13 at 16:38
You might also be able to use .skip() and .take() methods to only retrieve as much items as needed back. I also recommend including the lines of code in which you are querying against the EF in the question. — Bahador Izadpanah, Jan 28 '13 at 16:45
@BahadorIzadpanah if you do so you will not disable but make impossible lazy loading. To disable lazy loading use `someContext.LazyLoadingEnabled = false;` — tschmit007, Jan 28 '13 at 16:47
@BahadorIzadpanah I will edit the question to show the code that is doing the querying. Its just the normal Get method. — Aarron H, Jan 28 '13 at 16:51
@tschmit007 I understand that by "doing so" you mean removing the virtual keyword right? thanks for the tip — Bahador Izadpanah, Jan 28 '13 at 16:54

score 2 · Accepted Answer · answered Jan 28 '13 at 17:05

2

I imagine you Get<Category>(x => x.SomeName == "abcdef") and the iterate on Products.

Can't you Get<Product>(x => x.Categories.Where(y => y.SomeName == "abcdef").Count() > 0) ?

answered Jan 28 '13 at 17:05

tschmit007

7,559
2
35
43

This is pretty much what I did.. Thanks – Aarron H Jan 28 '13 at 19:32

Entity Framework 4 Code First Many-to-Many relationship slow

1 Answers1