how to group by multiple table and sum using LINQ

jewel 841 Reputation points
2023-11-26T15:25:24.7433333+00:00

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

ASP.NET Core
ASP.NET Core
A set of technologies in the .NET Framework for building web applications and XML web services.
4,390 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,632 questions
{count} votes

Accepted answer
  1. Tiny Wang-MSFT 2,321 Reputation points Microsoft Vendor
    2023-11-27T03:38:26.8766667+00:00

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

    User's image

    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; }
        }
    }
    
    1 person found this answer helpful.
    0 comments No comments

0 additional answers

Sort by: Most helpful