WithAggregate and Join Workaround

Hi Guys,

I would like to eager load on a linq query I have joined on, which I don't believe is possible at the moment. My ideal query would be:

var result = (from order in unitOfWork.Orders
              join account in unitOfWork.Accounts on order.AccountId equals account.Id
              join userAccounts in unitOfWork.UserAccounts on account.Id equals userAccounts.AccountId
              where order.Id == orderId && userAccounts.UserId == userId && order.Status == OrderStatusType.Open
              select order).WithAggregate(Aggregates.WithOrderLineDetails).FirstOrDefault();

However, from what I have read and experimented with, it is not possible to eager load as soon as you add a "join" in a linq statement. Ideally, I would like my query to grab the order, orderlines, and product records (and that's how I've set up my aggregate).

Thus, I have started looking for work-arounds. If I simply remove the "WithAggregate" call from my previous query, I can grab my order object with the first query. It is possible to then have a second query which uses my aggregate to load in the other related tables (e.g. OrderLine + Product)? e.g.

 // Psuedocode: Force an eager load of the related order lines/product info
result.OrderLines.WithAggregate("WithOrderLineDetails")

At the moment, I'm stuck forcing an eager load of orderlines, but I still have an N+2 queries due to the related product info I need to fetch.

Cheers,
-Brad

Oh, and one other suggestion - It would be great if there was a warning message output to the TraceLogger should someone try and use WithAggregate when they are using a "join" in Linq. As far as I can tell, it just silently fails.

A perfect message would be something along the lines of:

--- BRAD, STOP TRYING TO USE WITHAGGREGATE WHEN YOU USE A JOIN ---

I swear I've gone through about the diagnosing process for this style of issue about 8 times... (and promptly forget the result as soon as I have the solution)

:-)

-Brad

Hi Brad,

Yes this is largely correct - when LightSpeed executes a Project (projection) style query rather than a Find style query the aggregate is not used for eager loading. In general most LINQ queries are going to be issued as a projection so you can use this as a general rule.

Your suggestion about a warning in the log output is a good one though so I will have a look at how we can incorporate this :)

In terms of what you are trying to achieve, you can add the same aggregate to the association between OrderLines and ProductInfo's and this will also be eager loaded when you fetch an Order so your original query for Orders would fetch Orders, OrderLines and ProductInfos in the same batched statement.

Jeremy

Hi Jeremy,

I seem to be having a very dumb moment today - I'm not quite following exactly what I need to do to get this up and running.

If I retrieve the Order via a query as follows:

var result = (from order in unitOfWork.Orders
              join account in unitOfWork.Accounts on order.AccountId equals account.Id
              join userAccounts in unitOfWork.UserAccounts on account.Id equals userAccounts.AccountId
              where order.Id == orderId && userAccounts.UserId == userId && order.Status == OrderStatusType.Open
              select order).First();

I'm not sure how to eager load the OrderLines (and related Products). I do have an aggregate setup for OrderLines and Products, but I'm not sure of the syntax to load in the related orderlines and products when I hit the OrderLines property on my order object.

On a side note, back on my original query, I noticed that if I included the "WithAggregate", it appears as if the query engine queries for the order/orderline/products, but only populates the order in the object (the orderline property has a count of 0). I would have thought it would have ignored the WithAggregate completely (and thus not attempted to load the orderlines/products).

Cheers,
-Brad

Sorry to keep hassling, but any ideas on this one?

Cheers, -Brad

You are not really able to do this with LINQ since because of the way the expression tree gets structured for that we still have to manage that as a client side projection because LINQ creates a number of internal anonymous types which effectively just unwind the joined set back to the set of Orders for your actual final projection so that always forces us to use a projection to fetch in all of the entity information which in turn removes the use of aggregates.

You can express this using the underlying Query syntax though - something like:

var query = new Query(typeof(Order));

query.Join = Join.Inner<Order, Account>("AccountId", "Id").And(Join.Inner<Account, UserAccount>("Id", "AccountId"));
query.QueryExpression = Entity.Attribute<Order>("Id") == orderId && Entity.Attribute<UserAccount>("UserId") == userId && Entity.Attribute<Order>("Status") == OrderStatusType.Open;
query.AggregateName = "WithOrderLineDetails";

var result = unitOfWork.Find(query).First();

Jeremy