各位好,目前正在撰寫一支API碰到了一些問題想詢問各位大大可能的解法
這是我的Model

public class TestDto
    {
        public string CUSIDN { get; set; }
        public List<Zero> Zero { get; set; }
    }
    public class GUR
    {
        public string ITEMS { get; set; }
        public string NAME { get; set; }
    }
    public class Zero
    {
        public string CUSIDN { get; set; }
        public string ITMES { get; set; }
        public string ACN { get; set; }
        public List<GUR> GUR { get; set; }
    }

這是我的DB欄位資料內容分別擁有Test Table 和GUR Table
![]

依照條件我進行了GURKEY的left join

select test.CUSIDN,test.ITMES,test.ACN,GUR.ITEMS,GUR.NAME
from test
left join GUR
on test.GURKEY = GUR.GURKEY
where CUSIDN='F123456789'

最後我想將DB資料最後變成這樣的Json內容

{
    "CUSIDN":"F123456789",
    "Zero": [
        {
            "CUSIDN": "F123456789",
            "ITMES": "測試",
            "ACN": "1234556",
            "GUR": [
                {
                    "ITEMS": "123456",
                    "NAME": "張OO"
                },
                {
                    "ITEMS": "asd",
                    "NAME": "測試OO"
                }
            ]
        },
        {
            "CUSIDN": "F123456789",
            "ITMES": "測試二",
            "ACN": "1235323",
            "GUR": [
                {
                    "ITEMS": "23456",
                    "NAME": "文OO"
                }
            ]
        },
        {
            "CUSIDN": "F123456789",
            "ITMES": "測試三",
            "ACN": "1235323",
            "GUR": [
                {
                    "ITEMS": "123456",
                    "NAME": "張OO"
                },
                {
                    "ITEMS": "asd",
                    "NAME": "測試OO"
                }
            ]
        },

    ]
}

目前我寫了一個程式

        public static IEnumerable<TestDto> TestSlapper()
        {
            using (SqlConnection conn = new SqlConnection(GetDBConnectionString()))
            {
                string sql = @"
                select test.CUSIDN,test.CUSIDN AS Zero_CUSIDN,test.ITMES as Zero_ITMES,test.ACN as Zero_ACN,GUR.ITEMS as Zero_GUR_ITEMS,
                GUR.NAME as Zero_GUR_NAME
                from test
                left join GUR
                on test.GURKEY = GUR.GURKEY
                where CUSIDN='F123456789';
                ";
                var data = conn.Query<dynamic>(sql);

                var TestList = Slapper.AutoMapper.MapDynamic<TestDto>(data, false).ToList();
                return TestList;
            }
        }

這是我得到的結果

[
    {
        "CUSIDN": "F123456789",
        "Zero": [
            {
                "CUSIDN": "F123456789",
                "ITMES": "測試        ",
                "ACN": "1234556   ",
                "GUR": [
                    {
                        "ITEMS": "123456    ",
                        "NAME": "張OO       "
                    }
                ]
            }
        ]
    },
    {
        "CUSIDN": "F123456789",
        "Zero": [
            {
                "CUSIDN": "F123456789",
                "ITMES": "測試        ",
                "ACN": "1234556   ",
                "GUR": [
                    {
                        "ITEMS": "asd       ",
                        "NAME": "測試OO      "
                    }
                ]
            }
        ]
    },
    {
        "CUSIDN": "F123456789",
        "Zero": [
            {
                "CUSIDN": "F123456789",
                "ITMES": "測試二       ",
                "ACN": "1245693   ",
                "GUR": [
                    {
                        "ITEMS": "23456     ",
                        "NAME": "文OO       "
                    }
                ]
            }
        ]
    },
    {
        "CUSIDN": "F123456789",
        "Zero": [
            {
                "CUSIDN": "F123456789",
                "ITMES": "測試三       ",
                "ACN": "1235323   ",
                "GUR": [
                    {
                        "ITEMS": "123456    ",
                        "NAME": "張OO       "
                    }
                ]
            }
        ]
    },
    {
        "CUSIDN": "F123456789",
        "Zero": [
            {
                "CUSIDN": "F123456789",
                "ITMES": "測試三       ",
                "ACN": "1235323   ",
                "GUR": [
                    {
                        "ITEMS": "asd       ",
                        "NAME": "測試OO      "
                    }
                ]
            }
        ]
    }
]

這並不是我想要的結果
目前是有想說還是用Dapper的QueryMultiple來處理
但是會變成我必須先去select test Table的CUSIDN和ACN和GURKEY並存放到另外一個Model
再針對這個Model去做Foreach再去做塞值的結果
想請問各位大大是否有更好的做法呢?