LINQ join group by multiple tables

Question

I have created a linq query for the child row table of the jquery datatable.

It's working. The problem is that I want to display the product name instead of the productid of the table order.

I would appreciate it if someone could help.

public JsonResult Getrecord()

{

 var List = from a in _context.tbl_Orders

            group a by new { a.orderNumber } into g

            select new ordervm

            {

                orderNumber = g.Key.orderNumber,

				customername=_context.tbl_Customers.Where(a=>a.CustomerID== g.Select(x => x.CustomerID).FirstOrDefault()).FirstOrDefault().cucustomername,	

                Totalvalue = g.Sum(x => x.value),

                offerdiscount = g.Sum(x => x.offerdiscount),

                extradiscount = g.Sum(x => x.extradiscount),

                wholesale_amount = _context.tbl_Wholesale_Amounts.Where(x => x.OrderNumber == g.Key.orderNumber).Select(a => a.wholesale_amount).SingleOrDefault(),

                tbl_orderses = g.Select(t => new tbl_order

                {

                ![msc](/api/attachments/d644bf2d-045c-435a-a584-616a484fd872?platform=QnA)

                    oredrQty = t.oredrQty,

                    salerate = t.salerate,

                    value = t.value

                }).ToList()

            };

 return Json(List);

}

public class tbl_customer

{

  [Key]

  public int CustomerID { get; set; }

  public string cucustomername { get; set; }

}

public class tbl_product

{

 [Key]

 public int productID { get; set; }

 public string? productname { get; set; }

 public decimal? purchaseRate {get; set;}

}

public class tbl_order

{

 [Key]

 public int orderId { get; set; }

 public int CustomerID { get; set; }

 public String orderNumber { get; set; }

 public int productID { get; set; }  

 public int? oredrQty { get; set; }

 public Decimal? salerate { get; set; }

 public Decimal? value { get; set; }

 public decimal? offerdiscount { get; set; }

 public decimal? extradiscount { get; set; }

}

public class ordervm

{

 public int orderId { get; set; }

 public String orderNumber { get; set; }

 public int? customerID { get; set; }

 public string customername { get; set; }

 public int productID { get; set; }

 public int? oredrQty { get; set; }

 public Decimal? value { get; set; }

 public Decimal? Totalvalue { get; set; }

 public decimal? offerdiscount { get; set; }

 public decimal? extradiscount { get; set; }

 public decimal? wholesale_amount { get; set; }

 public List<tbl_order> tbl_orderses { get; set; }

}

Answer 1

Hi @jewel,

According to your codes, we could find the tbl_order doesn't contain the productname property and this is a data model, if you want to query the productname, you should add a new property inside the tbl_order named productname and since this property is just used for this model, you could add the Notmapped attribute for it to tell the ef core don't query it. Then you could write the query inside the linq to query the tbl_products table according to the product id.

More details, you could refer to below example codes:

    public class tbl_order
    {
        public int orderId { get; set; }
        public int CustomerID { get; set; }
        public String orderNumber { get; set; }
        public int productID { get; set; }
        public int? oredrQty { get; set; }
        public Decimal? salerate { get; set; }
        public Decimal? value { get; set; }
        public decimal? offerdiscount { get; set; }
        public decimal? extradiscount { get; set; }
        [NotMapped]
        public string ProductName { get; set; }
    }

Linq:

            var List = from a in _context.tbl_Orders
                       group a by new { a.orderNumber } into g
                       select new ordervm
                       {
                            
                           orderNumber = g.Key.orderNumber,
                           customerID = g.Select(x => x.CustomerID).FirstOrDefault(),
                           Totalvalue = g.Sum(x => x.value),
                           offerdiscount = g.Sum(x => x.offerdiscount),
                           extradiscount = g.Sum(x => x.extradiscount),
                           wholesale_amount = _context.tbl_Wholesale_Amounts.Where(x => x.OrderNumber == g.Key.orderNumber).Select(a => a.wholesale_amount).SingleOrDefault(),
                           tbl_orderses = g.Select(t => new tbl_order
                           {
                             
                              ProductName = _context.tbl_products.Where(x=>x.productID == t.productID).Select(x=>x.productname).SingleOrDefault(),
                               productID = t.productID, // here  i want to product name
                               oredrQty = t.oredrQty,
                               salerate = t.salerate,
                               value = t.value
                           }).ToList()
                       };

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.