Creating hierarchical JSON result from multiple tables using linq expression

Question

I have seen a few responses on SO dealing with PHP and JSON but using linq is throwing a wrench into what I am trying to do.

High Level: I have two views, BatchList and BatchDetails. BatchList is a view of all batches in my database while BatchDetails has a list of all the orders in a given batch.

I am trying to build a JSON structure with the following hierarchy:

BatchList.BatchNumber 1 Parent
  BatchDetails.OrderNumber 1 Children bound by related BatchNumber 1
  BatchDetails.OrderNumber 2
  BatchDetails.OrderNumber 3
BatchList.BatchNumber 2 Parent
  BatchDetails.OrderNumber 1 Children bound by related BatchNumber 2
  BatchDetails.OrderNumber 2
  BatchDetails.OrderNumber 3

I know once I build this that I can just JSON encode it and pass it to my view to display. I have seen examples like:

{
    "d" : {
        "results": [
        {
            "CategoryID": 1, 
            "CategoryName": "Beverages", 
            "Description": "Soft drinks, coffees, teas, beers, and ales", 
            "Products": {
                "results": [
                {
                    "ProductID": 1,
                    "ProductName": "Chai", 
                    "QuantityPerUnit": "10 boxes x 20 bags"
                },
                // ...
                // Product 2
                // ...
                ]
            }
        },
        // ...
        // Category 2
        // ...
        ]
    }
}

Currently the only linq expressions I have are:

public IQueryable<vwBatchList> AggregateBatchList(int coid)
{
    var contex = new LBPEntities();
    var batchList = 
        (from x in contex.vwBatchLists
        where x.CoId == coid
        select x);
    return batchList;
}

Which I can then encode the results and pass to a grid.

But i want to relate it to this expression which references the batch number:

public IQueryable<vwBatchDetail> AggregateBatchDetails(string batchNumber)
{
    var contex = new LBPEntities();
    var batchDetails = 
        (from x in contex.vwBatchDetails
        where x.BatchNumber == batchNumber
        select x);
    return batchDetails;
}

But creating that from two related tables in a database is escaping me. I have not been able to find any good solid JSON tutorials dealing with this problem, all examples generally have hard coded hierarchies.

Ahmad Ibrahim · Accepted Answer · 2013-12-27 16:16:03Z

1

you may try this

public IQueryable<vwBatchList> AggregateBatchList(int coid)
{
     var contex = new LBPEntities();
     var batchList = contex.vwBatchLists.Where(x => x.CoId == coid).Select(x => new
                  {
                        CoId = x.CoId,
                        ...
                        BatchDetails = contex.vwBatchDetails.Where(d => d.BatchNumber == x.batchNumber)
                  });

     var result = Json(batchList);
}

edited Dec 27, 2013 at 16:16

answered Dec 27, 2013 at 12:51

Ahmad Ibrahim

1,9352 gold badges15 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Zach M. Over a year ago

What exactly is the BatchDetails = x.Details suppose to be achieving?

Ahmad Ibrahim Over a year ago

if you have a collection of BatchDetails in your BatchList you can refer to this collection by BatchDetails = x.Details.. otherwise, you can use Where() as per my updated answer.

Zach M. Over a year ago

Ah BatchList and Batch Details are just tables in my database, i am using entity framework so I do not have any models containing a collection of batchlist data and then a collection of batchdetails

Zach M. Over a year ago

Thanks this worked very well. I ended up building a model to accommodate the structure above and it worked.

Ben Strombeck · Accepted Answer · 2013-12-27 02:04:32Z

0

I'm not an expert in this, but I believe you need a JOIN in your linq statement. See - http://code.msdn.microsoft.com/LINQ-Join-Operators-dabef4e9

something like:

 var batchList = (from x in contex.vwBatchLists
                  join d in contex.vwBatchDetails on x equals d.CoId
                  where x.BatchNumber == batchNumber
                  select x);

That's about the best I can do without seeing the database schema :-)

edited Dec 27, 2013 at 2:04

answered Dec 27, 2013 at 1:59

Ben Strombeck

1,5691 gold badge17 silver badges22 bronze badges

2 Comments

Zach M. Over a year ago

Unfortunately this does not achieve the data structure I am looking for, a simple join is not going to work in this instance.

Sagi Over a year ago

This is a simple Join that creates a flat return structure, not an Hierarchical List.

Collectives™ on Stack Overflow

Creating hierarchical JSON result from multiple tables using linq expression

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related