How can I achieve mentioned Json output from Linq EF in C#

Question

I'm new to ASP.NET MVC and trying to get below Json output of my Linq query of two tables:

{
  "data": [
    {
      "product_id": "1",
      "name": "Product 1",
      "image": "/Conten/product_image/1.jpg",
      "attrs": [
        {
          "id": "1",
          "weight": "500 Grams",
          "price": "1.300",
          "old_price": "1.300",
          "qty": "5"
        },
        {
          "id": "3",
          "weight": "1KG",
          "price": "1.300",
          "old_price": "1.300",
          "qty": "5"
        }
      ]
    },
    {
      "product_id": "2",
      "name": "Product 2",
      "image": "/Conten/product_image/2.jpg",
      "attrs": [
        {
          "id": "5",
          "weight": "250 Grams",
          "price": "0.700",
          "old_price": "0.700",
          "qty": "2"
        },
        {
          "id": "6",
          "weight": "2 KG",
          "price": "1.300",
          "old_price": "1.300",
          "qty": "1"
        }
      ]
    }
  ]
}

I have two tables

1. tblProducts with columns product_id, name, main_image
2. tblProduct_attr with id, product_id, attr_value, price, old_price, qty

tblProduct_attr contains multiple attributes of 1 product.

This is my Linq code to join those two tables:

var _products = (from p in db.tblProducts
                 join a in db.tblProduct_attr on p.product_id equals a.product_id
                 select new
                 {
                     product_id = p.product_id,
                     name = p.name,
                     image = p.main_image,

                     id = a.id,
                     weight = a.attr_value,
                     price = a.price,
                     old_price = a.old_price,
                     qty = a.qty
                 }).ToList();

Answer 1

Don't use an explicit join, set up a navigation property relationship and let EF manage the join and re-composing the desired projection. The issue with a JOIN is that it results in a Cartesian Product, basically a flattened version of the relationship where if each product has several attributes you get a row per each attribute with the product details appended flattened in single rows, where you want to display the related hierarchy.

Your Product entity should have a collection of Attributes mapped to the Product_Attr table and joined on the ProductId in both tables.

public virtual ICollection<ProductAttribute> Attributes { get; } = [];

From there we can project the desired output:

var products = db.tblProducts
    .AsNoTracking() // recommended for read operations to avoid querying tracking cache.
    .Select(p => new
    {
         product_id = p.product_id,
         name = p.name,
         image = p.main_image,
         attrs = p.Attributes.Select(a => new 
         {
             id = a.id,
             weight = a.attr_value,
             price = a.price,
             old_price = a.old_price,
             qty = a.qty
         }).ToList()
   }).ToList();

Behind the scenes EF will create the Cartesian but re-constitute the relationships into the desired projection. You can also consider adding an .AsSplitQuery() to remove the Cartesian entirely, executing two queries to build the products and attributes.