Fetch Data query from MVC controller is not showing 2 columns

Question

Please in my controller I have this code to fetch data from two tables in the database, it fetches the data alright but Quantity and TotalAmount amount is not coming. thank you.

public ActionResult getNewOrder()
{
    ASPNETMASTERPOSTEntities db = new ASPNETMASTERPOSTEntities();
    var dataList = db.tblSales.Include("tblSalesDetails").ToList();
    var modifiedData = dataList.Select(x => new
    {
       SalesDetailId = x.SalesDetailId,
        Quantity = x.Quantity,
        ProductId = x.ProductId,
        ProductName = x.tblProduct.ProductName,
        SalesId = x.SalesId,
        OrderDate = x.OrderDate,
        SubTotal = x.SubTotal,
        DiscountPercent = x.DiscountPercent,
        VatPercent = x.VatPercent,
        TotalAmount = x.TotalAmount
    }).ToList();
    return Json(modifiedData, JsonRequestBehavior.AllowGet);

}

Tuhin Paul · Answer

We can use eager loading with the Include method to load related entities efficiently. You are already doing this, which is good. Instead of calling ToList() on the entire dataList object, you can use AsEnumerable() to load only the necessary data into memory. This can improve performance if the original query returns a large amount of data. Instead of using anonymous types in the select statement, you can create a custom model class to hold the selected properties. This can make your code more readable and maintainable. public class SalesDetailModel { public int SalesDetailId { get; set; } public int Quantity { get; set; } public int ProductId { get; set; } public string ProductName { get; set; } public int SalesId { get; set; } public DateTime OrderDate { get; set; } public decimal SubTotal { get; set; } public decimal DiscountPercent { get; set; } public decimal VatPercent { get; set; } public decimal TotalAmount { get; set; } } public ActionResult getNewOrder() { using (var db = new ASPNETMASTERPOSTEntities()) { var dataList = db.tblSales.Include("tblSalesDetails"); var modifiedData = dataList.AsEnumerable().SelectMany(x => x.tblSalesDetails.Select(d => new SalesDetailModel { SalesDetailId = d.SalesDetailId, Quantity = d.Quantity, ProductId = d.ProductId, ProductName = d.tblProduct.ProductName, SalesId = x.SalesId, OrderDate = x.OrderDate, SubTotal = x.SubTotal, DiscountPercent = x.DiscountPercent, VatPercent = x.VatPercent, TotalAmount = x.TotalAmount })); return Json(modifiedData, JsonRequestBehavior.AllowGet); } } I also added a using block to properly dispose of the db object after use.

Naimish Makwana · Answer

It seems that you are using the Include method to eagerly load the tblSalesDetails table into the query result, but you are not using the properties from the tblSalesDetails table in your select statement. Instead, you are trying to select properties from the tblSales table which are not available in the dataList object. To fix this issue, you need to modify your query to include the properties from the tblSalesDetails table in the select statement. Here's an updated version of your code: public ActionResult getNewOrder() { ASPNETMASTERPOSTEntities db = new ASPNETMASTERPOSTEntities(); var dataList = db.tblSales.Include("tblSalesDetails").ToList(); var modifiedData = dataList.SelectMany(x => x.tblSalesDetails.Select(d => new { SalesDetailId = d.SalesDetailId, Quantity = d.Quantity, ProductId = d.ProductId, ProductName = d.tblProduct.ProductName, SalesId = x.SalesId, OrderDate = x.OrderDate, SubTotal = x.SubTotal, DiscountPercent = x.DiscountPercent, VatPercent = x.VatPercent, TotalAmount = x.TotalAmount })).ToList(); return Json(modifiedData, JsonRequestBehavior.AllowGet); } Thanks

Emmmanuel FIADUFE · Answer

Sachin quantity and TotalAmount belongs to tblSalesDetails

Emmmanuel FIADUFE · Answer

Thank you so much Mr Paul for this explanation, I really appreciate your help, I will try and revert as soon it works for me.

Emmmanuel FIADUFE · Answer

Thank you guys, I will check and revert, The results am getting is the quantity and TotalAmount amount rows came empty

Sachin Singh · Answer

do the quantity and totalamount belong to tblSales and they have data in DB?

Jignesh Kumar · Answer

What values you are getting, can you please share details ? you can try to check like this, https://stackoverflow.com/questions/21558277/entity-framework-retrieve-data-from-table-with-foreign-key