Join 3 tables in 1 LINQ-EF

Posted by user100161 on Stack Overflow See other posts from Stack Overflow or by user100161
Published on 2009-10-09T01:32:14Z Indexed on 2010/04/06 21:43 UTC
Read the original article Hit count: 623

Filed under:
|
|

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

© Stack Overflow or respective owner

Related posts about linq-entity-framework

Related posts about join