c# - Entity Framework 遍历并返回自引用表中的子记录

Question

我正在使用 Entity Framework 并有一个 BusinessUnits 表，它可以引用相同类型的另一条记录以形成父子层次结构。

我还有一组用户和用户权限，在此表中定义的每个用户都应该有权访问 BusinessUnit 和层次结构中的所有子业务单元。用户不应访问引用的业务单元之上的业务单元(如果存在)。

我如何才能形成 LINQ 查询来处理这个自引用关系树并返回该用户有权访问的所有业务单位(包括子单位)？是否可以在一个查询中完成，或者我是否需要使用 for 循环自己手动构建树？

我已经看到模式以这种方式从节点到父节点的引用，这是否意味着我必须从最远的子节点开始才能一次由一个父节点构建树？

提前致谢

克里斯

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

好的，这里有几件事。我们可以通过向您的模型添加更多属性来使这更容易一些。那是一个选择吗？如果是这样，请将集合属性添加到实体。现在，我不知道您使用的是哪个 EF API:DbContext(代码优先或 edmx)或 ObjectContext。在我的示例中，我使用了带有 edmx 模型的 DbContext API 来生成这些类。

如果您愿意，通过一些注释，您可以省去 edmx 文件。

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 是正确的，因为我们不能编写递归 LINQ(甚至 Entity SQL)查询。

选项 1:延迟加载

启用延迟加载后，你可以做这样的事情......

    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;
            }
        }
    }

选项 2:预加载实体

但是，您可以通过一些方法对其进行优化，以避免重复访问服务器。如果数据库中的业务单位数量相当少，则可以加载整个列表。然后，由于 EF 能够自动修复关系，只需从数据库加载用户及其权限即可满足我们的所有需求。

澄清一下:此方法意味着您加载所有 BusinessUnit 实体；即使是用户没有权限的那些。但是，因为它大大减少了与 SQL Server 的“交流”，所以它的性能可能仍优于上面的选项 1。与下面的选项 3 不同，这是“纯”EF，不依赖于特定提供程序。

        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();
        }

请注意，上述两个示例可以改进，但作为一个示例让您继续前进。

选项 3:使用公用表表达式的定制 SQL 查询

如果您有大量业务单位，您可能想尝试最有效的方法。那将是执行自定义 SQL，该 SQL 使用分层公用表表达式来一次性取回信息。这当然会将实现绑定(bind)到一个提供程序，可能是 SQL Server。

你的 SQL 应该是这样的:

    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

您可以使用如下代码来具体化用户有权访问的 BusinessUnit 对象集合。

bm.BusinessUnits.SqlQuery(mySqlString, userId);

上述行与@Jeffrey 建议的非常相似的代码之间存在细微差别。以上使用 DbSet.SqlQuery()而他使用Database.SqlQuery .后者生成不被上下文跟踪的实体，而前者返回(默认情况下)跟踪的实体。跟踪的实体使您能够进行和保存更改，以及自动修复导航属性。如果您不需要这些功能，请禁用更改跟踪(使用 .AsNoTracking() 或使用 Database.SqlQuery )。

总结

没有什么比使用实际数据集进行测试更能确定哪种方法最有效了。使用手工编写的 SQL 代码(选项 3)总是可能表现最佳，但代价是代码更复杂，可移植性更差(因为它与底层数据库技术相关)。

另请注意，您可以使用的选项取决于您使用的 EF 的“风格”，当然还取决于您选择的数据库平台。如果您需要一些更具体的指导来说明这一点，请使用额外信息更新您的问题。

• 您使用什么数据库？
• 您的项目是使用 EDMX 文件还是先编码？
• 如果使用 EDMX，您是使用默认的 (EntityObject) 代码生成技术，还是使用 T4 模板？