none
LINQ groud by create hierarchical object RRS feed

  • Question

  • Hi there

    Problem definition: i have a SQL table that holds parent and children serial numbers although the children can have their own children serial numbers. So potentially it can result into four layers.

    Example of query I have managed to construct

    var queryPCB = from burned in MES.CU_BURNEDBOARDS
                                       where burned.ParentSN == panelSN
                                       group burned by burned.xPcbSN;

    This query takes top level serial number and groups its children. So I can use childs key to find their own children.

    I am required: in the query above to add two more layers, so that is I can navigate to a child and then find child's children and I can navigate to child's child's chilchen. (Sorry my english isnt perfect I hope you are getting the idea?)

    Thanks

    Jonas



    • Edited by JonasLub Monday, November 18, 2019 10:13 AM grammar
    Monday, November 18, 2019 10:02 AM

Answers

  • Hi Jonas,

    Thanks for your feedback.

    For your question, you want to find four layers of data.

    As Michael said, it just need a simple recursion, this is my code.

        class Program
        {
            private static List<MyClass> resultData = new List<MyClass>();
            static void Main(string[] args)
            {
                List<MyClass> list = CreateData();
                string panelSN = "1";
    
                List<MyClass> result = SelectDatas(list, panelSN);
                foreach (var item in result)
                {
                    Console.WriteLine(item);
                }
                Console.WriteLine("Press any key to continue...");
                Console.ReadKey();
            }
            private static int layers = 1;
       
            public static List<MyClass> SelectDatas(List<MyClass> rawData, String panelSN)
            {
                //This number is the number of layers you are looking for.
                if (layers == 4)
                {
                    return resultData;
                }
                else
                {
                    var queryPCB = from burned in rawData
                                   where burned.ParentSN == panelSN
                                   group burned by burned.xPcbSN;
                    layers++;
                    foreach (var group in queryPCB)
                    {
                        resultData.AddRange(group.ToList());
                        SelectDatas(rawData, group.Key);
                    }
                }
                return resultData;
            }
            static List<MyClass> CreateData()
            {
                //I used a list to simulate your data.
                List<MyClass> list = new List<MyClass>();
                list.Add(new MyClass() { xPcbSN = "1", ParentSN = "", TiStamp = DateTime.Now });
                list.Add(new MyClass() { xPcbSN = "2", ParentSN = "1", TiStamp = DateTime.Now });
                list.Add(new MyClass() { xPcbSN = "3", ParentSN = "2", TiStamp = DateTime.Now });
                list.Add(new MyClass() { xPcbSN = "4", ParentSN = "3", TiStamp = DateTime.Now });
                list.Add(new MyClass() { xPcbSN = "5", ParentSN = "4", TiStamp = DateTime.Now });
                list.Add(new MyClass() { xPcbSN = "6", ParentSN = "5", TiStamp = DateTime.Now });
                list.Add(new MyClass() { xPcbSN = "7", ParentSN = "6", TiStamp = DateTime.Now });
                list.Add(new MyClass() { xPcbSN = "8", ParentSN = "6", TiStamp = DateTime.Now });
    
                return list;
            }
        }
        class MyClass
        {
            public String xPcbSN { get; set; }
            public String ParentSN { get; set; }
            public DateTime TiStamp { get; set; }
    
            public override string ToString()
            {
                return xPcbSN + " " + ParentSN + " " + TiStamp;
            }
        }
    

    Hope this could be helpful.

    Best Regards,

    Timon


    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 MSDNFSF@microsoft.com.

    • Marked as answer by JonasLub Thursday, November 28, 2019 1:22 PM
    Wednesday, November 20, 2019 9:50 AM

