实体框架配方:分层数据管理

本文探讨了如何使用实体框架6管理检索和显示等分层数据,例如您在菜单系统中找到的分层数据。 Internet上有许多现有项目涵盖相同的主题,但大多数情况似乎不必要地复杂,并且不包括一旦检索到一旦检索到数据显示数据的任何信息。

分层数据通常存储在一个"self-referencing"表,表的主键也用作外键,以将子元素与父母相关联。例如,菜单项可能具有许多子菜单项,每个项目又可能具有一个或多个子项,每个子项也可能有一个或多个子项,广告Infinitum。数据库表看起来像这样:

具有EF6的分层数据

每个菜单项都有一个 parentmenuitemid 哪个是可用的。如果特定项目是顶级项,则该值将为null。否则,该值将是它与之相关的项目。菜单将项目链接到特定菜单。这是两个实体使用C#定义的方式

菜单实体

using System.Collections.Generic;

public class 菜单
{
    public int 菜单Id { get; set; }
    public string 菜单Name { get; set; }
    public icollection.<menuitem.> MenuItems { get; set; }
}

菜单Item实体

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

public class menuitem.
{
    public int menuitem.Id { get; set; }
    [StringLength(50)]
    public string 菜单Text { get; set; }
    [StringLength(255)]
    public string LinkUrl { get; set; }
    public int? MenuOrder { get; set; }
    public int? ParentMenuItemId { get; set; }
    public virtual menuitem. 父母 { get; set; }
    public virtual icollection.<menuitem.> Children { get; set; }
    public int 菜单Id { get; set; }
    public virtual 菜单 菜单 { get; set; }
}

自引用关系通过空标记定义 parentmenuitemid 属性配对虚拟 父母 财产。任何孩子都会通过虚拟照顾 icollection.<MenuItem> Children 财产。代码首次迁移的惯例需要生成相应的表和键是一个名为“父级的外键属性<name of key field>“(parentmenuitemid)与名为”父“的属性配对,以及名为”shows“的集合。这是 向上 代码首次迁移为两个实体生成的方法:

public override void 向上 ()
{
    CreateTable(
        "dbo.MenuItems",
        c => new
            {
                MenuItemId = c.Int(nullable: false, identity: true),
                MenuText = c.String(maxLength: 50),
                LinkUrl = c.String(maxLength: 255),
                MenuOrder = c.Int(),
                ParentMenuItemId = c.Int(),
                MenuId = c.Int(nullable: false),
            })
        .PrimaryKey(t => t.MenuItemId)
        .ForeignKey("dbo.MenuItems", t => t.ParentMenuItemId)
        .ForeignKey("dbo.Menus", t => t.MenuId, cascadeDelete: true)
        .Index(t => t.ParentMenuItemId)
        .Index(t => t.MenuId);
            
    CreateTable(
        "dbo.Menus",
        c => new
            {
                MenuId = c.Int(nullable: false, identity: true),
                MenuName = c.String(),
            })
        .PrimaryKey(t => t.MenuId);
            
}

parentmenuitemid 可为空,这是最高级别要素的存在。以下是要在此处执行的代码 种子 创建一个方法 菜单 对象并添加一些 menuitem.s to it:

var menuItems = new List<menuitem.>{
    new menuitem.{MenuText = "First Link", LinkUrl = "#", MenuOrder = 1},
    new menuitem.{MenuText = "Second Link", LinkUrl = "#", MenuOrder = 2},
    new menuitem.{MenuText = "Third Link", LinkUrl = "#", MenuOrder = 3},
    new menuitem.{MenuText = "Fourth Link", LinkUrl = "#", MenuOrder = 4},
    new menuitem.{MenuText = "Fifth Link", LinkUrl = "#", MenuOrder = 5},
    new menuitem.{MenuText = "First Child Link", LinkUrl = "#", MenuOrder = 1, ParentMenuItemId = 1},
    new menuitem.{MenuText = "Second Child Link", LinkUrl = "#", MenuOrder = 2, ParentMenuItemId = 1},
    new menuitem.{MenuText = "Third Child Link", LinkUrl = "#", MenuOrder = 3, ParentMenuItemId = 1},
    new menuitem.{MenuText = "First Grandchild Link", LinkUrl = "#",  MenuOrder = 1, ParentMenuItemId = 7},
    new menuitem.{MenuText = "Second Grandchild Link", LinkUrl = "#", MenuOrder = 2, ParentMenuItemId = 7},
    new menuitem.{MenuText = "Third Grandchild Link", LinkUrl = "#", MenuOrder = 3, ParentMenuItemId = 7}
};
menu.MenuItems = menuItems;
context.Menus.AddOrUpdate(m => m.MenuName, menu);
context.SaveChanges();

