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.

Are there a lot of transactions, in average, for a given manager? In other words, how concerned with performance are you? If not so much, you could do eager loading, then filter with Skip and Take normally. — Bruno Saboia, Dec 24 '16 at 13:25
@BrunoSaboia Thanks for the response. Yes. A manager can have a lot of transactions. Loading all the transactions can slow down the application. I believe I'm already using eager loading, but I'm not quite sure about "filtering with skip and take normally". How exactly do I do that? — Rian, Dec 24 '16 at 13:32
Rian, you are not using eager loading, you are using lazy loading. Eager loading would load all transactions for a given manager. I see that you probably don't want to use eager loading, as you have many transactions per manager. I will think of a solution which respects your needs and then come back here — Bruno Saboia, Dec 24 '16 at 13:35
@BrunoSaboia .Include expression is the definition of eager loading. https://msdn.microsoft.com/en-us/library/jj574232(v=vs.113).aspx Lazy loading would be to just call manager.Transactions without Include. And Rian: yes, please disable lazy loading. It's a nightmare to performance test and it is IMO the biggest reason why some people think EF is slow. Especially if you work with less experienced colleagues. How to disable lazy loading can be found here: http://stackoverflow.com/questions/2967214/disable-lazy-loading-by-default-in-entity-framework-4 — Igor, Dec 24 '16 at 13:41
@BrunoSaboia I have turned off lazyloading. This is why I am using .include. hvd posted a working solution below. — Rian, Dec 24 '16 at 13:54

Igor · Answer 1 · 2016-12-24T13:33:25.217

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:

Load all transactions and then filter in memory. Of course if there are a lot of transactions this might be quite inefficient.
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!

I tried your first solution but couldn't get it to work. I use the second of the other 2 options which was also suggested by hvd earlier and got it to work. Thanks for your solution but hvd posted the working solution first so I have to accept his answer as the correct answer. But thanks a lot anyway. — Rian, Dec 24 '16 at 13:53

score 0 · Accepted Answer · answered Dec 24 '16 at 13:28

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.

I actually thought about this approach but I thought that putting everything in a single query would be the most efficient way. Well, I guess I was wrong. This solution worked for me! Thanks a lot! — Rian, Dec 24 '16 at 13:50

Use skip and take inside a LINQ include

2 Answers2