0

how to convert sql to linq, entity?

select Productdeltail.*,tv.sale,tv.idsale,tv.dateTo,tv.datefrom 
from Productdeltail
left join 
    (select SaleDetail.idProduct, SaleDetail.idSize, SaleDetail.sale,Sale.idsale,dateTo,datefrom from SaleDetail
    join Sale on SaleDetail.idsale = Sale.idsale 
    where (GETDATE() >= Sale.dateTo and GETDATE() <= Sale.datefrom ) ) tv on Productdeltail.idProduct= tv.idProduct and Productdeltail.idSize= tv.idSize
Wei Lin
  • 3,591
  • 2
  • 20
  • 52
Hiếu Nguyễn
  • 1
  • 1
    You don't. EF is an ORM, just like NHibernate. You create entities with relations and map them to tables. After that, the ORM generates the actual SQL queries each time you try to load entities and their relations. It looks like you have `Sale`, `SaleDetail` and `Product`entities in your applications. – Panagiotis Kanavos Jun 27 '19 at 07:54
  • It's easier to use `Database.SqlQuery` – Wei Lin Jun 27 '19 at 07:57
  • If you configure the entities and mappings, all you need to do is write something like `from sale in dbContext.Sales from detail in sale.Details where sale.DateTo>=DateTime.Now and sale.DateFrom >=DateTime.Now select new { Details=detail.ProductDetail, detail.Sale,detail.idSale,sale.DateFrom,Sale.DateTo}`. Or reverse the query `from detail in context.SalesDetails let sale =detail.Sales where sale.DateTo <=DateTime.Now and sale.DateFrom >=DateTime.Now select detail`. The resulting `SalesDetail` objects will also pull the related `ProductDetail`, `Sale` objects – Panagiotis Kanavos Jun 27 '19 at 07:58
  • In fluent form this could look like `dbContext.SalesDetail.Where(detail=>detail.Sale.DateFrom<=DateTime.Now ....).Select(..)` – Panagiotis Kanavos Jun 27 '19 at 08:02
  • Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) will help you. – NetMage Jun 27 '19 at 17:59

0 Answers0