I'm trying to return SQL Server data using Dapper ORM that had nested object, so far here is what I'm doing

Model class

public class CustomerAll
{
    public string KODELANG { get; set; }
    public string GRUPLANG { get; set; }
    public string NAMALANG { get; set; }
    public string ALAMAT { get; set; }
    public string STATUS { get; set; }
    public List<NoDiscount> DISC_ACTIVE  { get; set; }
}

public class NoDiscount
{
    public string NO_DISC { get; set; }
    public List<ProductDiscount> LIST_PRODUCT{ get; set; }
}

public class ProductDiscount
{
    public string KODEPROD{ get; set; }
}

Raw query

SELECT CUSTOMER.KODELANG
      ,CUSTOMER.GRUPLANG
      ,CUSTOMER.NAMALANG
      ,CUSTOMER.ALAMAT
      ,CUSTOMER.STATUS
      ,DISCOUNT.NO_DISC
      ,PRODUCT.KODEPROD
  FROM CUST_TABLE CUSTOMER, DCNT_TABLE DISCOUNT, PRODUCT_TABLE PRODUCT
 WHERE     CUSTOMER.KODELANG = DISCOUNT.KODELANG
       AND DISCOUNT.NO_DISC = PRODUCT.NO_DISC
       AND CUSTOMER.KODELANG = @CustNum

Controller class

var parameters = new { CustNum = customerNumber };
var lookup = new Dictionary<string, CustomerAll>();

con.Query<CustomerAll, NoDiscount, ProductDiscount, CustomerAll>(
    querySql,
    (c, n, p) =>
    {
        CustomerAll customer;

        if (!lookup.TryGetValue(c.KODELANG, out customer))
        {
            lookup.Add(c.KODELANG, customer = c);
        }
        if (customer.DISC_ACTIVE == null)
        {
            customer.DISC_ACTIVE = new List<NoDiscount>();
        }
        if (p != null)
        {
            if (n.LIST_PRODUCT == null)
            {
                n.LIST_PRODUCT = new List<ProductDiscount>();
            }
            n.LIST_PRODUCT.Add(p);
        }
        customer.DISC_ACTIVE.Add(n);
        return customer;
    }
    ,parameters
    ,splitOn: "KODELANG, NO_DISC, KODEPROD");

return Ok(lookup.Values);

Here is what response returned

[
    {
        "KODELANG": "101308",
        "GRUPLANG": "22",
        "NAMALANG": "Name Example",
        "ALAMAT": "Street number 4",
        "STATUS": "A",
        "DISC_ACTIVE": [
            {
                "NO_DISC": "DISC/1021/0001",
                "LIST_PRODUCT": [
                    {
                        "KODEPROD": "TLBCA"
                    }
                ]
            },
            {
                "NO_DISC": "DISC/1021/0001",
                "LIST_PRODUCT": [
                    {
                        "KODEPROD": "TLBCB"
                    }
                ]
            },
            {
                "NO_DISC": "DISC/3304/0009",
                "LIST_PRODUCT": [
                    {
                        "KODEPROD": "ZVKAA"
                    }
                ]
            }
        ]
    }
]

What i'm trying to had is to be returned like these

[
    {
        "KODELANG": "101308",
        "GRUPLANG": "22",
        "NAMALANG": "Name Example",
        "ALAMAT": "Street number 4",
        "STATUS": "A",
        "DISC_ACTIVE": [
            {
                "NO_DISC": "DISC/1021/0001",
                "LIST_PRODUCT": [
                    {
                        "KODEPROD": "TLBCA"
                    },
                    {
                        "KODEPROD": "TLBCB"
                    },
                    {
                        "KODEPROD": "TLBCC"
                    }
                ]
            },
            {
                "NO_DISC": "DISC/3304/0001",
                "LIST_PRODUCT": [
                    {
                        "KODEPROD": "ZVKAA"
                    }
                ]
            }
        ]
    }
]

I don't want the LIST_PRODUCT getting added again if the NO_DISC are the same

CodePudding user response：

Try this, beware that same NoDiscount object may have duplicate copys in diffierent customers.

        var parameters = new { CustNum = customerNumber };
        var customDict = new Dictionary<string, CustomerAll>();

        con.Query<CustomerAll, NoDiscount, ProductDiscount, CustomerAll>(
            querySql,
            (c, n, p) =>
            {
                CustomerAll customer = null;

                if (customDict.ContainsKey(c.KODELANG))
                {
                    customer = customDict[c.KODELANG];
                }
                else
                {
                    var customerObj = new CustomerAll()
                    {
                        KODELANG = c.KODELAN,
                        DISC_ACTIVE = new List<NoDiscount>()
                    };

                    customDict.Add(c.KODELANG, customerObj);
                };

                NoDiscount noDiscount = customer.DISC_ACTIVE.FirstOrDefault(x => x.NO_DISC == n.NO_DISC);
                if (noDiscount ==null)
                {
                    var noDiscountObj = new NoDiscount()
                    {
                        NO_DISC = n.NO_DISC,
                        LIST_PRODUCT = new List<ProductDiscount>()
                    };
                    noDiscount = noDiscountObj;
                    customer.DISC_ACTIVE.Add(noDiscountObj);
                }

                if (p != null && !noDiscount.LIST_PRODUCT.Contains(x=>x.KODEPROD==p.KODEPROD))
                {
                    noDiscount.LIST_PRODUCT.Add(p);
                }
                return customer;
            }
            , parameters
            , splitOn: "KODELANG, NO_DISC, KODEPROD");

        return Ok(customDict.Values);