我的应用程序允许用户为其项目分配类别。类如下所示。
class Item
{
public string Id { get; set; }
/* ... */
public virtual IEnumerable<Category> Categories { get; set; }
}
class Category
{
public string Id { get; set; }
public virtual Category Parent { get; set; }
public virtual IEnumerable<Category> Subcategories { get; set; }
}
从上面可以看出，类别之间有一个层次结构。
如果我有以下类别树：
|-- Item 1
|---- Child 1
|---- Child 2
|---- Child 3
|-- Item 2
用户想要显示 Item1，我想在结果中包含子项 1、2 和 3 的所有类别，即查询中应包含四个类别(项 1、子项 1、子项 2、子项 3)。
我如何使用EntityFrameworkCore执行此操作。我正在使用SQLite作为后端，但如果可能的话，我更愿意在没有SQL的情况下执行此操作。
你有没有尝试过使用 DbFunction 的 ToString() 方法
ToString() 将打印当前对象。所以，它的孩子也会被打印出来。 您需要在 Item 类中重写此方法。
实体框架非常方便，具有自动化功能，但不幸的是，就像生活中的大多数事情一样，它还没有掌握所有棘手的情况，这是其中之一。(尽管公平地说，问题几乎在于将分层数据存储在关系数据库中)。
我倾向于通过"作弊"来解决类似的情况，至少在可能/合适的情况下，通过引入某种额外的属性/列来对它们进行分组，然后简单地将它们全部加载，并手动进行关系映射，这通常非常简单。
在进行多次调用之前，通常更喜欢在一个数据库调用中加载其他数据。(不过，您可能仍然需要潜伏任何潜伏的数据库管理员)。
假设您正在计划一个广度可能为N个量，深度为M个量的情况(如果没有，其他答案应该就足够了)，这是一个快速而肮脏的解决方案，在最坏的情况下至少可以完成工作。
为了坚持使用 EF，这个想法本质上是首先解耦 EF 可能已映射的关系，并使用简单的值类型作为参考：(这不是真正的必需品，但我倾向于更喜欢)
class Item
{
public string Id { get; set; }
public virtual IEnumerable<Category> Categories { get; set; }
}
class Category
{
public string Id { get; set; }
// We drop the parent reference property and add a simple ParentId property instead,
// hopefully saving us some future headache.
//
public string ParentId { get; set; }
//public virtual Category Parent { get; set; } // Goodbye dear friend, you have served us well.
// Depending on how you're configuring, we might have to "loose" some EF-mapped relationships,
// [NotMapped] is merely an example of that here, it's not neccessarily required.
[NotMapped]
public virtual IEnumerable<Category> Subcategories { get; set; }
// As an example, I've just added the item id as our category scope/discriminator,
// allowing us to limit our full query at least somewhat.
//
public string ItemId { get; set; }
}
现在，我们已准备好执行 EF 最擅长的操作。加载和映射数据！我们将单独加载类别实体的计划列表，与其他任何实体没有任何直接关系，然后自己映射它们。
为了使它易于维护，让我们创建一个简洁的小静态类，并添加一些有用的扩展方法来帮助我们，从初始的 DbContext-load 开始。
public static class CategoryExtensions
{
/// <summary>
/// Extension method to find and load all <see cref="Category"/> per <see cref="Category.ItemId"/>
/// </summary>
public static List<Category> FindCategoriesForItemId(this DbContext dbContext, string itemId)
=> dbContext.Set<Category>()
.Where(c => c.ItemId == itemId)
.ToList();
}
一旦我们能够轻松加载类别，能够映射子类别并在必要时可能扁平化它们/任何子类别会很有用，所以我们在那里抛出了另外两个方法，一个将子类别映射到我们发现的所有类别，一个用于扁平化我们未来可能拥有的分层结构(或只是为了好玩)。
/// <summary>
/// Flattens the IEnumerable by selecting and concatenating all children recursively
/// </summary>
/// <param name="predicate">Predicate to select the child collection to flatten</param>
/// <returns>Flat list of all items in the hierarchically constructed source</returns>
public static IEnumerable<TSource> Flatten<TSource>(this IEnumerable<TSource> source, Func<TSource, IEnumerable<TSource>> predicate)
=> source.Concat(source.SelectMany(s => predicate(s).Flatten(predicate)));
/// <summary>
/// "Overload" for above but to use with a single root category or sub category...
/// </summary>
public static IEnumerable<TSource> Flatten<TSource>(this TSource source, Func<TSource, IEnumerable<TSource>> predicate)
=> predicate(source).Flatten(predicate);
/// <summary>
/// For each entry in the <paramref name="flatSources"/>,
/// finds all other entries in the <paramref name="flatSources"/> which has
/// a <paramref name="parentRefPropSelector"/> value matching initial entries
/// <paramref name="identityPropSelector"/>
/// </summary>
/// <param name="flatSources">Flat collection of entities that can have children</param>
/// <param name="identityPropSelector">Selector Func to select the identity property of an entry</param>
/// <param name="parentRefPropSelector">Selector Func to select the parent reference property of an entry</param>
/// <param name="addChildren">Action that is called once any children are found and added to a parent entry</param>
public static IEnumerable<TSource> MapChildren<TSource, TKey>(
this IEnumerable<TSource> flatSources,
Func<TSource, TKey> identityPropSelector,
Func<TSource, TKey> parentRefPropSelector,
Action<TSource, IEnumerable<TSource>> addChildren)
=> flatSources.GroupJoin( // Join all entityes...
flatSources, // ... with themselves.
parent => identityPropSelector(parent), // On identity property for one...
child => parentRefPropSelector(child), // ... And parent ref property for another.
(parent, children) => // Which gives us a list with each parent, and the children to it...
{
addChildren(parent, children); // ... Which we use to call the addChildren action, leaving adding up to the caller
return parent;
});
就是这样。它并不完美，但在我看来，这是一个足够不错的入门解决方案，它仍然利用了 EF 并且不会使其过于复杂。唯一担心的是，如果加载的类别数量变得太大，但在这一点上，花一些实际时间在更"适当"的解决方案上是非常值得的。(我还没有实际测试过MapChildren扩展，它有很大的改进空间，但我希望它有助于说明这个想法。
为了最终实际使用它，它最终看起来像这样：
/// <summary>
/// Loads and structures all categories related to <see cref="itemId"/>
/// and returns first <see cref="Category"/> where <see cref="Category.ParentId"/>
/// is null.
/// </summary>
public Category GetMeRootCategorylore(string itemId)
{
using (var dbContext = new DbContext())
{
var mappedAndArmedCategories
= dbContext // Use our db context...
.FindCategoriesForItemId(itemId) // To find categories..
.MapChildren( // And then immediately map them, which comes close to what we're used with when using EF.
parent => parent.Id, // Set the identity property to map children against
child => child.ParentId, // Set the parent references to map with
(parent, children) => parent.Subcategories = children); // This is called when children have been found and should be mapped to the parent.
// Oh noes, what if I need a flattened category list later for whatever reason! (Or to do some real lazy loading when looking a single one up!)
// ... Aha! I almost forgot about our nifty extension method to flatten hierarchical structures!
//
var flattenedList = mappedAndArmedCategories.Flatten(c => c.Subcategories);
// Maybe we'll pick up a root category at some point
var rootCategory = mappedAndArmedCategories.FirstOrDefault(c => c.ParentId == null);
// And perhaps even flatten it's children from the single category node:
var subFlattenedList = rootCategory?.Flatten(c => c.Subcategories);
// But now we've had enough fun for today, so we return our new category friend.
return rootCategory;
}
}
最后，如果您想深入研究或获得其他想法，这里有一个关于关系数据库中分层数据的非常有用且有用的问题：在关系数据库中存储分层数据有哪些选项？
你可以像这样找孙子孙女：
考虑急切负载
public List<Category> GetCategories(int itemId)
{
Category categoryChildren = _context.Set<Category>()
.Include(i => i.Subcategories)
.ThenInclude(i => i.Category)
.FirstOrDefault(w => w.ItemId == itemId);
var categories = new List<Category>();
if (categoryChildren == null)
return categories;
// get children
categories.AddRange(categoryChildren.Subcategories.Select(s => s.Category));
// get grandchildren
foreach (var subCategory in categoryChildren.Subcategories.Select(s => s.Category))
{
_context.Entry(subCategory).Collection(b => b.Subcategories).Load();
foreach (var categoryGrandChildren in subCategory.Subcategories)
{
_context.Entry(categoryGrandChildren).Reference(b => b.Category).Load();
// check if not adding repeatables
if (!categories.Any(a => a.Id == categoryGrandChildren.Id))
categories.Add(categoryGrandChildren.Category);
}
}
return categories;
}
如果您使用的是延迟加载，则甚至不需要.包括和。加载方法。
public void PrintAllItems() //Use Take or where to fetch you specfic data
{
var allItems = context.Items
.Include(item=> item.Categories)
.ThenInclude(cat=>cat.Subcategories)
.ToList();
foreach(var item in allItems)
{
Console.WriteLine(item.Id);
foreach(var category in item.Categoires)
{
Console.WriteLine(category.Id);
foreach(var sub in category.Subcategories)
{
Console.WriteLine(sub.Id);
}
}
}
}
public void FirstItem(string Id) //Use Take or where to fetch you specfic data
{
var allItems = context.Items
.Include(item=> item.Categories)
.ThenInclude(cat=>cat.Subcategories)
.FirstOrDefault(g=>g.Id==Id);
foreach(var item in allItems)
{
Console.WriteLine(item.Id);
foreach(var category in item.Categoires)
{
Console.WriteLine(category.Id);
foreach(var sub in category.Subcategories)
{
Console.WriteLine(sub.Id);
}
}
}
}