How can I return data in this specific way

Question

How can I return data in this specific way

c# dapper json repository-pattern

Question

I am building an angularjs app with a c# backend with dapper micro orm which gets me data from the database.

I wanted the data returned to look like this:

[{
        "CategoryId": 1,
        "CategoryName": "cat1",
        "Items": [{
            "ItemId": 1,
            "ItemName": "Item1"
        }, {
            "ItemId": 2,
            "ItemName": "Item2"
        }]
    }, {
        "CategoryId": 2,
        "CategoryName": "cat2",
        "Items": [{
            "ItemId": 3,
            "ItemName": "Item3"
        }, {
            "ItemId": 4,
            "ItemName": "Item4"
        }]
    }
]

but this is what my data looks like

[{
        "CategoryId": 1,
        "CategoryName": "cat1",
        "Items": {
            "ItemId": 1,
            "ItemName": "Item1"
        }
    }, {
        "CategoryId": 1,
        "CategoryName": "cat1",
        "Items": {
            "ItemId": 2,
            "ItemName": "Item2"
        }
    },


    {
        "CategoryId": 2,
        "CategoryName": "cat2",
        "Items": {
            "ItemId": 3,
            "ItemName": "Item3"
        }
    },

    {
        "CategoryId": 2,
        "CategoryName": "cat2",
        "Items": {
            "ItemId": 4,
            "ItemName": "Item4"
        }
    }

]

This is what I have in my repository:

public IEnumerable<CategoryModel> GetAllCategories()
        {
            using (var conn = ConnectionSettings.GetSqlConnection())
                  {
                      const string sql = @" SELECT
                            c.CategoryName,     
                            c.CategoryId,
                            i.ItemId,
                            i.ItemName,                                
                            i.CategoryId 
                        from Category c
                        INNER JOIN item i ON  c.CategoryId = i.CategoryId";   


    var categoriesList = conn.Query<CategoryModel, ItemModel, CategoryModel>(sql, (cat, it) =>
                              {
                                  cat.Item = it;
                                  return cat;
                              }, splitOn: "ItemId");

                              return categoriesList;


                          }
        }

And these are my Category and Item Models

 public class CategoryModel
    {

        public int CategoryId { get; set; }
        public string CategoryName { get; set; }

        public ItemModel Item { get; set; }

    }


public class ItemModel
    {
        public int ItemId { get; set; }
        public int CategoryId { get; set; }
        public string ItemName { get; set; }

    }

Could someone please point me in the right direction?

Please let me know what I am doing wrong here.

Thanks in advance

aliaz

View more on Stack Overflow

Mrinal Kamboj · Accepted Answer · 3/31/2016 6:54:48 AM

I would suggest a following approach to achieve the expected result:

Modify the entities as shown below, use Newtonsoft Json attributes to ignore the CategoryId in the ItemModel, to avoid the serialization

[JsonObject]
public class CategoryModel
    {

        public int CategoryId { get; set; }

        public string CategoryName { get; set; }

        public IEnumerable<ItemModel> Items { get; set; }

    }

[JsonObject]
public class ItemModel
    {
        public int ItemId { get; set; }

        [JsonIgnore] 
        public int CategoryId { get; set; }

        public string ItemName { get; set; }

    }

Now for fetching data, since its one to many mapping inside the Category Model, which contains multiple Item Model, use the QueryMultiple to fetch both the result sets separately, code will look like:

public IEnumerable<CategoryModel> GetAllCategories()
        {
            using (var conn = ConnectionSettings.GetSqlConnection())
                  {
                      const string sql = @" SELECT
                            c.CategoryName,     
                            c.CategoryId from Category c;
                            SELECT
                            i.ItemId,
                            i.ItemName,                                
                            i.CategoryId 
                        from item i";   


    var reader = conn.QueryMultiple(sql);

    IEnumerable<CategoryModel> categoriesList = reader.Read<CategoryModel>();

     IEnumerable<ItemModel> itemList = reader.Read<ItemModel>();


    foreach(Category c in categoriesList)
    {
       c.items = itemList.Where(i => i.CategoryId = c.CategoryId)
    }

    return categoriesList;


                          }
        }

How can I return data in this specific way

Question

Fastest Dapper Plus Extensions

Accepted Answer

Performance

Expression Evaluator