过滤包括LINQ和实体框架中的项目 [英] Filtering include items in LINQ and Entity Framework

问题描述

我目前在我的应用程序中有这个LINQ / EF代码：

  var rootCategoryItem = DatabaseContext.Categories 
。 Include（SubCategories）
 .OrderBy（c => c.CategoryOrder）
 .Single（c => c.CategoryId == 1）; 
  

我知道在EF你不能过滤包括的项目，我可以写一些LINQ代码过滤掉不需要的子类别...但是LINQ代码被转换为高度未优化的可怕的SQL。我也可以写一个存储过程，这样做（并写出比LINQ更好的查询），但我真的想使用纯EF。

所以我离开有两个选项（除非有人可以看到其他选项）。

第一个是循环遍历子类别，删除不需要的选项：

  var subCategoriesToFilter = rootCategoryItem.SubCategories.ToList（）; （int i = 0; i< subCategoriesToFilter.Count; i ++）
 {
 if（subCategoriesToFilter [i] .Deleted）
 rootCategoryItem.SubCategories。除去（subCategoriesToFilter [I]）; 
} 
  

第二个选项是在我的视图中：

 < 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（Model.SubCategories.OrderBy（c => c.CategoryOrder）中的var categoryitem）
 {
 
 @if（！Model.Deleted）
 {
< li class =treelistitemid =@ Model.CategoryId> 
< div class =ui-accordion-header ui-state-default ui-corner-all ui-accordion-icons ui-sortable-handle first> 
< span class =可点击> 
< 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> 
  

在2中，哪一个将是最好的选择？还是还有另一个选择我失踪了？

解决方案

OK ，Servy's是非常正确的，我不得不修改他的答案使其工作：

  var rootCategoryItem = DatabaseContext.Categories 
 .OrderBy（c => c.CategoryId）
 .ToList（）。选择（c => new Category（）
 {
 SubCategories = c.SubCategories.Where =>！sub.Deleted）.ToList（），//确保只返回未删除的子类别
 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 ）; 
  

我有几个错误试图让Servy的解决方案工作：

不能在LINQ to Entities查询中构造实体或复合类型.Category

不能隐式转换类型到System.Collections.Generic.ICollection。存在一个显式的转换（你错过了一个转换）？

这一切都通过在Select（）之前添加.ToList方法。

解决方案

虽然您无法过滤通过 Include 您可以使用选择并将该集合投影到过滤的集合中。

  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，
 //包括这里需要的任何其他字段
}）
 .Single（c => c.CategoryId == 1）; 
  

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);

这篇关于过滤包括LINQ和实体框架中的项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！