How to do a recursive query in EF ? RRS feed

  • Question

  • Table Provice Structure:

    ParentItemKey: string



    void Main()
    	var item = Provices.Where(p=>p.ItemValue == "Xi'an");
    IQueryable<Provice> GetItemTree(IQueryable<Provice> parentList)
    	var children = Provices.Where(item=> parentList.Any(p=>p.ItemKey == item.ParentItemKey));
    	return children.Union(GetItemTree(children));
    When it runs, the linqpad crashed. Sorry for my poor English. Please help me.

    Tuesday, August 8, 2017 3:55 AM

All replies

  • I want the generated SQL is only one sql statement, is it possible ?
    Tuesday, August 8, 2017 3:57 AM
  • Hi 沧海一杰,

    Thank you for posting the problem here.

    >> How to do a recursive query in EF

    For your problem, I suppose that you would like to select all of information under Xi’an by recursive query.

    For instance, I created demo which  traverses all sub-inforamtion under Xi’an according to your code, please take a reference.

    class Program
            public static IQueryable<Provice> Provices;
            static void Main(string[] args)
                IEnumerable<Provice> provices = new Provice[] {
                    new Provice { ParentItemKey="China",ItemKey="1",ItemValue="Xi'an"},
                    new Provice { ParentItemKey="China",ItemKey="2",ItemValue="Bei'jing"},
                    new Provice { ParentItemKey="China",ItemKey="3",ItemValue="Shang'hai"},
                   new Provice { ParentItemKey="1",ItemKey="1",ItemValue="a"},
                    new Provice { ParentItemKey="1",ItemKey="2",ItemValue="b"},
                    new Provice { ParentItemKey="2",ItemKey="3",ItemValue="c"},
                    new Provice { ParentItemKey="2",ItemKey="4",ItemValue="d"},
                    new Provice { ParentItemKey="3",ItemKey="5",ItemValue="e"},
      new Provice { ParentItemKey="3",ItemKey="6",ItemValue="f"}
                Provices = provices.AsQueryable();
                Provice provice = new Provice();
                var item = Provices
                    .Where(p => p.ParentItemKey == "1");
            public static IQueryable<Provice> GetItemTree(IQueryable<Provice> parentList)
                var children = Provices.Where(item => parentList.Any(p => p.ItemKey == item.ParentItemKey));
                return children.Union(GetItemTree(children));
           public static void HieararchyWalk(IQueryable<Provice> parentList)
                if (parentList != null)
                    foreach (var item in parentList)
                        Console.WriteLine(string.Format("{0} {1}", item.ItemKey, item.ItemValue));
        public class Provice
            public string ParentItemKey { set; get; }
            public string ItemKey { set; get; }
            public string ItemValue { set; get; }
            public IQueryable<Provice> Children { set; get; }

    I’m glad to be of help to you.

    Best Regards,


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact

    Wednesday, August 9, 2017 7:14 AM