CODEWITHSUNDEEP
c#jsonasp.net-mvc-3asp.net-mvc-4dbcontext
hidden
hidden

Reputation: 3236

How to create a dbcontext query that returns the relational database structure in json

My problem is I need to render the following structure from a dbcontext query because a library can render this format very nicely. Here the data in json.

{"Customer":{"Id":"BOLID","CompanyName":"Bólido Comidas preparadas","ContactName":"Martín Sommer","ContactTitle":"Owner","Address":"C/ Araquil, 67","City":"Madrid","PostalCode":"28023","Country":"Spain","Phone":"(91) 555 22 82","Fax":"(91) 555 91 99"},"CustomerOrders":[{"Order":{"Id":10326,"CustomerId":"BOLID","EmployeeId":4,"OrderDate":"\/Date(844898400000+0000)\/","RequiredDate":"\/Date(847321200000+0000)\/","ShippedDate":"\/Date(845244000000+0000)\/","ShipVia":2,"Freight":77.92,"ShipName":"Bólido Comidas preparadas","ShipAddress":"C/ Araquil, 67","ShipCity":"Madrid","ShipPostalCode":"28023","ShipCountry":"Spain"},"OrderDetails":[{"OrderId":10326,"ProductId":4,"UnitPrice":17.6,"Quantity":24,"Discount":0},{"OrderId":10326,"ProductId":57,"UnitPrice":15.6,"Quantity":16,"Discount":0},{"OrderId":10326,"ProductId":75,"UnitPrice":6.2,"Quantity":50,"Discount":0}]},{"Order":{"Id":10801,"CustomerId":"BOLID","EmployeeId":4,"OrderDate":"\/Date(883350000000+0000)\/","RequiredDate":"\/Date(885769200000+0000)\/","ShippedDate":"\/Date(883522800000+0000)\/","ShipVia":2,"Freight":97.09,"ShipName":"Bólido Comidas preparadas","ShipAddress":"C/ Araquil, 67","ShipCity":"Madrid","ShipPostalCode":"28023","ShipCountry":"Spain"},"OrderDetails":[{"OrderId":10801,"ProductId":17,"UnitPrice":39,"Quantity":40,"Discount":0.25},{"OrderId":10801,"ProductId":29,"UnitPrice":123.79,"Quantity":20,"Discount":0.25}]},{"Order":{"Id":10970,"CustomerId":"BOLID","EmployeeId":9,"OrderDate":"\/Date(890694000000+0000)\/","RequiredDate":"\/Date(891900000000+0000)\/","ShippedDate":"\/Date(893368800000+0000)\/","ShipVia":1,"Freight":16.16,"ShipName":"Bólido Comidas preparadas","ShipAddress":"C/ Araquil, 67","ShipCity":"Madrid","ShipPostalCode":"28023","ShipCountry":"Spain"},"OrderDetails":[{"OrderId":10970,"ProductId":52,"UnitPrice":7,"Quantity":40,"Discount":0.2}]}]}

Which 

http://ajaxstack.com/jsonreport/
renders very nicely. Now I tried this:

Controller stuff

MedicalVarianceEntities DbCtx = new MedicalVarianceEntities();

    var data = 
        (
        from Mvrs in DbCtx.Mvrs
        join MvrMeds in DbCtx.MvrMeds
        on Mvrs.PKMvrId equals MvrMeds.FKMvrId
        join MvrMedsProcurementErrors in DbCtx.MvrMedsProcurementErrors
        on MvrMeds.PKMvrMedsId equals MvrMedsProcurementErrors.FKMvrMedsId
            select new
            {
                MvrId,
                MvrMeds.MvrMedsMedicineName//,
              //  MvrMedsProcurementErrors.MvrMedsProcurementErrorsId
            }
       );

Now the JQUERY 

$(function () {
  $.getJSON("/MedicineManagement/MvrSummary/1", null, function (data) {
                  var test = JSON.stringify(data);
                 alert(test);
});

enter image description here

Notice THAT what I wanted was something like this Mvrs":[{"MvrMeds":{"MvrMedsMedicineName":ACETAMINOPHEN}}]

This JSON I think means Mvrs is the parent of MvrMeds and MvrMedsMedicineName is a property of MvrMeds.

Upvotes: 1

Views: 621

Answers (1)

hidden
hidden

Reputation: 3236

So far I have only managed to do it this ugly way. 

     public JsonResult MvrSummary(int MvrId)
        {
       MedicalVarianceEntities DbCtx = new MedicalVarianceEntities();
            var data = from entity in DbCtx.Mvrs
                          select new
                          {
                              Prop1 = entity.PKMvrId,

                              ChildProp = entity.MvrEmployees.Select(x=>x.ODSEmpNumber),
                              GrandChildProp = entity.MvrEmployees.Select(x=>x.MvrEmployeesStorageErrors.Select(y=>y.MvrEmployeesStorageErrorsId))
                          };
 return Json(data, JsonRequestBehavior.AllowGet);
}

Upvotes: 0

Related Questions