This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
If I place an orderby attribute at a class level when I access the respective table via a thru association the orderby is NOT on the emitted sql for the table. Is this the expected behaviour or is there some way I can turn it on so that I can get the rows via the thru association in a specific order ? Thanks. |
|
|
If you are using a ThroughAssociation you will notice that the way LightSpeed fetches the through collection is by selecting the associated rows from the through table rather than from the target table when building up the collection, so it will not use any Ordering directive from the target table for this. When you access the individual target objects they are individually fetches by Id as seperate queries at the time of access (so if you do a .ToList()) on a ThroughAssociation it will cost N queries.
Jeremy |
|
|
I have the same problem.
I have a line like this: notes = Me.Models.Audits.Notes.OrderBy(Function(x) x.Order).ToList This code don't generate the "Order By" clause in the emitted SQL.
But if I write line like this: notes = (From x In UnitOfWork.Notes Where x.Audit = 1 Order By x.Order).ToList This code work perfectly; the emitted code have the "Order by" clause.
After reading what you said Jeremy, I'm not sure to understand why exactly to generated SQL don't have the "Order by" clause.
Thank you very much for your patience.
|
|
|
No problem :) For examples sake I will assume your model contains 3 underlying DB tables - Audit, Note and AuditNote (many to many table). In your model you are using the through association from Audit to Note through AuditNote. So given this, when you write a query like: Audits.Notes.OrderBy(x => x.Order).ToList() This basically asks LightSpeed to load the contents of the through collection (Notes) to see what should be in the collection. The way LightSpeed determines what is in the collection is to load all entries from the AuditNote table (where AuditId = the parents Id) and then individually fetch the Note entries which match this. So the reason the OrderBy is not emitted as part of any SQL is because it would only make sense on the original query, but in the context of that query the OrderBy attribute would not form part of that query. For the second query you gave, you are working directly against the Note table, so an ordering makes sense here and it can be passed down to the database query. Hopefully that provides a bit more clarity :) Before you ask, yes - this could be made more efficient if we loaded the Note entities via a joined query at the time we loaded them from the AuditNote table and this is something I have on the backlog :)
Jeremy |
|
|
Thank you very much Jeremy, This morning, I had the idea of calling the .ToList of the result for the through association and call the .OrderBy but the problem is that I have an Order By of one column and also an Order By Descending of another column and that sequence is not handled by Linq To Object. But with what you have advertise me, I will create my Notes list directly from another Linq Query instead of a Through association. Just as a suggestion, it would be very nice if you can put a lot of your team knowledge in a documentation file. I don't know if is more efficient to leave it in the forum but in the last couple of months, I saw a lot of your valuable brain juice that could make and incredible documentation while maintaining your very useful forum.
Thank you again for your clean and detailled answer. |
|