I have a sql query i want to make it as linq query. It would be helpful if experienced people could help. thanks in advance

//This My Sql Query select c.productname ,c.productID, stock = ISNULL(a.qty, 0) - isnull(b.qty, 0), value=ISNULL(a.qty, 0) - isnull(b.qty, 0)*c.purchaseRate from( select productID, qty = sum(Qty) from tbl_Purchases group by productID) a left join (select productID, qty = sum(qty) from tbl_Sells group by productID ) b on a.productID = b.productID left join (select * from tbl_Products) c on a.productID = c.productID where a.productID=1 //This is My Models Short Form public class tbl_product [Key] public int productID { get; set; } public string? productname { get; set; public decimal? purchaseRate { get; set; public class tbl_purchase [Key] public int purchasesId { get; set; } public int productID { get; set; } [Required] public int Qty { get; set; } public class tbl_Sell [Key] public int SelliD { get; set; } public int productID { get; set; } public int Qty { get; set; } public class ProductVM public int SelliD { get; set; } public int productID { get; set; } public String productname { get; set; } public int Qty { get; set; } public Decimal Value { get; set; }

Hi jewel, we have documents here which help us using group feature via linq, and following the document, we might have expression like below.

             from a in (
                 from tbl_purchase in tbl_Purchases
                 where tbl_purchase.productID == 1
                 group tbl_purchase by tbl_purchase.productID into grp
                 select new
                     productID = grp.Key,
                     qty = grp.Sum(p => p.Qty)
             join b in (
                 from tbl_Sell in tbl_Sells
                 group tbl_Sell by tbl_Sell.productID into grp
                 select new
                     productID = grp.Key,
                     qty = grp.Sum(s => s.qty)
             ) on a.productID equals b.productID into bGroup
            List<tbl_purchase> tbl_Purchases = new List<tbl_purchase> { 
                new tbl_purchase{ purchasesId = 1, productID = 1, QtyForPurchase = 1},
                new tbl_purchase{ purchasesId = 2, productID = 1, QtyForPurchase = 1},
                new tbl_purchase{ purchasesId = 3, productID = 1, QtyForPurchase = 2},
                new tbl_purchase{ purchasesId = 4, productID = 2, QtyForPurchase = 1},
                new tbl_purchase{ purchasesId = 5, productID = 2, QtyForPurchase = 1},
                new tbl_purchase{ purchasesId = 6, productID = 3, QtyForPurchase = 1}
            List<tbl_Sell> tbl_Sells = new List<tbl_Sell>
                new tbl_Sell{ SelliD = 1, productID = 1, QtyForSell = 1},
                new tbl_Sell{ SelliD = 2, productID = 1, QtyForSell = 1},
                new tbl_Sell{ SelliD = 5, productID = 2, QtyForSell = 1},
                new tbl_Sell{ SelliD = 6, productID = 3, QtyForSell = 1},
                new tbl_Sell{ SelliD = 7, productID = 3, QtyForSell = 1}
            List<tbl_product> tbl_Product = new List<tbl_product> {
                new tbl_product{ productID = 1, productname = "prod one", purchaseRate = (decimal)0.5},
                new tbl_product{ productID = 2, productname = "prod two", purchaseRate = (decimal)0.5}
            var temp5 = from a in (
                 from purchase in tbl_Purchases
                 where purchase.productID == 1
                 group purchase by purchase.productID into grp
                 select new
                     productID = grp.Key,
                     qtyP = grp.Sum(p => p.QtyForPurchase)
             ) join b in (
                 from sell in tbl_Sells
                 group sell by sell.productID into grp
                 select new
                     productID = grp.Key,
                     qtyS = grp.Sum(s => s.QtyForSell)
             ) on a.productID equals b.productID into bGroup
             from c in bGroup.DefaultIfEmpty()
             join d in tbl_Product on a.productID equals d.productID into cGroup
             from e in cGroup.DefaultIfEmpty()
             select new {
                 e.productname,
                 a.productID,
                 qtyP = a.qtyP,
                 qtyS = c.qtyS,
                 stock = (a.qtyP == 0 ? 0 : a.qtyP) - (c.qtyS == 0 ? 0 : c.qtyS),
                 value = (a.qtyP == 0 ? 0 : a.qtyP) - (c.qtyS == 0 ? 0 : c.qtyS) * (e.purchaseRate == 0 ? 0: e.purchaseRate)
            return View();
    public class tbl_product
        [Key]
        public int productID { get; set; }
        public string? productname{ get; set; }
        public decimal? purchaseRate
            get; set;
    public class tbl_purchase
        [Key]
        public int purchasesId { get; set; }
        public int productID { get; set; }
        [Required]
        public int QtyForPurchase { get; set; }
    public class tbl_Sell
        [Key]
        public int SelliD { get; set; }
        public int productID { get; set; }
        public int QtyForSell { get; set; }
    public class ProductVM
        public int SelliD { get; set; }
        public int productID { get; set; }
        public String productname { get; set; }
        public int Qty { get; set; }
        public Decimal Value { get; set; }