Select multiple joined entities with a single query

Hi,

Sorry if this has already been asked but I couldn't find anything in search. I am looking into converting a large application that's using linq-to-sqlserver to use mysql instead, and evaluating your product as one of the options. (By the way, I'm not sure if it's relevant, but I haven't switched to mysql yet, I am evaluating lightspeed against the existing sql server database).

The existing app has many queries that look something like this

var q = from oi in x.OrderItems
        where oi.Order.DeliveryDate < DateTime.Now
        select new
        {
            Order = oi.Order,
            OrderItem = oi
        };

var test = q.Take(2).ToArray();

which in Microsoft's LINQ gets translated to a single joined query. Unfortunately when I run this in LightSpeed, although it does do a joined query, when the anonymous type is being constructed it executes a separate sub query every time the "Order" property of the anonymous type is set, like so:

SELECT
  [t0].*
FROM
  (
    SELECT
      [t0].[OrderItemID] AS [t0.OrderItemID],
      [t0].[OrderId] AS [t0.OrderId],
      [t0].[ProductId] AS [t0.ProductId],
      [t0].[Quantity] AS [t0.Quantity],
      [t1].[OrderID] AS [t1.OrderID],
      ROW_NUMBER() OVER(ORDER BY [t0].[OrderItemID]) as RowNumber
    FROM [OrderItem] [t0]
    INNER JOIN
      [Order] [t1]
    ON
      [t0].[OrderId] = [t1].[OrderID]
    WHERE
      EXISTS (
        SELECT [t1].*
        FROM [Order] [t1]
        WHERE [t1].[OrderID] = [t0].[OrderId] AND [t1].[DeliveryDate] < '16/08/2011 12:06:58'
      )
  )
  [t0]
WHERE
  RowNumber <= 2

SELECT [Order].[OrderID] AS [Order.OrderID],
FROM [Order]
WHERE [Order].[OrderID] = 4864

SELECT [Order].[OrderID] AS [Order.OrderID],
FROM [Order]
WHERE [Order].[OrderID] = 4865

I fear this is going to get extremelly slow in the actual app, where it's actually doing .Take(20) instead of .Take(2), as well as 10 joins in the anonymous type as oppose to just "Order". Is there any any setting I can set to solve this, or advice on how the linq queries might be constructed to achieve what I want?

Thanks

Make the OrderItem.Order association eager loaded (or part of a named aggregate if you don't always want it eager loaded).

See http://www.mindscapehq.com/documentation/lightspeed/Performance-and-Tuning/Controlling-How-Entities-Load

Eager loading doesn't really work imo since it then eager loads them always, regardless of how I do the select.

The link you gave shows how you can conditionally eager load, but that appears to require replacing all linq queries with extension methods, which to me kind of defeats the purpose of linq...

Thanks though.

You don't need to replace LINQ query syntax with extension methods to use named aggregates.  You just need to use the WithAggregate() extension method within the query.  E.g.:

var query = from p in uow.Products.WithAggregate("MyAggregate")
                   where p.Price < 100
                   select p;