LINQ ".Include" orderby in subquery

Question

I have the following entity code, which returns all users and "includes" all of their sample requests:

    var userQuery = from u in _IntranetContext.UserSet.Include("SampleRequests")
                           orderby u.LastName ascending
                           select u;

Each user has multiple SampleRequests. Each SampleRequest has an ID# (just an integer: 1, 22, 341, etc). The above LINQ to entities grabs the users and their SampleRequests like so:

User1: 33, 22, 341, 12

User2: 24, 3, 981

As you can see the SampleRequest ID# are not in an ascending order. I would like the results to be in order.

How do I put the orderby constraint on the Included SampleRequests ID#

Please note: SampleRequestId is a property of the SampleRequest...not a property of the User object

Had the same issue. Found... [http://stackoverflow.com/questions/1304556/linq-orderby-name-thenby-childrencollection-name](http://stackoverflow.com/questions/1304556/linq-orderby-name-thenby-childrencollection-name) — kervin, Oct 22 '09 at 18:00

score 8 · Accepted Answer · answered Jul 20 '09 at 20:46

I can currently think of two options for what you want. You can select them into a new class, where the user and the associated requests are properties:

var userQuery = from u in _IntranetContext.UserSet.Include("SampleRequests")
                orderby u.LastName ascending
                select new
                {
                    User = u,
                    SampleRequests = u.SampleRequests.OrderByDescending(r => r.SampleRequestId)
                };

This will cause issues if you wanted to return this type, as it is anonymous.

You can also select this into a new user object, similar to this:

var userQuery = from u in _IntranetContext.UserSet.Include("SampleRequests")
                orderby u.LastName ascending
                select new User
                {
                    Property1 = u.Property1,
                    Property2 = u.Property2,
                    Property3 = u.Property3,
                    SampleRequests = u.SampleRequests.OrderByDescending(r => r.SampleRequestId).ToList()
                };

This will return a collection of User objects, but updating the objects in the database could cause issues.

If User is an EF entity, you cannot assign the result of your OrderByDescending().ToList() call (a List) to the SampleRequests property on User, since its type will be EntityCollection. Also, I'd be wary of manually assigning each property in the query; if you add a new property to User later down the track and forget to update all your queries, you'll get User instances without that property assigned to. IMO it could be a maintenance PITA. — Daniel Chambers, Dec 10 '09 at 01:54
I find this quite ugly if your User-class has very many properties. Will this subcollection.ToList() make a lot of transfer between business logic and sql-server? One solution could be just fetch SampleRequests without order and then sort them later in c#-code (=memory). — Tuomas Hietanen, Mar 10 '11 at 14:58
You should create one sorted database index to UserSet.LastName. And another to SampleRequests with (foreign-key to UserSet and SampleRequestId) — Tuomas Hietanen, Mar 10 '11 at 14:59

score -1 · Answer 2 · answered Jul 20 '09 at 20:29

-1

Just add another ordering parameter to the orderby:

var userQuery = from u in _IntranetContext.UserSet.Include("SampleRequests")
                           orderby u.LastName ascending, 
                                   u.SampleRequestId descending
                           select u;

answered Jul 20 '09 at 20:29

LBushkin

129,300
32
216
265

Thanks, but SampleRequestId is a property of the SampleRequests include, not of the User object. – Jul 20 '09 at 20:33
@John: This is the only answer I see for what you described. I think we need more information first. – Sam Harwell Jul 20 '09 at 20:35
OK i rewrote my question - i think it might be a bit more clear – Jul 20 '09 at 20:45

score -2 · Answer 3 · answered Jul 20 '09 at 20:30

-2

Edit: Treed by <15 seconds.

var userQuery = from u in _IntranetContext.UserSet.Include("SampleRequests")
                orderby u.LastName ascending, u.SampleRequestId descending
                select u;

answered Jul 20 '09 at 20:30

Sam Harwell

97,721
20
209
280

Thanks, but SampleRequestId is a property of the SampleRequests include, not of the User object. – Jul 20 '09 at 20:33

LINQ ".Include" orderby in subquery

3 Answers3

Linked