none
self joining Parent Child relationship RRS feed

  • Question

  • Hi,
    
    Before
    
    
    ProgramBudget_SK	ParentBudget_SK		Phase_SK	Task_SK		BudgetParameter_SK
    43			55			NULL		NULL		6
    55			NULL			6		29		NULL
    61			58			NULL		NULL		3
    58			NULL			6		13		NULL
    59			58			NULL		NULL		3
    60			58			NULL		NULL		3
    
    
    After using Linq:
    ProgramBudget_SK	ParentBudget_SK		Phase_SK	Task_SK		BudgetParameter_SK
    55			NULL			6		29		NULL
    43			55			NULL		NULL		6
    58			NULL			6		13		NULL
    59			58			NULL		NULL		3
    60			58			NULL		NULL		3
    61			58			NULL		NULL		3
    
    
    How can I accomplish the above order with Linq?
    
    Thanks


    How can I accomplish the above order with Linq?

    Thanks


    • Edited by tBStar Thursday, August 15, 2013 8:14 PM
    Thursday, August 15, 2013 8:12 PM

Answers

  • Hi tBStar,

    According to your reply, I don’t understand clearly what are you want to do? Based on your SQL, I tried to create database, however I can’t get the data. Based on you original said, I also would like to know what do you get that dataset. Could you provide your database schema, some data and the detail problem to us?

    Maybe below code is usefull to you:

    var context = new TestDbEntities();
                var query1 = from e1 in context.tPRProgramBudgetConfigDet
                             join e2 in context.tPRProgramBudgetConfigDet
                             on e1.ParentBudgetConfig_SK equals e2.ProgramBudgetConfigDet_SK
                             into g
                             where (e1.programbudgetconfigHdr_sk == "5") 
                             from gi in g
                             select new
                             {
                                 ProgramBudget_SK = e1.ProgramBudgetConfigDet_SK,
                                 ParentBudget_SK = e1.ParentBudgetConfig_SK,
                                 Phase_SK = e1.phase_sk == null ?(gi==null?null:gi.phase_sk): e1.phase_sk,
                                 Task_SK = e1.task_sk ??(gi==null?null:gi.task_sk),
                                 BudgetParameter_SK = e1.BudgetParameter_SK,
                                 locationcodes = e1.locationcodes
                             };
                var query2 = from det in query1
                             join p in context.tPBBudgetPhasePriority
                             on det.Phase_SK equals p.Phase_SK
                             orderby p.priority, det.Task_SK, det.ParentBudget_SK, det.BudgetParameter_SK, det.locationcodes
                             select det;

    Thanks

    Best Regards

    • Edited by Clearly09 Saturday, August 17, 2013 8:52 AM modify
    • Marked as answer by tBStar Monday, October 7, 2013 12:42 PM
    Saturday, August 17, 2013 7:58 AM

All replies

  • Hi tBStar,

    Thanks for your post!

    According to your description, I suggest that you can group the data by ParentBudget_SK and then order by ProgramBudget_SK.

    There is a simple example below about Group By and Order by:

    var context = new MessageInfoEntities();
    var u = from uu in context.UserInfoes
            group uu by uu.Id into g
            let eee=(
            from kk in g
            orderby kk.Pwd
            select kk
                    )
            Select eee;
    foreach (var groupItem in u)
     {
         \\TODO
          foreach (var item in groupItem)
          {
    	 \\TODO	
          }
     }
    

    Thanks

    Best Regards


    Starain Chen
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, August 16, 2013 2:20 PM
    Moderator
  • Thanks Starain for trying. Unfortunately, it didn't work. It was returning only the parent records.

    This is the sql that I got close to what I am looking for:

    select det.* from
    (
    SELECT e1.ProgramBudgetConfigDet_SK SK, ISNULL(e1.phase_sk, e2.phase_sk) Phase_SK, ISNULL(e1.task_sk, e2.task_sk) Task_SK, e1.ParentBudgetConfig_SK Parent_SK, e1.BudgetParameter_SK Parameter, e1.locationcodes
    FROM tPRProgramBudgetConfigDet e1 left JOIN
    tPRProgramBudgetConfigDet e2 ON e1.ParentBudgetConfig_SK = e2.ProgramBudgetConfigDet_SK 
    where e1.programbudgetconfigHdr_sk=5
    ) as det inner join tPBBudgetPhasePriority p ON p.Phase_SK = det.Phase_SK
    order by p.priority, det.task_SK, det.Parent_SK, det.Parameter, det.locationcodes

    Friday, August 16, 2013 8:15 PM
  • Hi tBStar,

    According to your reply, I don’t understand clearly what are you want to do? Based on your SQL, I tried to create database, however I can’t get the data. Based on you original said, I also would like to know what do you get that dataset. Could you provide your database schema, some data and the detail problem to us?

    Maybe below code is usefull to you:

    var context = new TestDbEntities();
                var query1 = from e1 in context.tPRProgramBudgetConfigDet
                             join e2 in context.tPRProgramBudgetConfigDet
                             on e1.ParentBudgetConfig_SK equals e2.ProgramBudgetConfigDet_SK
                             into g
                             where (e1.programbudgetconfigHdr_sk == "5") 
                             from gi in g
                             select new
                             {
                                 ProgramBudget_SK = e1.ProgramBudgetConfigDet_SK,
                                 ParentBudget_SK = e1.ParentBudgetConfig_SK,
                                 Phase_SK = e1.phase_sk == null ?(gi==null?null:gi.phase_sk): e1.phase_sk,
                                 Task_SK = e1.task_sk ??(gi==null?null:gi.task_sk),
                                 BudgetParameter_SK = e1.BudgetParameter_SK,
                                 locationcodes = e1.locationcodes
                             };
                var query2 = from det in query1
                             join p in context.tPBBudgetPhasePriority
                             on det.Phase_SK equals p.Phase_SK
                             orderby p.priority, det.Task_SK, det.ParentBudget_SK, det.BudgetParameter_SK, det.locationcodes
                             select det;

    Thanks

    Best Regards

    • Edited by Clearly09 Saturday, August 17, 2013 8:52 AM modify
    • Marked as answer by tBStar Monday, October 7, 2013 12:42 PM
    Saturday, August 17, 2013 7:58 AM