Entity framework.. self referencing table.. get records of Depth =x?

Question

I am successfully using a self referencing table in entity framework. But I can't figure out how to get the records of the desired depth ?

What should be the logic for this ?

Model :

public class FamilyLabel
{
    public FamilyLabel()
    {
        this.Children = new Collection<FamilyLabel>();
        this.Families = new Collection<Family>();
    }

    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int FamilyLabelId { get; set; }
    public string FamilyLabelName { get; set; }

    public virtual FamilyLabel Parent { get; set; }

    public int JamaatId { get; set; }
    public virtual Jamaat Jamaat { get; set; }

    public virtual ICollection<Family> Families { get; set; }
    public virtual ICollection<FamilyLabel>  Children { get; set; }
}

Your question is confused. If you are working in C# you have objects, not tables. — Dale M, Mar 26 '13 at 10:25
@DaleM Why is it confusing ? I am simply asking that I need to know the depth of a record.. — Bilal Fazlani, Mar 26 '13 at 10:41
Maybe you can show code how you populate a hierarchy of your objects, because I think there is the answer to your question. — Gert Arnold, Mar 26 '13 at 11:14
@GertArnold I posted the model code. It has two helpful properties right now ... i.e. "parent" & "Children". Can we create a function , which given a LabelId, return's its depth ? — Bilal Fazlani, Mar 26 '13 at 11:27

score 4 · Accepted Answer · edited Sep 26 '15 at 19:47

Theoretically you can create a method that builds query expression dynamically based on the specified depth level:

context.FamilyLabels.Where(x => 
    x.Parent. ... .Parent != null &&
    x.Parent.Parent ... .Parent == null);

The following implementation does the trick:

public static IList<FamilyLabel> Get(DbConnection connection, int depth)
{
    var p = Expression.Parameter(typeof(FamilyLabel));
    Expression current = p;

    for (int i = 0; i < deep; i++)
    {
        current = Expression.Property(current, "Parent");
    }

    var nullConst = Expression.Constant(null, typeof(FamilyLabel));

    var predicate = Expression.Lambda<Func<FamilyLabel, bool>>(
        Expression.AndAlso(
            Expression.NotEqual(current, nullConst),
            Expression.Equal(Expression.Property(current, "Parent"), nullConst)), p);

    using (MyDbContext context = new MyDbContext(connection))
    {
        return context.FamilyLabels.Where(predicate).ToList();
    }
}

However, presumably this will create a bunch of join expressions, so maybe this is not the most optimal way.

Does this get translated to T-SQL? – Shimmy Weitzhandler Sep 26 '15 at 19:59 — Shimmy Weitzhandler, Sep 26 '15 at 19:59

Entity framework.. self referencing table.. get records of Depth =x?

1 Answers1

Linked