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