Join 3 tables in 1 LINQ-EF
- by user100161
I have to fill warehouse table cOrders with program using Ado.NET EF. I have SQL command but i don't know how to do this with LINQ.
static void Main(string[] args)
{
var SPcontex = new PI_NorthwindSPEntities();
var contex = new NorthwindEntities();
dCustomers dimenzijaCustomers = new dCustomers();
dDatum dimenzijaDatum = new dDatum();
...
CREATE TABLE PoslovnaInteligencija.dbo.cOrders(
cOrdersID int PRIMARY KEY IDENTITY(1,1),
OrderID int NOT NULL,
dCustomersID int FOREIGN KEY REFERENCES PoslovnaInteligencija.dbo.dCustomers(dCustomersID),
dEmployeesID int FOREIGN KEY REFERENCES PoslovnaInteligencija.dbo.dEmployees(dEmployeesID),
OrderDateID int FOREIGN KEY REFERENCES PoslovnaInteligencija.dbo.dDatum(sifDatum),
RequiredDateID int FOREIGN KEY REFERENCES PoslovnaInteligencija.dbo.dDatum(sifDatum),
ShippedDateID int FOREIGN KEY REFERENCES PoslovnaInteligencija.dbo.dDatum(sifDatum),
dShippersID int FOREIGN KEY REFERENCES PoslovnaInteligencija.dbo.dShippers(dShippersID),
dShipID int FOREIGN KEY REFERENCES PoslovnaInteligencija.dbo.dShip(dShipID),
Freight money,
WaitingDay int
)
INSERT INTO PoslovnaInteligencija.dbo.cOrders (OrderID, dCustomersID, dEmployeesID, OrderDateID, RequiredDateID, dShippersID, dShipID, Freight, ShippedDateID, WaitingDay)
SELECT OrderID, dc.dCustomersID, de.dEmployeesID, orderD.sifDatum, requiredD.sifDatum, dShippersID, ds.dShipID, Freight,
ShippedDateID=CASE WHEN (ShippedDate IS NULL) THEN -1 ELSE shippedD.sifDatum END,
WaitingDay=CASE WHEN (shippedD.sifDatum - orderD.sifDatum) IS NULL THEN -1 ELSE shippedD.sifDatum - orderD.sifDatum END
FROM PoslovnaInteligencija.dbo.dShippers AS s, PoslovnaInteligencija.dbo.dCustomers AS dc,
PoslovnaInteligencija.dbo.dEmployees AS de, PoslovnaInteligencija.dbo.dShip AS ds,PoslovnaInteligencija.dbo.dDatum AS orderD,
PoslovnaInteligencija.dbo.dDatum AS requiredD,
PoslovnaInteligencija.dbo.Orders AS o LEFT OUTER JOIN PoslovnaInteligencija.dbo.dDatum AS shippedD
ON shippedD.datum=DATEADD(dd, 0, DATEDIFF(dd, 0, o.ShippedDate))
WHERE o.ShipVia=s.ShipperID AND dc.CustomerID=o.CustomerID AND de.EmployeeID=o.EmployeeID AND ds.ShipName=o.ShipName AND
orderD.datum=DATEADD(dd, 0, DATEDIFF(dd, 0, o.OrderDate)) AND
requiredD.datum=DATEADD(dd, 0, DATEDIFF(dd, 0, o.RequiredDate));
