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
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;
}
}