Using [OrderBy] for an EntityCollection

 Hi All,

I have a query that returns a ProductLine object and all associated Products using a named aggregate as below :

ProductLines.Where(pl => pl.Id == prodLineID).WithAggregate("AllProductsForLine").First();

now, I want to return the Products in a particular order, so went into the designer and added code into the Order By property for the relationship between ProductLine and Product. However, when I examined the SQL that was produced for the query, there was no order by clause at all. I then changed the property to total rubish and the result was the same. It seems to be ignorning the OrderBy completely. Have I missed something here ?

What I'd ideally like to do is order by the ColourCode which is in another table - something like this :

Products.Where(p => p.ProdLineId == prodLineID).OrderBy(p => p.Colour.ColourCode)

Is that possible with an aggregate on the ProductLine, or will I have to do it in two hits (once for Product Line, then another for all Products) ?

To help us diagnose this problem, could you whether the OrderByAttribute been generated onto the ProductLine._products field (or wherever it is meant to be) please?  This will help us determine whether the issue is in the query engine or the designer.  Thanks!

I don't think LINQ supports ordering on values in another table at the moment.  I think you may be able to do this using the core API e.g. uow.Find<Product>(new Query { Order = Order.By("Colour.ColourCode" }) but this may be database-dependent.  I believe this will work more consistently in v3 as a result of improvements to join support.

Hmm, I have looked into this a bit more and I think there is actually an issue in the core API as well.  This could cause errors if there are multiple associations between the same types.  When we get this fixed it may enable LINQ support as well.  I will keep you posted.

 Hi Ivan,

The code to order by the ColourCode that I posted does actually work as a seperate query, sorry forgot to mention that !

Yes, the [OrderBy] attribute gets generated by the model.

Thanks.  Unfortunately I can't reproduce the issue -- I have created a query like yours and the SQL ORDER BY is being emitted for the child collection subselect as one would expect.  Can you check that the [EagerLoad(AggregateName="AllProductsForLine")] attribute is present on the _products collection?  Can you provide us with your .lsmodel and .lsmodel.cs files (email if you don't want to post them in the forums)?

 files sent to your email address

This appears to be a bug where subselect orderings are not being applied if the main query is paging (and the LINQ First() operator is implemented using paging).

We are looking into a fix.  In the meantime the workaround is to remove the paging from your ProductLine query -- i.e. pull back all the results that meet the Where clause.  (This won't impact efficiency because you're only bringing back a single result anyway.)  To do this, either insert a ToList() call before .First():

....WithAggregate("...").ToList().First();

or use Single instead of First:

....WithAggregate("...").Single();

 Thanks Ivan, glad I hadn't done something daft !

A fix for this bug will appear in the next nightly build (9 Apr 2009).  Though in your particular case there's no harm in using the Single() operator anyway, since this makes it clearer that you expect only one ProductLine to meet the criteria, rather than there potentially being many and just picking the first.

 Thanks Ivan. Still finding my way round LINQ and didn't actually notice the Single operator before , which does fit this scenario much better.