Entity Framework Traverse and return child records in self reference table

Question

I am using Entity Framework and have a table of BusinessUnits which can reference another record of the same type to form a child-parent hierarchy.

I also have a set of users, and user permissions, where each user defined in this table should have access to the BusinessUnit and all sub-business units in the hierarchy. Users should not have access to the BusinessUnit above the one referenced (if exists).

How can I go about forming LINQ queries to handle this self-referencing relationship tree and return all the business units (with child units) for which this user has access? Is it possible to do it in one query, or do I need to manually build the tree myself with a for-loop?

I have seen schema's reference in this way from node to parent, does this mean I have to start at the furthest child node to build the tree by one parent at a time?

Thanks in advance,

Chris

class BusinessUnit
{
    int BusinessUnitID {get;set;}
    public string BusinessName {get;set;}
    BusinessUnit ParentBusinessUnit {get;set;}
}

class User
{
    int UserID {get;set;}
    string Firstname {get;set;}
}

class UserPermissions
{
    [Key, ForeignKey("BusinessUnit"), Column(Order = 0)] 
    BusinessUnit BusinessUnit {get;set;}
    [Key, ForeignKey("User"), Column(Order = 1)] 
    User User {get;set;}
}

IEnumerable<BusinessUnit> GetUnitsForWhichUserHasAccess(User user)
{
/* Example 1
 given: BusinessUnitA (ID 1) -> BusinessUnitB (ID 2) -> BusinessUnitC (ID 3)
 with user with ID 1:
 and UserPermissions with an entry: BusinessUnit(2), User(1)
 the list { BusinessUnitB, BusinessUnitC } should be returned
*/

/* Example 2
 given: BusinessUnitA (ID 1) -> BusinessUnitB (ID 2) -> BusinessUnitC (ID 3)
 with user with ID 1:
 and UserPermissions with an entry: BusinessUnit(1), User(1)
 the list { BusinessUnitA, BusinessUnitB, BusinessUnitC } should be returned
*/
}

Answer 1

OK, there are a few things here. We can make this a bit easier by adding some more properties to your model. Is that an option? If so, add collection properties to the entities. Now, I don't know which EF API you're using: DbContext (code first or edmx) or ObjectContext. In my sample I've used the DbContext API with an edmx model to generate these classes.

If you prefer, with a few annotations you could dispense with the edmx file.

public partial class BusinessUnit
{
    public BusinessUnit()
    {
        this.ChlidBusinessUnits = new HashSet<BusinessUnit>();
        this.UserPermissions = new HashSet<UserPermissions>();
    }

    public int BusinessUnitID { get; set; }
    public string BusinessName { get; set; }
    public int ParentBusinessUnitID { get; set; }

    public virtual ICollection<BusinessUnit> ChlidBusinessUnits { get; set; }
    public virtual BusinessUnit ParentBusinessUnit { get; set; }
    public virtual ICollection<UserPermissions> UserPermissions { get; set; }
}

public partial class User
{
    public User()
    {
        this.UserPermissions = new HashSet<UserPermissions>();
    }

    public int UserID { get; set; }
    public string FirstName { get; set; }

    public virtual ICollection<UserPermissions> UserPermissions { get; set; }
}

public partial class UserPermissions
{
    public int UserPermissionsID { get; set; }
    public int BusinessUnitID { get; set; }
    public int UserID { get; set; }

    public virtual BusinessUnit BusinessUnit { get; set; }
    public virtual User User { get; set; }
}

public partial class BusinessModelContainer : DbContext
{
    public BusinessModelContainer()
        : base("name=BusinessModelContainer")
    {
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        throw new UnintentionalCodeFirstException();
    }

    public DbSet<BusinessUnit> BusinessUnits { get; set; }
    public DbSet<User> Users { get; set; }
    public DbSet<UserPermissions> UserPermissions { get; set; }
}

@Chase medallion is correct in that we can't write recursive LINQ (or even Entity SQL) queries.

Option 1: Lazy Loading

