Home / Community & Support / Developer Productivity Tools Forums / OpenAccess ORM > LINQ (LINQ specific questions) > Wrong SQL generated with joined subqueries

• Greg

  Posted on Jun 8, 2012 (permalink)

  When I 'left join' two filtered tables the filter expression is moved to after the join operation. Example uses the NorthwindEF sample database.

  LINQ query (db is the ObjectContext):

  from c in db.Customers

  join o0 in db.Orders.Where(a => a.OrderID == -1) on c.CustomerID equals o0.CustomerID into og

  from o in og.DefaultIfEmpty()

  select new { c.CustomerID, o.OrderID };

  Expected results (91 rows, one for each row in dbo.Customers):

  SELECT a.CustomerID, b.OrderID

  FROM Customers a

  LEFT JOIN

  ( SELECT * FROM Orders WHERE b.OrderID = -1) AS b

  ON a.CustomerID = b.CustomerID

  Actual results (0 rows):

  SELECT a.CustomerID, b.OrderID

  FROM Customers a

  LEFT JOIN Orders AS b ON a.CustomerID = b.CustomerID

  WHERE b.OrderID = -1

  Note that even though there is an fk association between Customers and Orders, I'm intentionally not using it. Orders could be any query with arbitrary complexity.

  Reply

• Greg

  Posted on Jun 12, 2012 (permalink)

  Issue still present in Q2 release (2012.2.607.1)

  Reply

• Thomas Thomas

  Posted on Jun 25, 2012 (permalink)

  Yes, I must confirm that. We are aware of the issue, and will provide a fix for this. It is just not doable quickly....
  
  Kind regards,
  Thomas
  the Telerik team

  OpenAccess ORM Q2'12 Now Available! Get your hands on all the new stuff.

  Reply

Back to Top

Home / Community & Support / Developer Productivity Tools Forums / OpenAccess ORM > LINQ (LINQ specific questions) > Wrong SQL generated with joined subqueries