How can I query for a list of customers + the dollar amount of their last order?

from c in session.Query.All<Customer>()
join o in session.Query.All<Order>() on c equals o.Customer into orders
select new { 
    Name = c.Name,
    LastOrderAmount = orders.OrderBy(o => o.OrderDate).Last().Amount

I get an exception that Last() cannot be translated by the query translator.

Also, I'd like a LEFT JOIN. Thus, if a customer has no orders, I'd like his LastOrderAmount to be 0.

asked Feb 22 '12 at 18:52

ara's gravatar image


edited Feb 23 '12 at 01:25

Dmitri%20Maximov's gravatar image

Dmitri Maximov

Apparently, the Last() method is not supported. I tried using First() and doing OrderByDescending(o => o.OrderDate) to reverse the sequence of the elements. The query executes now, but it returns the amount of the FIRST order, instead of the last. Apparently, the OrderByDescending() is not being applied.

(Feb 22 '12 at 19:09) ara ara's gravatar image

Ara, could you specify which version of DataObjects.Net you are using? The build number will do.

(Feb 23 '12 at 01:26) Dmitri Maximov Dmitri%20Maximov's gravatar image

(Feb 23 '12 at 02:23) ara ara's gravatar image

We are planning to publish the next 4.5 beta tomorrow. It will contain the fix for such issues.

(Feb 23 '12 at 04:53) Dmitri Maximov Dmitri%20Maximov's gravatar image

Great news, thank you. Roughly in how many hours will it be released? I hate to bug you guys, but it's really urgent for us.

(Feb 24 '12 at 03:48) ara ara's gravatar image

Ara, it might take a whole day to get it cooked and checked. I'll notify you here or you may monitor the blog for the announcement.

(Feb 24 '12 at 04:11) Dmitri Maximov Dmitri%20Maximov's gravatar image

Ara, the beta is available for download at the website. If you use Security practice you'll also need DataObjects.Net 4.5.0 package, it contains the latest version of Practices (which is now renamed to Extensions so you'll have to rename the namespaces in your project).

(Feb 24 '12 at 09:58) Dmitri Maximov Dmitri%20Maximov's gravatar image
Be the first one to answer this question!
Please start posting your answer anonymously - your answer will be saved within the current session and published after you log in or create a new account. Please try to give a substantial answer, for discussions, please use comments and please do remember to vote (after you log in)!
toggle preview

powered by OSQA