Self-referencing parent/child table

[michaelswells]

Describe the question

I am trying to have a self referencing table populate my class using the Auto mapping feature on my Interface. I am NOT generating a treeview (Although I might later).

What am I doing incorrect? Am I missing something?

I have a table (simplified):
skill_group_id is identity
parent_group_id is foreign key and references skill_group_id

skill_group_id	some_other_id	skill_group_name	parent_group_id
1000000	24	Group 00	null
1000001	14	Group 01	1000000
1000009	11	Group 09	1000010
1000010	19	Group 10	1000000
1000011	12	Group 11	1000001

Steps to reproduce (if applicable)

class SkillGroup
{
  [Column("skill_group_id ")]
  int SkillGroupId {get; set;}
  
  [Column("some_other_id ")]
  int SomeOtherId {get; set;}
  
  [Column("skill_group_name ")]
  string SkillGroupName {get; set;}
  
  [Column("parent_group_id ")]
  int ParentGroupId {get; set;}
  
  SkillGroup ParentGroup {get;set;}
}

Interface Repo (various permutations commented out; None produce the desired result)

    [Recordset(0, typeof(SkillGroup))]
    //[Recordset(1, typeof(SkillGroup))]
    //[Recordset(1, typeof(SkillGroup), IsChild = true)]
    //[Recordset(1, typeof(SkillGroup), IsChild = true, Id = "SkillGroupId")]
    //[Recordset(1, typeof(SkillGroup), IsChild = true, Id = "SkillGroupId", GroupBy = "ParentGroupId", Into = "ParentGroup")]
    //[Recordset(1, typeof(SkillGroup), IsChild = true, Id = "SkillGroupId", GroupBy = "ParentGroupId")]
    //[Recordset(1, typeof(SkillGroup), IsChild = true, GroupBy = "ParentGroupId", Into = "ParentGroup")]
    [Recordset(1, typeof(SkillGroup), IsChild = true, Id = "SkillGroupId", Into = "ParentGroup")]

    [Sql(@"
        select [sg].*
        from [dbo].[SkillGroup] as [sg]
        where  isnull([sg].[parent_group_id], 0) = coalesce(@parent_group_id, [sg].[parent_group_id], 0);

        select distinct
               [sg].[skill_group_id],
               [pg].*
        from   [dbo].[SkillGroup] as [sg]
               inner join [dbo].[SkillGroup] as [pg] on [sg].[parent_group_id] = [pg].[skill_group_id]
        where  isnull([sg].[parent_group_id], 0) = coalesce(@parent_group_id, [sg].[parent_group_id], 0);
        ")]
    Task<IList<SkillGroup>> GetSkillGroupsAsync(int? parent_group_id = null);

I would like to see this after executing my call to the repo:

SkillGroupId	SomeOtherId	SkillGroupName	ParentGroupId	ParentGroup
1000000	24	Group 00	null	null
1000001	14	Group 01	100000	{SkillGroupId: 1000000,SomeOtherId: 24,SkillGroupName: "Group 00", ParentGroupId: null, ParentGroup: null}
1000009	11	Group 09	100010	{SkillGroupId: 1000010,SomeOtherId: 19,SkillGroupName: "Group 10", ParentGroupId: 100000, ParentGroup: {SkillGroupId: 1000000,SomeOtherId: 24,SkillGroupName: "Group 00", ParentGroupId: null, ParentGroup: null}}
1000010	19	Group 10	100000	{SkillGroupId: 1000000,SomeOtherId: 24,SkillGroupName: "Group 00", ParentGroupId: null, ParentGroup: null}
1000011	12	Group 11	100001	{SkillGroupId: 1000001,SomeOtherId: 14,SkillGroupName: "Group 01", ParentGroupId: null, ParentGroup: null}

What I actually see after calling the repo: (ParentGroupId and ParentGroup are incorrect)

SkillGroupId	SomeOtherId	SkillGroupName	ParentGroupId	ParentGroup
1000000	24	Group 00	null	null
1000001	14	Group 01	100001	{SkillGroupId: 100001, SomeOtherId: 24, SkillGroupName: "Group 00", ParentGroupId: , ParentGroup: {}}
1000009	11	Group 09	100009	{SkillGroupId: 100009, SomeOtherId: 19, SkillGroupName: "Group 10", ParentGroupId: 100000, ParentGroup: {}}
1000010	19	Group 10	100010	{SkillGroupId: 100010, SomeOtherId: 24, SkillGroupName: "Group 00", ParentGroupId: , ParentGroup: {}}
1000011	12	Group 11	100011	{SkillGroupId: 100011, SomeOtherId: 14, SkillGroupName: "Group 01", ParentGroupId: 100000, ParentGroup: {}}

• Dotnet version: netcoreapp3.1
• Database: SQL Server 2012
• Library version: Insight.Database 6.3.0

[michaelswells]

[Solved] This morning I decided to try out #378 (Better Support for Parent/Child Records) not assuming it would work for my scenario. It worked!

[Recordset(0, typeof(SkillGroup))]
[Sql(@"
        select [sg].*,
               [pg].*
        from [dbo].[SkillGroup] as [sg]
               left join [dbo].[SkillGroup] as [pg] on [sg].[parent_group_id] = [pg].[skill_group_id]
        where  isnull([sg].[parent_group_id], 0) = coalesce(@parent_group_id, [sg].[parent_group_id], 0);
        ")]
    Task<IList<SkillGroup>> GetSkillGroupsAsync(int? parent_group_id = null);

This is what I got:

SkillGroupId	SomeOtherId	SkillGroupName	ParentGroupId	ParentGroup
1000000	24	Group 00	null	null
1000001	14	Group 01	100000	{SkillGroupId: 1000000,SomeOtherId: 24,SkillGroupName: "Group 00", ParentGroupId: null, ParentGroup: null}
1000009	11	Group 09	100010	{SkillGroupId: 1000010,SomeOtherId: 19,SkillGroupName: "Group 10", ParentGroupId: 100000, ParentGroup: null (not correct, but I do not need to resolve any recursion)
1000010	19	Group 10	100000	{SkillGroupId: 1000000,SomeOtherId: 24,SkillGroupName: "Group 00", ParentGroupId: null, ParentGroup: null}
1000011	12	Group 11	100001	{SkillGroupId: 1000001,SomeOtherId: 14,SkillGroupName: "Group 01", ParentGroupId: null, ParentGroup: null}

[jonwagner]

I'm glad that you found a solution that works. For 1-1 parent/child, it's probably better to do the single query.

But if you wanted to go back to your original question...

When querying Parent/Child with multiple recordsets, the child property probably would need to be a collection/list, so Insight could insert multiple records into the parent. (I say probably because there is a good chance that it can detect that it's not a list and then expect single records. I tend to forget all of the magic pieces...)