Recursive or hierarchical queries using EF Code First and Migrations
Introduction
This sample demonstrates how you could implement recursive or hierarchical queries using EF Code First for a self-referencing table or entity. You can then query an entity for all its descendents or all its ancestors. LINQ itself does not support recursive or hierarchical queries. To execute recursive or hierarchical queries you need to execute sql in the database directly that uses a recursive common table expression (CTE) in Microsoft SQL Server or uses CONNECT BY PRIOR and CONNECT_BY_ROOT in Oracle. Preferably you would create a table valued function (TVF) in the database, but EF Code First does not yet support calling TVFs. Instead, until EF Code First supports TVFs, we can create a join or junction entity and have EF Code First map that entity to a view instead of a table, where this view has the required recursive or hierarchical sql. This join entity is otherwise similar to the one required by many-to-many relationships. Where the parent-child relationship is one-to-many, the ancestor-descendant relationship is indeed many-to-many. If you let EF Code First create your database it will by default create a table for this join entity. Using an IDatabaseInitializer you can drop the table after its been created and create a view in its place. Or using EF Code First Migrations you can forego the creation of the table completely and create a view directly. The view is not an updateable view, but as long as you only query the join entity and not insert, update or delete the join entity EF Code First is just as happy querying a view as it is querying a table. And just like a TVF a view is composable.
Building the Sample
The sample requires Visual Studio 2010 and SQL Server Express. It uses the current beta 1 of EF Code First Migrations. To build the database run the Update-Database command from the NuGet Package Manager Console in Visual Studio.
Description
We start with a simple self-referencing entity called Employee.
public class Employee
{
public int EmployeeID { get; set; }
[Required]
public string LastName { get; set; }
[Required]
public string FirstName { get; set; }
public int? ReportsToEmployeeID { get; set; }
public virtual Employee ReportsTo { get; set; }
public virtual ICollection<Employee> Manages { get; set; }
}
We then create a DbContext as follows:
public class NorthwindContext : DbContext, INorthwindContext
{
public IDbSet<Employee> Employees { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<Employee>().HasOptional(e => e.ReportsTo).WithMany(e => e.Manages).HasForeignKey(e => e.ReportsToEmployeeID).WillCascadeOnDelete(false);
}
}
Next we define the signature of our two recursive or hierarchical queries in an interface.
public interface INorthwindContext : IUnitOfWork
{
IDbSet<Employee> Employees { get; }
IQueryable<Employee> GetEmployeesForManagerRecursively(int managerEmployeeID);
IQueryable<Employee> GetManagersForEmployeeRecursively(int employeeID);
}
Preferably we would like to use a table-valued function to implement these two methods, but because EF Code First does not yet support calling TVFs, we can achieve the same result using a join or junction entity called ManagerEmployee mapped to a view in the database.
public class ManagerEmployee
{
public int ManagerEmployeeID { get; set; }
public int EmployeeID { get; set; }
}
Preferrably we would define this class as internal instead of public, but in order for EF Code First to be able to map this entity to a view in the database we need to define this class as public. We also need to define a public IDbSet of this entity on the NorthwindContext, but we purposely do not expose the IDbSet of this entity on the INorthwindContext interface. We do need to define the primary key of the entity in the NorthwindContext OnModelCreating method. Finally we can also implement the recursive or hierarchical queries using the ManagerEmployee entity.
public class NorthwindContext : DbContext, INorthwindContext
{
public IDbSet<Employee> Employees { get; set; }
public IDbSet<ManagerEmployee> ManagerEmployees { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<Employee>().HasOptional(e => e.ReportsTo).WithMany(e => e.Manages).HasForeignKey(e => e.ReportsToEmployeeID).WillCascadeOnDelete(false);
modelBuilder.Entity<ManagerEmployee>().HasKey(me => new { me.ManagerEmployeeID, me.EmployeeID });
}
public IQueryable<Employee> GetEmployeesForManagerRecursively(int managerEmployeeID)
{
return from managerEmployee in this.ManagerEmployees
join employee in this.Employees on managerEmployee.EmployeeID equals employee.EmployeeID
where managerEmployee.ManagerEmployeeID == managerEmployeeID
select employee;
}
public IQueryable<Employee> GetManagersForEmployeeRecursively(int employeeID)
{
return from managerEmployee in this.ManagerEmployees
join manager in this.Employees on managerEmployee.ManagerEmployeeID equals manager.EmployeeID
where managerEmployee.EmployeeID == employeeID
select manager;
}
}
To create the database and the view we could create a DatabaseInitializer as follows
public class NorthwindDatabaseInitializer : IDatabaseInitializer<NorthwindContext>
{
public void InitializeDatabase(NorthwindContext context)
{
if (context.Database.Exists() && !context.Database.CompatibleWithModel(true))
{
context.Database.Delete();
}
if (!context.Database.Exists())
{
context.Database.Create();
context.Database.ExecuteSqlCommand("DROP TABLE [dbo].[ManagerEmployees]");
context.Database.ExecuteSqlCommand(@"CREATE VIEW [dbo].[ManagerEmployees]
AS
WITH cte ( ManagerEmployeeID, EmployeeID )
AS ( SELECT EmployeeID ,
EmployeeID
FROM dbo.Employees
UNION ALL
SELECT e.EmployeeID ,
cte.EmployeeID
FROM cte
INNER JOIN dbo.Employees AS e ON e.ReportsToEmployeeID = cte.ManagerEmployeeID
)
SELECT ISNULL(EmployeeID, 0) AS ManagerEmployeeID ,
ISNULL(ManagerEmployeeID, 0) AS EmployeeID
FROM cte");
Seed(context);
context.SaveChanges();
}
}
private void Seed(NorthwindContext context)
{
}
}
Which we can set as follows:
Database.SetInitializer<NorthwindContext>(new NorthwindDatabaseInitializer());
Or, using EF Code First Migrations instead of a DatabaseInitializer you can completely forego the creation of the table and create the view directly.
public partial class ManagerEmployeesMigration : DbMigration
{
public override void Up()
{
//CreateTable(
// "ManagerEmployees",
// c => new
// {
// ManagerEmployeeID = c.Int(nullable: false),
// EmployeeID = c.Int(nullable: false),
// })
// .PrimaryKey(t => new { t.ManagerEmployeeID, t.EmployeeID });
Sql(@"CREATE VIEW [dbo].[ManagerEmployees]
AS
WITH cte ( ManagerEmployeeID, EmployeeID )
AS ( SELECT EmployeeID ,
EmployeeID
FROM dbo.Employees
UNION ALL
SELECT e.EmployeeID ,
cte.EmployeeID
FROM cte
INNER JOIN dbo.Employees AS e ON e.ReportsToEmployeeID = cte.ManagerEmployeeID
)
SELECT ISNULL(EmployeeID, 0) AS ManagerEmployeeID ,
ISNULL(ManagerEmployeeID, 0) AS EmployeeID
FROM cte");
}
public override void Down()
{
//DropTable("ManagerEmployees");
Sql("DROP VIEW [dbo].[ManagerEmployees]");
}
}
You then create the database using the Update-Database command in the NuGet Package Manager Console in Visual Studio.
