LINQ查询以查找相关数据 [英] LINQ query to find related data
问题描述
我以前在Stack Overflow上的递归产品类别树状视图中获得了一些帮助，并且可以正常工作:
I got some help with my recursive product category tree view here on Stack Overflow before, and this is working:
实体模型:
public class ProductCategory
{
public int Id { get; set; }
public int SortOrder { get; set; }
public string Title { get; set; }
[ForeignKey(nameof(ParentCategory))]
public int? ParentId { get; set; }
public ProductCategory ParentCategory { get; set; } //nav.prop to parent
public ICollection<ProductCategory> Children { get; set; } //nav. prop to children
public List<ProductInCategory> ProductInCategory { get; set; }
}
控制器:
var categories = _context.ProductCategories.Include(e => e.Children).ToList();
var topLevelCategories = categories.Where(e => e.ParentId == null);
return View(topLevelCategories);
查看:
@if (Model != null)
{
foreach (var item in Model)
{
<li>
@item.Title
<ul>
@Html.Partial("_CategoryRecursive.cshtml", item.Children)
</ul>
</li>
}
}
但是当我尝试将此设置转换为我的视图模型(并添加一个属性以计数每个类别中的产品以及没有任何类别连接的产品列表时)...:
But when I tried to translate this setup to my viewmodel (and adding a property for counting products in each category, as well as a list of products without any category connection) ...:
Viewmodel:
Viewmodel:
public class ViewModelProductCategory
{
public int Id { get; set; }
public int? ParentId { get; set; }
public string Title { get; set; }
public int SortOrder { get; set; }
public string ProductCountInfo
{
get
{
return Products != null && Products.Any() ? Products.Count().ToString() : "0";
}
}
public ViewModelProductCategory ParentCategory { get; set; } // Nav.prop. to parent
public IEnumerable<ViewModelProductCategory> Children { get; set; } // Nav.prop. to children
public List<ViewModelProduct> Products { get; set; } // Products in this category
public List<ViewModelProduct> OrphanProducts { get; set; } // Products with no reference in ProductInCategory
}
控制器:
var VMCategories = _context.ProductCategories
.Include(e => e.Children)
.OrderBy(s => s.SortOrder)
.Where(r => r.ParentId == null) // only need root level categories in the View
.Select(v => new ViewModelProductCategory
{
Id = v.Id,
ParentId = v.ParentId,
Title = v.Title,
SortOrder = v.SortOrder,
// get products without a category:
OrphanProducts = v.ProductInCategory
.Where(o => !_context.ProductsInCategories.Any(pc => o.Id == pc.ProductId))
.Select(orph => new ViewModelProduct
{
Id = orph.Product.Id,
Title = orph.Product.Title,
Price = orph.Product.Price,
Info = orph.Product.Info,
SortOrder = orph.SortOrder
})
.OrderBy(s => s.SortOrder)
.ToList()
})
.ToList();
return View(VMCategories);
查看:
@if (Model != null)
{
foreach (var item in Model)
{
<li>
@item.Title (@item.ProductCountInfo)
<ul>
@Html.Partial("_CategoryRecursive.cshtml", item.Children)
</ul>
</li>
}
}
...它将不再起作用.该视图可以渲染，但是仅显示根类别.看来我修改过的查询不会获得任何子类别.当我检查查询结果时，Children属性为null.
... it won't work any more. The view does render, but it is just showing the root categories. It seems that my modified query won't get any of the children categories. When I inspect the query result, the Children property is null.
编辑
我要使用@Rainman的解决方案，并已将查询.Select更改为包含Children = v.Children,，并因此更改了我的viewmodel导航属性:
I'm going with @Rainman's solution, and have changed my query .Select to include Children = v.Children,, and changing my viewmodel navigational properties thusly:
public ProductCategory ParentCategory { get; set; } //nav.prop to parent
public ICollection<ProductCategory> Children { get; set; } //nav. prop to children
我还创建了新的视图模型CategoryRecursiveModel，并将视图更改为此:
I have also created the new viewmodel CategoryRecursiveModel and changed my view to this:
@model IEnumerable<MyStore.Models.ViewModels.ViewModelProductCategory>
<ul>
@if (Model != null)
{
foreach (var item in Model)
{
<li>
@item.Title (@item.ProductCountInfo)
<ul>
@Html.Partial("_CategoryRecursive.cshtml", new CategoryRecursiveModel
{
Children = item.Children.ToList();
})
</ul>
</li>
}
}
</ul>
现在，我面临InvalidOperationException，因为该视图期望ViewModelProductCategory的IEnumerable，但接收到CategoryRecursiveModel.
Now I'm faced with InvalidOperationException, as the view is expecting an IEnumerable of ViewModelProductCategory, but receives CategoryRecursiveModel.
推荐答案
因为您没有选择Children进行第二次查询；
Because you are not selecting the Children for second query;
var VMCategories = _context.ProductCategories
.Include(e => e.Children)
.OrderBy(s => s.SortOrder)
.Where(r => r.ParentId == null) // only need root level categories in the View
.Select(v => new ViewModelProductCategory
{
Id = v.Id,
Children = v.Children, // Select it
ParentId = v.ParentId,
Title = v.Title,
SortOrder = v.SortOrder,
// get products without a category:
OrphanProducts = v.ProductInCategory
.Where(o => !_context.ProductsInCategories.Any(pc => o.Id == pc.ProductId))
.Select(orph => new ViewModelProduct
{
Id = orph.Product.Id,
Title = orph.Product.Title,
Price = orph.Product.Price,
Info = orph.Product.Info,
SortOrder = orph.SortOrder
})
.OrderBy(s => s.SortOrder)
.ToList()
})
.ToList();
public ProductCategory ParentCategory { get; set; } //nav.prop to parent
public ICollection<ProductCategory> Children { get; set; } //nav. prop to children
此外，导航属性仅对于EF实体存在，而不是ViewModelProductCategory模型类或其他类.
Also, navigation properties exist only for EF entities not ViewModelProductCategory model class or other classes.
编辑
为_CategoryRecursive视图创建模型类；
public class CategoryRecursiveModel
{
public List<ProductCategory> Children { get; set; }
}
然后更改主视图；
@if (Model != null)
{
foreach (var item in Model)
{
<li>
@item.Title (@item.ProductCountInfo)
<ul>
@Html.Partial("_CategoryRecursive.cshtml", new CategoryRecursiveModel
{
Children = item.Children.ToList();
})
</ul>
</li>
}
}
这篇关于LINQ查询以查找相关数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
