How to return nested, one to many relationship in a ASP.NET MVC json response?

Question

How would you format a Json response to include nested, one to many related data?

My simple JQuery Autocomplete example. The Linq2Sql is within. The first part of this question answered here. This uses a repository with Linq 2 SQL to send the response:

public IQueryable GetProductIDs(string myPrefix, int limit)
{
    return from z in db.Products
           where z.ItemNo.StartsWith(myPrefix)
           select new { id = z.ItemNo, name = z.DetailText, **** }; 
           //, This is where I need to assemble about 4 related product quantities
           //  like Qty1: 5, PricePer: $3, Qty2: 10, PricePer: $2, Qty3: 25, PricePer: $1
}

It returns a json object:

public ActionResult autocomplete(string q, int limit)
{
    var jsonData = plantRepository.GetProductIDs(q, limit);
    return Json(jsonData);
}

This currently returns data parse-able with JavaScript:

parse: function(data) {
    var rows = new Array();
    for( var i = 0; i<data.length; i++)
    {   rows[i] = {data:data[i], value:data[i].name, result:data[i].id }; }
    return rows;}

So how to you format the Linq 2 SQL to return Json that can be parsed like data[i].price[1].qty, data[i].price[1].pricePer? (or simply add a multi-part object in the initializer?)

I hope this makes sense. I provided all the information to provide a context because the question in itself didn't make sense to me.

Answer 1

You need your projected object to include a property "price" so when it's serialized to json you can do like you quoted:

data[i].price[1].qty

Change your linq query to something like this:

public IQueryable GetProductIDs(string myPrefix, int limit)
{
    return from prod in db.Products
           where prod.ItemNo.StartsWith(myPrefix)
           select new { id = prod.ItemNo, name = prod.DetailText, price = from pce in db.Price where pce.ItemNo equals prod.ItemNo select pce }; 
}

.. i haven't checked the syntax of the above but hopefully you get the idea. This way your anon object should have a property id and name (as before) and a collection called price. You could even go further to project the parts of price you want like this:

public IQueryable GetProductIDs(string myPrefix, int limit)
{
    return from prod in db.Products
           where prod.ItemNo.StartsWith(myPrefix)
           select new { id = prod.ItemNo, name = prod.DetailText, price = from pce in db.Price where pce.ItemNo equals prod.ItemNo select new { qty = pce.qty, pricePer = pce.pricePer }}; 
}

.. but i dont think that's necessary as the names are the same. Pls forgive my bad formatting.