-1

I'd like to select only child below CFO and its subchild entities in an Entity Framework select statement.

Here's my table:

+-------+------------+----------+
| OrgId | Name       | ParentId |
+-------+------------+----------+
| 1     | COO        |          |
+-------+------------+----------+
| 2     | CFO        |          |
+-------+------------+----------+
| 3     | Accountant | 2        |
+-------+------------+----------+
| 4     | Bookkeeper | 3        |
+-------+------------+----------+
| 5     | Controller | 2        |
+-------+------------+----------+
| 6     | Operations | 1        |
+-------+------------+----------+

I'd like to select only this:

+-------+------------+----------+
| OrgId | Name       | ParentId |
+-------+------------+----------+
| 3     | Accountant | 2        |
+-------+------------+----------+
| 4     | Bookkeeper | 3        |
+-------+------------+----------+
| 5     | Controller | 2        |
+-------+------------+----------+

The entity framework select:

public virtual IList<OrgStructureModel> GetAll()
{
    using (var db = _context)
    {
        var result = _session.GetObjectFromJson<IList<OrgStructureModel>>("OrgStructure");

        if (result == null)
        {
            result = db.OrgStructures
                .Select(org => org.ToOrgStructureModel(db.OrgStructures.Where(s => s.ParentId == org.OrgId).Count() > 0))
                .ToList();

            _session.SetObjectAsJson("OrgStructure", result);
        }

        return result;
    }
}

How can this be achieved in EF?

Here's what I've tried I've tried testing to just show child with any parent .Where(e => e.ParentId != null):

            result = db.OrgStructures
                .Select(org => org.ToOrgStructureModel(db.OrgStructures.Where(s => s.ParentId == org.OrgId).Count() > 0))
                .Where(e => e.ParentId != null)
                .ToList();

But this returned 0 results

Definition of ToOrgStructureModel:

public static OrgStructureModel ToOrgStructureModel(this OrgStructure org, bool hasChildren)
{
    return new OrgStructureModel
    {
        OrgId = org.OrgId,
        ParentId = org.ParentId,
        Name = org.Name
        hasChildren = hasChildren
    };
}

Update: It looks like something's wrong with the Telerik TreeList Control where the above query has data, but the control won't output the data. But the question remains, how do I get OrgId: 3,4,5 with LINQ?

sojim2
  • 1,245
  • 2
  • 15
  • 38
  • can use join method of LINQ – Navjyot Mar 18 '19 at 17:34
  • If anyone has an example, it would be great, thanks! – sojim2 Mar 18 '19 at 17:52
  • Is this not where parentid == null then? – Vidmantas Blazevicius Mar 18 '19 at 19:59
  • 1
    If I choose `OrgId: 2` should I get back `OrgId: 3, 5` or `OrgId: 3, 4, 5`? The first answer is easy, the second one is impossible because it requires a [Recursive CTE which Entity-Framework does not support (linq nor lambda)](https://stackoverflow.com/questions/11929535/writing-recursive-cte-using-entity-framework-fluent-syntax-or-inline-syntax). – Erik Philips Mar 18 '19 at 20:30
  • @ErikPhilips should get back `OrgId: 3,4,5`. After your edit: I see so stored proc it is! – sojim2 Mar 18 '19 at 20:32

1 Answers1

-1

Only bring back the results where the ParentId is not null.

kingtreelo
  • 251
  • 3
  • 15