代码创建五个父菜单项。然后它创造了三个进一步的物品 parentmenuitemid 指定的。这些成为第一个父项的孩子。另一个有三个项目创建并与该项目有关 menuitem.Id 7,其实际将是第二个孩子。检索菜单项所需的LINQ非常简单:

public ActionResult Index()
{
    var model = new List<menuitem.>();
    using (var context = new EFRecipeContext())
    {
        model = context.MenuItems.Where(m => m.MenuId == 1).ToList();
    }
    return View(model);
}

和这个生成的SQL也很简单:

SELECT 
    [Extent1].[MenuItemId] AS [MenuItemId], 
    [Extent1].[MenuText] AS [MenuText], 
    [Extent1].[LinkUrl] AS [LinkUrl], 
    [Extent1].[MenuOrder] AS [MenuOrder], 
    [Extent1].[ParentMenuItemId] AS [ParentMenuItemId], 
    [Extent1].[MenuId] AS [MenuId]
    FROM [dbo].[MenuItems] AS [Extent1]
    WHERE 1 = [Extent1].[MenuId]

你可以选择明确地包括 孩子们 导航财产:

model = context.MenuItems.Include(m => m.Children).Where(m => m.MenuId == 1).ToList();

这将改变SQL以下内容:

SELECT 
    [Project1].[MenuItemId] AS [MenuItemId], 
    [Project1].[MenuText] AS [MenuText], 
    [Project1].[LinkUrl] AS [LinkUrl], 
    [Project1].[MenuOrder] AS [MenuOrder], 
    [Project1].[ParentMenuItemId] AS [ParentMenuItemId], 
    [Project1].[MenuId] AS [MenuId], 
    [Project1].[C1] AS [C1], 
    [Project1].[MenuItemId1] AS [MenuItemId1], 
    [Project1].[MenuText1] AS [MenuText1], 
    [Project1].[LinkUrl1] AS [LinkUrl1], 
    [Project1].[MenuOrder1] AS [MenuOrder1], 
    [Project1].[ParentMenuItemId1] AS [ParentMenuItemId1], 
    [Project1].[MenuId1] AS [MenuId1]
    FROM ( SELECT 
        [Extent1].[MenuItemId] AS [MenuItemId], 
        [Extent1].[MenuText] AS [MenuText], 
        [Extent1].[LinkUrl] AS [LinkUrl], 
        [Extent1].[MenuOrder] AS [MenuOrder], 
        [Extent1].[ParentMenuItemId] AS [ParentMenuItemId], 
        [Extent1].[MenuId] AS [MenuId], 
        [Extent2].[MenuItemId] AS [MenuItemId1], 
        [Extent2].[MenuText] AS [MenuText1], 
        [Extent2].[LinkUrl] AS [LinkUrl1], 
        [Extent2].[MenuOrder] AS [MenuOrder1], 
        [Extent2].[ParentMenuItemId] AS [ParentMenuItemId1], 
        [Extent2].[MenuId] AS [MenuId1], 
        CASE WHEN ([Extent2].[MenuItemId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
        FROM  [dbo].[MenuItems] AS [Extent1]
        LEFT OUTER JOIN [dbo].[MenuItems] AS [Extent2] ON [Extent1].[MenuItemId] = [Extent2].[ParentMenuItemId]
        WHERE 1 = [Extent1].[MenuId]
    )  AS [Project1]
    ORDER BY [Project1].[MenuItemId] ASC, [Project1].[C1] ASC

它可以实现儿童属性的群体,只有在代码中需要引用它们时才需要。如果您愿意采取严格面向对象的编码方法,您可能希望执行此操作。但是,您真正需要显示此数据的所有数据都是如下所示的递归助手 Buildmenu. method:

@helper Buildmenu.(IEnumerable<menuitem.> data, int? parentId = 空值)
{
    var items = data.Where(d => d.ParentMenuItemId == parentId).OrderBy(i => i.MenuOrder);
    if (items.Any())
    {
        <ul>
            @foreach (var item in items)
            {
                <li>
                    <a href="@item.LinkUrl">@item.MenuText</a>
                    @Buildmenu.(data, item.MenuItemId)
                </li>
            }
        </ul>
    }
}

第一次使用菜单数据将第一次传递给Helper 括号 省略价值,因此违约 空值。为所有有的人查询菜单项 parentmenuitemid 与之匹配 括号 值,在第一次迭代时产生所有是根项目的所有这些。如果有任何匹配项,则会创建无序列表,每个项目都显示为具有链接的列表项。随着每个项目的呈现,整个集合都被传回到了 Buildmenu. 方法与 括号 参数设置为当前项的ID,以便可以执行对属于当前项目的任何子项的检查。结果输出如下所示:

具有EF6的分层数据

概括

本文使用实体框架代码首次迁移来实现最简单的方法来模拟SQL Server中的自引用表,并研究了如何存储和检索表示层次结构的数据。最后,从数据库中检索数据,并且使用递归函数来管理分层数据的显示。