I currently have this LINQ/EF code in my application:
var rootCategoryItem = DatabaseContext.Categories
.Include("SubCategories")
.OrderBy(c => c.CategoryOrder)
.Single(c => c.CategoryId == 1);
I know in EF you can't filter Included items yet, and I can write some LINQ code to filter out the SubCategories that aren't needed... but the LINQ code gets converted to a horrendous SQL which is highly un-optimised. I could also write a stored proc that does this (and write a much better query than LINQ), but I really want to use pure EF.
So I'm left with 2 options (unless someone can see other options).
The first is to loop through the subcategories, remove the ones that aren't needed:
var subCategoriesToFilter = rootCategoryItem.SubCategories.ToList();
for (int i = 0; i < subCategoriesToFilter.Count; i++)
{
if (subCategoriesToFilter[i].Deleted)
rootCategoryItem.SubCategories.Remove(subCategoriesToFilter[i]);
}
The second option would be to have this in my view:
<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))
{
@if(!Model.Deleted)
{
<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>
</span>
</div>
</li>
}
}
</ul>
Out of the 2, which one would be the best option? Or is there another option I'm missing?
The Solution
OK, Servy's is pretty much correct, I had to modify his answer to make it work:
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);
I had several errors trying to get Servy's solution to work:
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?)
This was all resolved by adding .ToList() before the Select() method.
While you cannot filter a collection included via Include
, you can use Select
and project that collection into a filtered collection.
var rootCategoryItem = DatabaseContext.Categories
.OrderBy(c => c.CategoryOrder)
.Select(c => new Category()
{
SubCategories = c.SubCategories.Where(sub => !sub.Deleted)
.OrderBy(sub => sub.CategoryOrder),
c.CategoryId,
c.CategoryName,
//include any other fields needed here
})
.Single(c => c.CategoryId == 1);
I recon this way looks more cleaner and shorter. Not sure about database impact
var rootCategoryItem = DatabaseContext.Categories.SingleOrDefault();
if (rootCategoryItem == null) return null;
{
rootCategoryItem.Items = rootCategoryItem ?.Items.Where(x => !x.IsDeleted).ToList();
return rootCategoryItem;
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With