How to create nested JSON response with Entity Framework Query

Question

I want to get a nested JSON reply with my EF query and not sure how to do this. I have declared my Models as follows:

  [Serializable]
    public class StockReturnMethod
    {
        public int WarehouseID { get; set; }
        public int ProductSKUID { get; set; }
        public int LotID { get; set; }
        public string LotName { get; set; }
        public int AreaID { get; set; }
        public string AreaName { get; set; }
        public int BinID { get; set; }
        public string BinName { get; set; }
        public List<AvailibleStock> Stock { get; set; }
    }


  [Serializable]
    public class AvailibleStock
    {
        public int WarehouseID { get; set; }
        public int ProductSKUID { get; set; }
        public string ProductSKUName { get; set; }
        public string WarehouseName { get; set; }
        public string Status { get; set; }
        public int QtyUnassigned { get; set; }
    }

Here is my EF query that I have so far.

    {
       return (from WH in SCMENT.Warehouses 
               join WL in SCMENT.WarehouseLots on WH.WarehouseID equals WL.WarehouseID
               join WA in SCMENT.WarehouseAreas on WL.WarehouseLotID equals WA.WarehouseLotID
               join WB in SCMENT.WarehouseBins on WA.WarehouseAreaID equals WB.WarehouseAreaID
               join SLI in SCMENT.StockLineItems on WH.WarehouseID equals SLI.WarehouseID
               join PSKU in SCMENT.ProductSKUs on SLI.ProductSKUID equals PSKU.ProductSKUID
               where SLI.SystemAreaID == 1

               select new StockReturnMethod() 
               {
                WarehouseID = WH.WarehouseID,
                LotID = WL.WarehouseLotID,
                LotName = WL.WarehouseLotName,
                AreaID = WA.WarehouseAreaID,
                AreaName = WA.WarehouseAreaName,
                BinID = WB.WarehouseBinID,
                BinName = WB.WarehouseBinName,
                ProductSKUID = PSKU.ProductSKUID,
                Stock = (Will I create a sub query here?)


               }

    )

Admir Tuzović · Accepted Answer · 2013-03-12 12:11:01Z

2

public List<AvailibleStock> Stock { get; set; }

Change this to:

public IList<AvailibleStock> Stock { get; set; }

Edit: Here's working sample from one of my projects that you can use as reference:

public class StatisticsModel
{
    public int Id { get; set; }
    public int ClientId { get; set; }
    public int Opened { get; set; }
    public IEnumerable<LocationsModel> Locations { get; set; }
}


public class LocationModel
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int OpenCount { get; set; }
}


return dbSet.Select(x => new StatisticsModel
{
    Id = x.Id,
    ClientId = x.ClientId,
    Opened = x.OpenCount,
    Locations = x.Locations.Select(z => new LocationsModel{
        Id = z.Id,
        Name = z.Store.Name,
        OpenCount = z.OpenCount
    })
})

edited Mar 12, 2013 at 12:11

answered Mar 12, 2013 at 8:46

Admir Tuzović

11.2k7 gold badges38 silver badges75 bronze badges

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

3 Comments

Jacques Bronkhorst Over a year ago

Added the suggested changes, i get ****Invalid initializer member declarator**** with this line of code: SLI.select(z => new AvailibleStock

Admir Tuzović Over a year ago

Since I don't have insight in your data tables, I've provided you with one of my models and projections. I believe you will be able to extract the logic.

Jacques Bronkhorst Over a year ago

You Sir Are a gentlemen and a scholar!

Collectives™ on Stack Overflow

How to create nested JSON response with Entity Framework Query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related