ASP.NET MVC, Entity Framework and performance

Question

I have problem with performance and don't understand behaviour of EF. I use ASP.NET MVC application and have the following code in model:

public List<Portal> PortalListWithCategories()
{
    List<Portal> q = new List<Portal>();
        q = (from i in _dataContext.Portals.Include("Categories").Include("Ideas") where i.Categories.Count > 0 orderby i.DefaultPortal descending select i).ToList();
    return q;
}

I call it from controller:

        portalList = _repository.PortalListWithCategories();

as I understand, EF should execute batch request and return collection of portals with nested collections "Categories" and "Ideas".

But on view I have following:

                @foreach (var category in portal.Categories.Where(n => n.Ideas.Count > 0 && n.Portals.Any(g => g.PortalID == portal.PortalID)))
                {
                    if ((from e in category.Ideas where e.Portals.Any(t => t.PortalID == portal.PortalID) select e).Count() > 0)
                    {
                        string categoryLink = Url.RouteUrl("Full", new { PortalID = portal.PortalID, CategoryID = category.CategoryID, action = "Ideas" });
                        List<NEOGOV_Ideas.Models.Ideas> ideas = category.Ideas.Where(o => o.Portals.Any(p => p.PortalID == portal.PortalID) && o.Categories.Any(h => h.CategoryID == category.CategoryID)).OrderByDescending(k => k.CreatedDateTime).ToList();
                    <div class="grid_4">
                        <h4>
                            <a href="@categoryLink">@category.CategoryName<span class="count_link">&nbsp;(@ideas.Count())</span>
                                <span class="follow_link">&raquo;</span></a></h4>
                        <ul>
                            @foreach (var idea in ideas.Take(3))
                            {
                                string ideaLink = Url.RouteUrl("IdeaShort", new { id = idea.IdeaID });
                                if (!idea.IdeaTypeReference.IsLoaded) { idea.IdeaTypeReference.Load(); }
                                string cssclass = " class=\"" + idea.IdeaType.TypeName.ToLower() + "\"";
                                <li><a href="@ideaLink" @cssclass>@idea.Title</a></li>
                            }
                        </ul>
                    </div>
                            if (i == 2)
                            {
                    <div class="clear">
                    </div>
                            }
                            i++;
                    }
                }

as I understand, I should not have new requests to DB, but I have and very many. Why?

[ADDED]

I found that this string (from e in category.Ideas where e.Portals.Any(t => t.PortalID == portal.PortalID) select e).Count() generates many requests to DB like:

exec sp_executesql N'SELECT [Extent2].[PortalID] AS [PortalID], [Extent2].[PortalName] AS [PortalName], [Extent2].[DefaultPortal] AS [DefaultPortal] FROM [dbo].[PortalIdeas] AS [Extent1] INNER JOIN [dbo].[Portals] AS [Extent2] ON [Extent1].[PortalID] = [Extent2].[PortalID] WHERE [Extent1].[IdeaID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=5618

why it happens for Count() ?

second question how to make it correctly?

Kamyar · Accepted Answer · 2011-10-05 16:46:40Z

1

Because e.Portals is not loaded and in your foreach loop, EF has to have a round-trip to database to fetch Portals for Idea.
You should Include Ideas in your query too.

I have not tested this but I think you should add .Include("Ideas.Portals") (or if you use EF 4.1, addusing System.Data.Entity and use .Include(c => c.Ideas.Portals)).

answered Oct 5, 2011 at 16:46

Kamyar

18.8k9 gold badges101 silver badges174 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

ASP.NET MVC, Entity Framework and performance

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related