Entity Framework Code First Tree Model

问题:

I have the following entity:

public class Module
{
    public Module()
    {
        SubModules = new List<Module>();
    }

    public int Id { get; set; }
    public string Title { get; set; }
    public string Action { get; set; }
    public string Controller { get; set; }
    public string Icon { get; set; }

    public List<Module> SubModules { get; set; }
}

Which, when initialised through Code First generates the following table Schema:

CREATE TABLE [dbo].[Modules](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Title] [nvarchar](max) NULL,
    [Action] [nvarchar](max) NULL,
    [Controller] [nvarchar](max) NULL,
    [Icon] [nvarchar](max) NULL,
    [Module_Id] [int] NULL,
 CONSTRAINT [PK_dbo.Modules] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)

GO
ALTER TABLE [dbo].[Modules]  WITH CHECK ADD  CONSTRAINT [FK_dbo.Modules_dbo.Modules_Module_Id] FOREIGN KEY([Module_Id])
REFERENCES [dbo].[Modules] ([Id])
GO
ALTER TABLE [dbo].[Modules] CHECK CONSTRAINT [FK_dbo.Modules_dbo.Modules_Module_Id]
GO

The problem is that when I populate this table with a parent module (Module_Id of null) and two child modules (Module_Id of the parent Module) and query the DBContext's collection of Modules, I get the parent module with it's collection of child modules correctlly, but I also get the child modules returned by themselves.

So the Modules collection in the DbContext looks a bit like this:

ParentModule
---Child Module
---Child Module
Child Module
Child Module

What I need is for those two Child Modules to not be returned as Modules in their own right, but only as the children of the parent.

Hope I've explained this ok.

回答1:

I would add a ParentModuleId property (int?) to your Module class.

public class Module
{
    public Module()
    {
        SubModules = new List<Module>();
    }

    public int Id { get; set; }
    public string Title { get; set; }
    public string Action { get; set; }
    public string Controller { get; set; }
    public string Icon { get; set; }

    public int? ParentModuleId { get; set; }

    [ForeignKey("ParentModuleId")]
    public virtual ICollection<Module> SubModules { get; set; }
}

Notice how I have also added a ForeignKey attribute to the SubModules list to ensure that the new ParentModuleId property is used as the foreign key column.

This way you can manually check for the presence of a parent module.

You can then get the "root modules" like this:

var rootModules = context.Modules.Where(x => x.ParentModuleId == null);

If you need that a lot, you can also create an extension method:

public IQueryable<Module> WithoutParent(this IQueryable<Module> modules)
{
    return modules.Where(x => x.ParentModuleId == null);
}

var rootModules = context.Modules.WithoutParent();

回答2:

Couldn't add a comment, but Kristof Claes forgot to make the

public List<Module> SubModules { get; set; }

virtual as in:

public virtual List<Module> SubModules { get; set; }

Otherwise EntityFramework cannot prepopulate the List for you when it loads the Children from the database. So correct code would be:

public class Module
{
    public Module()
    {
        SubModules = new List<Module>();
    }

    // Other properties

    public int? ParentModuleId { get; set; }

    [ForeignKey("ParentModuleId")]
    public virtual List<Module> SubModules { get; set; }
}