Today I (/ later together with some colleagues) spent almost 4 hours trying to improve a Entity Framework LINQ to SQL query.
The initial problem was, that one of our List API endpoints took longer the more you "page" (besides the long response time it had anyways).
So after
- brainstorming in the team
- brainstorming alone
- hacking around and
- shouting at screens
- googling
we
- got nothing optimized
- got confused about what EF does
- lost the believe in our development skills

So Entity Framework is really a nice thing. But as soon as you look deeper, trying to figure out what it really does between ToList() and "yeah my data arrived" it is just....demonic.

If it takes longer the more page I believe every page works through from the beginning.

If you use skip and take on an enumerable, remember that they do not always remember between calls.

For paging, use
Context.Skip(page * perpage).Take(perpsge)

That should construct a better query.

@Voxera Yes we are already using the Skip/Take approach. This translates to an OFFSET/FETCH in the SQL query. There are different options on the internet whether this offset is applied WHILE the actual query or to the result of it. That would explain that it takes longer when paging "deeper"

@fermar7 it also depends on if you “materealize” the result first.

If you add it directly to the ienumerable or iqueryable it should go inte the sql, but if you have a to list in there it will be on the result.

If you need to traverse the result multiple times, make sure you do I to list first or it will query the database multiple times.

Ienumerable is a lazy query that is executed only when you try to use the result and if you for example loop 10 times, picking from an ienumerable each iteration you might end up doing 10 queries to the database.

So if possible, try the code in linqpad, it should have a separate resulttab with all generated queries which can be helpful.
And if that is not possible try sql profiler to log queries instead.

Once you know the queries asked you either see the problem or can try the queries in management studio to test if they get slower and probably figure out why.

Entity framework is very powerful but complex and large databases can make it do strange queries, especially if statistics is outdated.

Also look over indexes, bad or missing indexes is a killer for any database beyond a couple of 1000 rows.