how to group by multiple table and sum using LINQ

Question

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; }      
}

Answer 1

I have a test with code below, here's my test result:

namespace WebAppMvc.Controllers
{
    public class LinqController : Controller
    {
        public IActionResult Index()
        {
            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; }
    }
}