Filtering include items in LINQ and Entity Framework

c# entity-framework linq


Currently, my application contains the following LINQ/EF code:

var rootCategoryItem = DatabaseContext.Categories
                            .OrderBy(c => c.CategoryOrder)
                            .Single(c => c.CategoryId == 1);

I am aware that Included items cannot currently be filtered in EF, but I can create some LINQ code to remove any unnecessary SubCategories. Unfortunately, this LINQ code is then translated to terrible, incredibly inefficient SQL. I could create a stored procedure that accomplishes this as well (and create a query that is far superior to LINQ), but I really want to utilize pure EF.

I therefore only have two choices (unless someone can see other options).

The first is to go through the subcategories and eliminate those that are unnecessary:

        var subCategoriesToFilter = rootCategoryItem.SubCategories.ToList();

        for (int i = 0; i < subCategoriesToFilter.Count; i++)
            if (subCategoriesToFilter[i].Deleted)

In my opinion, the second choice would be to have this:

<ul class="treeview ui-accordion-content ui-helper-reset ui-widget-content ui-corner-bottom ui-accordion ui-widget ui-sortable ui-accordion-content-active">
@foreach (var categoryitem in Model.SubCategories.OrderBy(c => c.CategoryOrder))

        <li class="treelistitem" id="@Model.CategoryId">
            <div class="ui-accordion-header ui-state-default ui-corner-all ui-accordion-icons ui-sortable-handle first">
            <span class="clickable">
                <span class="ui-accordion-header-icon ui-icon treeviewicon treeviewplus"></span>
                <i class="glyphicon glyphicon-folder-open rightfolderpadding"></i><span class="categoryname">@Model.CategoryName</span>

Which of the two would be the better choice? Or is there another choice that I'm overlooking?

The Answer

Okay, Servy's response is mostly true, but I had to change it to fit my needs:

        var rootCategoryItem = DatabaseContext.Categories
            .OrderBy(c => c.CategoryId)
            .ToList().Select(c => new Category()
                SubCategories = c.SubCategories.Where(sub => !sub.Deleted).ToList(),    //make sure only undeleted subcategories are returned
                CategoryId = c.CategoryId,
                CategoryName = c.CategoryName,
                Category_ParentID = c.Category_ParentID,
                CategoryOrder = c.CategoryOrder,
                Parent_Category = c.Parent_Category,
                Deleted = c.Deleted
            }).Single(c => c.CategoryId == 1);

In my attempts to make Servy's idea work, I encountered various problems:

The entity or complex type '.Category' cannot be constructed in a LINQ to Entities query

Cannot implicitly convert type to System.Collections.Generic.ICollection. An explicit conversion exists (are you missing a cast?)

Everything worked out when.ToList() was added before the Select() procedure.

9/2/2014 11:25:12 PM

Accepted Answer

Although a collection contained via cannot be filteredInclude , you may employSelect and transform that collection into one that has been filtered.

var rootCategoryItem = DatabaseContext.Categories
    .OrderBy(c => c.CategoryOrder)
    .Select(c => new Category()
        SubCategories = c.SubCategories.Where(sub => !sub.Deleted)
            .OrderBy(sub => sub.CategoryOrder),
        //include any other fields needed here
    .Single(c => c.CategoryId == 1);
9/2/2014 5:40:45 PM

Popular Answer

This technique, in my opinion, appears to be cleaner and shorter. Uncertain of database impact

 var rootCategoryItem = DatabaseContext.Categories.SingleOrDefault();
 if (rootCategoryItem == null) return null;
   rootCategoryItem.Items = rootCategoryItem ?.Items.Where(x => !x.IsDeleted).ToList();
   return rootCategoryItem;

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow