Use skip and take inside a LINQ include

Question

I have an object that has a property which is a collection of another object. I would like to load just a subset of the collection property using LINQ.

Here's how I'm trying to do it:

manager = db.Managers
            .Include(m => m.Transactions.Skip((page - 1) * 10).Take(10))
            .Where(m => m.Id == id)
            .FirstOrDefault();

The code above throws an error that says

The Include path expression must refer to a navigation property defined on the type. Use dotted paths for reference navigation properties and the Select operator for collection navigation properties.\r\nParameter name: path

What is the right way to do this in LINQ? Thanks in advance.

Answer 1

You cannot do this with Include. EF simply doesn't know how to translate that to SQL. But you can do something similar with sub-query.

manager = db.Managers
            .Where(m => m.Id == id)
            .Select(m => new { Manager = m, 
                               Transactions = db.Transactions
                                                .Where(t=>t.ManagerId == m.Id)
                                                .Skip((page-1) * 10)
                                                .Take(10)})
            .FirstOrDefault();

This will not return instance of Manager class. But it should be easy to modify it to suit your needs.

Also you have two other options:

1. Load all transactions and then filter in memory. Of course if there are a lot of transactions this might be quite inefficient.
2. Don't be afraid to make 2 queries in database. This is prime example when that is probably the best route, and will probably be the most efficient way of doing it.

Either way, if you are concerned with performance at all I would advise you to test all 3 approaches and see what is the fastest. And please let us know what were the results!

Answer 2

Sometimes the added complexity of putting everything in a single query is not worth it. I would split this up into two separate queries:

var manager = db.Managers.SingleOrDefault(m => m.Id == id);
var transactions = db.Transactions
    .Where(t => t.ManagerId == id)
    // .OrderBy(...)
    .Skip((page - 1) * 10).Take(10)
    .ToList();

Note that after doing this, manager.Transactions can be used as well to refer to those just-loaded transactions: Entity Framework automatically links loaded entities as long as they're loaded into the same context. Just make sure lazy loading is disabled, to prevent EF from automatically pulling in all other transactions that you specifically tried to filter out.