ASP.NET Core
A set of technologies in the .NET Framework for building web applications and XML web services.
4,815 questions
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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; }
}
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; }
}
}