CODEWITHSUNDEEP
linq-to-entities
John B
John B

Reputation:

LINQ ".Include" orderby in subquery

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

Upvotes: 8

Views: 14105

Answers (3)

Ryan Versaw
Ryan Versaw

Reputation: 6495

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.

Upvotes: 8

Sam Harwell
Sam Harwell

Reputation: 99949

Edit: Treed by <15 seconds.

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

Upvotes: -2

LBushkin
LBushkin

Reputation: 131746

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;

Upvotes: -1

Related Questions