With lazy loading enabled, you could do something like this...

    private static IEnumerable<BusinessUnit> UnitsForUser(BusinessModelContainer container, User user)
    {
        var distinctTopLevelBusinessUnits = (from u in container.BusinessUnits
                                             where u.UserPermissions.Any(p => p.UserID == user.UserID)
                                             select u).Distinct().ToList();

        List<BusinessUnit> allBusinessUnits = new List<BusinessUnit>();

        foreach (BusinessUnit bu in distinctTopLevelBusinessUnits)
        {
            allBusinessUnits.Add(bu);
            allBusinessUnits.AddRange(GetChildren(container, bu));
        }

        return (from bu in allBusinessUnits
                group bu by bu.BusinessUnitID into d
                select d.First()).ToList();
    }

    private static IEnumerable<BusinessUnit> GetChildren(BusinessModelContainer container, BusinessUnit unit)
    {
        var eligibleChildren = (from u in unit.ChlidBusinessUnits
                                select u).Distinct().ToList();

        foreach (BusinessUnit child in eligibleChildren)
        {
            yield return child;

            foreach (BusinessUnit grandchild in child.ChlidBusinessUnits)
            {
                yield return grandchild;
            }
        }
    }

Option 2: Pre-load Entities

However, there are some ways you could optimize this to avoid repeated trips to the server. If you have only a reasaonably small number of business units in the database you could load the entire list. Then, because of EFs ability to fix up relationships automatically, simply loading a user and his permissions from the database would give us all we need.

To clarify: this method means that you load all the BusinessUnit entities; even the ones that the user has no permissions to. However, because it greatly reduces the 'chatter' with the SQL Server, it may still perform better than Option 1 above. Unlike Option 3 below, this is 'pure' EF without any dependency on a specific provider.

        using (BusinessModelContainer bm = new BusinessModelContainer())
        {
            List<BusinessUnit> allBusinessUnits = bm.BusinessUnits.ToList();

            var userWithPermissions = (from u in bm.Users.Include("UserPermissions")
                                       where u.UserID == 1234
                                       select u).Single();

            List<BusinessUnit> unitsForUser = new List<BusinessUnit>();

            var explicitlyPermittedUnits = from p in userWithPermissions.UserPermissions
                                           select p.BusinessUnit;

            foreach (var bu in explicitlyPermittedUnits)
            {
                unitsForUser.Add(bu);
                unitsForUser.AddRange(GetChildren(bm, bu));
            }

            var distinctUnitsForUser = (from bu in unitsForUser
                                        group bu by bu.BusinessUnitID into q
                                        select q.First()).ToList();
        }

Please note that the above two examples could be improved, but serve as an example to get you going.

Option 3: Bespoke SQL Query Using Common Table Expression

If you have a large number of business units, you might want to try the most efficient method. That would be to execute custom SQL that uses a hierarchical Common Table Expression to get back the information in one hit. This would of course tie the implementation to one provider, probably SQL Server.

Your SQL would be something like this:

    WITH UserBusinessUnits
            (BusinessUnitID,
            BusinessName,
            ParentBusinessUnitID)
            AS
            (SELECT Bu.BusinessUnitId,
                    Bu.BusinessName,
                    CAST(NULL AS integer)
                    FROM Users U
                    INNER JOIN UserPermissions P ON P.UserID = U.UserID
                    INNER JOIN BusinessUnits Bu ON Bu.BusinessUnitId = P.BusinessUnitId
                    WHERE U.UserId = ?
            UNION ALL
            SELECT  Bu.BusinessUnitId,
                    Bu.BusinessName,
                    Bu.ParentBusinessUnitId
                    FROM UserBusinessUnits Uu
                    INNER JOIN BusinessUnits Bu ON Bu.ParentBusinessUnitID = Uu.BusinessUnitId)
    SELECT  DISTINCT
            BusinessUnitID,
            BusinessName,
            ParentBusinessUnitID
            FROM UserBusinessUnits

You would use code like the following to materialize a collection of BusinessUnit objects for which the user has permissions.

bm.BusinessUnits.SqlQuery(mySqlString, userId);

There is a subtle difference between the above line and the very similar code suggested by @Jeffrey. The above uses DbSet.SqlQuery() while his uses Database.SqlQuery. The latter produces entities that are not tracked by the context, while the former returns (by default) tracked entities. Tracked entities give you the ability to make and save changes, and automatic fix-up of the navigation properties. If you don't need those features, disable change tracking (either with .AsNoTracking() or by using Database.SqlQuery).

Summary

Nothing beats testing with a realistic data set to determine which method is most effective. Using hand-crafted SQL code (Option 3) is always likely to perform best, but at the expense of having more complicated code that is less portable (because it's tied to the underlying db technology).

Note also that the options available to you depend on the "flavour" of EF that you're using, and of course, on your chosen database platform. If you would like some more specific guidance that accounts for this please update your question with the extra information.

• What database do you use?
• Does you project use an EDMX file, or code first?
• If using an EDMX, do you use the default (EntityObject) code generation technique, or T4 templates?