All replies

  • You could probably do this with a single query, especially if you could limit to 2 or 3 levels but I think simple recursion would be easier.

    Put all this into a loop. Use your original list of SNs for the first loop to get the children. Then run the query again but now use the child SNs as the source. Repeat until you stop getting any children back. At that point you have all the items that are directly or indirectly tied to the original SN.


    Michael Taylor http://www.michaeltaylorp3.net

    Monday, November 18, 2019 2:58 PM
    Moderator
  • Hi JonasLub,

    Thank you for posting here..

    Could you please provide some data or database table format that you have?

    This will help us write a LinQ statement that is more suitable for you.

    Looking forward to your reply.

    Best Regards,

    Timon


    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 MSDNFSF@microsoft.com.

    Tuesday, November 19, 2019 9:41 AM
  • Thank you for your help Timon,

    The table format is

    INSERT INTO [dbo].[CU_BURNEDBOARDS]
               ([xPcbSN]
               ,[ParentSN]
               ,[TiStamp]
         
              
         VALUES
               (<xPcbSN, varchar(64),>
               ,<ParentSN, varchar(64),>
               ,<TiStamp, datetime,>
               )
    GO

    Is this enough information for you?

    Thank you

    Jonas

    Tuesday, November 19, 2019 10:57 AM
  • Hi Jonas,

    Thanks for your feedback.

    For your question, you want to find four layers of data.

    As Michael said, it just need a simple recursion, this is my code.

        class Program
        {
            private static List<MyClass> resultData = new List<MyClass>();
            static void Main(string[] args)
            {
                List<MyClass> list = CreateData();
                string panelSN = "1";
    
                List<MyClass> result = SelectDatas(list, panelSN);
                foreach (var item in result)
                {
                    Console.WriteLine(item);
                }
                Console.WriteLine("Press any key to continue...");
                Console.ReadKey();
            }
            private static int layers = 1;
       
            public static List<MyClass> SelectDatas(List<MyClass> rawData, String panelSN)
            {
                //This number is the number of layers you are looking for.
                if (layers == 4)
                {
                    return resultData;
                }
                else
                {
                    var queryPCB = from burned in rawData
                                   where burned.ParentSN == panelSN
                                   group burned by burned.xPcbSN;
                    layers++;
                    foreach (var group in queryPCB)
                    {
                        resultData.AddRange(group.ToList());
                        SelectDatas(rawData, group.Key);
                    }
                }
                return resultData;
            }
            static List<MyClass> CreateData()
            {
                //I used a list to simulate your data.
                List<MyClass> list = new List<MyClass>();
                list.Add(new MyClass() { xPcbSN = "1", ParentSN = "", TiStamp = DateTime.Now });
                list.Add(new MyClass() { xPcbSN = "2", ParentSN = "1", TiStamp = DateTime.Now });
                list.Add(new MyClass() { xPcbSN = "3", ParentSN = "2", TiStamp = DateTime.Now });
                list.Add(new MyClass() { xPcbSN = "4", ParentSN = "3", TiStamp = DateTime.Now });
                list.Add(new MyClass() { xPcbSN = "5", ParentSN = "4", TiStamp = DateTime.Now });
                list.Add(new MyClass() { xPcbSN = "6", ParentSN = "5", TiStamp = DateTime.Now });
                list.Add(new MyClass() { xPcbSN = "7", ParentSN = "6", TiStamp = DateTime.Now });
                list.Add(new MyClass() { xPcbSN = "8", ParentSN = "6", TiStamp = DateTime.Now });
    
                return list;
            }
        }
        class MyClass
        {
            public String xPcbSN { get; set; }
            public String ParentSN { get; set; }
            public DateTime TiStamp { get; set; }
    
            public override string ToString()
            {
                return xPcbSN + " " + ParentSN + " " + TiStamp;
            }
        }
    

    Hope this could be helpful.

    Best Regards,

    Timon


    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 MSDNFSF@microsoft.com.

    • Marked as answer by JonasLub Thursday, November 28, 2019 1:22 PM
    Wednesday, November 20, 2019 9:50 AM
  • Timon, thanks for your help! It has worked for me with a little addaptation.
    Thursday, November 28, 2019 1:23 PM