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

问题描述

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

var rootCategoryItem = DatabaseContext.Categories.Include("子类别").OrderBy(c => c.CategoryOrder).Single(c => c.CategoryId == 1);

我知道在 EF 中你还不能过滤包含的项目，我可以写一些 LINQ 代码来过滤掉不需要的子类别......但是 LINQ 代码被转换成一个可怕的 SQL，这是非常不-优化.我也可以编写一个存储过程来执行此操作(并编写比 LINQ 更好的查询)，但我真的想使用纯 EF.

所以我只有 2 个选项(除非有人可以看到其他选项).

首先是遍历子类别，删除不需要的:

 var subCategoriesToFilter = rootCategoryItem.SubCategories.ToList();for (int i = 0; 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="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>

}}

在这两个选项中，哪一个是最好的选择?还是我缺少其他选项?

解决方案

好的，Servy 的回答非常正确，我不得不修改他的答案以使其有效:

 var rootCategoryItem = DatabaseContext.Categories.OrderBy(c => c.CategoryId).ToList().Select(c => new Category(){SubCategories = c.SubCategories.Where(sub => !sub.Deleted).ToList(),//确保只返回未删除的子类别CategoryId = c.CategoryId,类别名称 = c.类别名称，Category_ParentID = c.Category_ParentID,CategoryOrder = c.CategoryOrder,Parent_Category = c.Parent_Category,已删除 = c.已删除}).Single(c => c.CategoryId == 1);

我在尝试使用 Servy 的解决方案时遇到了几个错误:

<块引用>

无法在 LINQ to Entities 查询中构造实体或复杂类型.Category"

无法将类型隐式转换为 System.Collections.Generic.ICollection.存在显式转换(您是否缺少演员表?)

这一切都通过在 Select() 方法之前添加 .ToList() 来解决.

解决方案

虽然您无法过滤通过 Include 包含的集合，但您可以使用 Select 并将该集合投影到过滤后的集合.

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.类别名称，//包括这里需要的任何其他字段}).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屋！