LINQ .Take() returns more elements than requested

Question

We have a simple LINQ-to-Entities query that should return a specific number of elements from particular page. The example of the request can be:

var query = from r in records
            orderby r.createdDate descending
            select new MyObject()
            { ... };

//Parameters: pageId = 8, countPerPage = 10
List<MyObject> list = query.Skip(pageId * countPerPage).Take(countPerPage);

The above example works great in most of the cases, but sometimes the list has more than 10 elements. This doesn't seem to be always true and depends from the database data. For example, when we request the page 10 and pass countPerPage as 10, we're getting 10 elements. But when we request the page 12 and pass countPerPage as 10, we're getting 11 elements. Then when we ask for page 21, we're getting 10 elements once again.

Is there any possible reason why that happens?

UPDATE: The query, for sure, is not so simple, as it is in example, and contains the sub-queries.

And here's a more complete example:

var elementsQuery = from m in entityContext.elements
                    where m.elementSearchText.Contains(filter)
                    orderby m.CreatedDate descending
                    select new DataContracts.ElementForWeb()
                    {
                        FirstName = m.FirstName,
                        LastName = m.LastName,
                        Photos = (from p in m.Photos select p.ID),
                        PlacesCount = m.Childs.Where(x => x.Place != null).Count() + ((m.MainChild != null)?1:0),
                        SubElements = (
                            from t in m.Childs
                            orderby t.CreatedDate descending
                            select new DataContracts.ChildForWeb()
                            {
                                CommentsCount = t.ChildComments.Count,
                                Photos = (from p in t.Photos select p.ID),
                                Comments = (from c in t.ChildComments
                                orderby c.CreatedDate descending
                                select new DataContracts.CommentForWeb()
                                {
                                    CommentId = c.ID,
                                    CommentText = c.CommentText,
                                    CreatedByPhotoId = c.Account.UserPhoto,
                                    CreatedDate = c.CreatedDate,
                                }).Take(5)
                            }).Take(5)
                      };

List<DataContracts.ElementForWeb> elements = 
    new List<DataContracts.ElementForWeb>(
        elementsQuery
           .Skip(pageId * countPerPage)
           .Take(countPerPage));

UPDATE2: Here's even more interesting test.

        for (var i = 0; i < 10; i++) {
            Service.GetElementsForWebPaged(12, 10, "",
                function (result) {
                    console.log("Elements returned: " + result.length);
                },
                function (error) {
                });
        }

The results are "awesome"!

Elements returned: 11
Elements returned: 11
Elements returned: 10
Elements returned: 11
Elements returned: 11
Elements returned: 10
Elements returned: 11
Elements returned: 10
Elements returned: 11
Elements returned: 11

This is an odd one; is that the complete extend of your select statement? Are you able to provide any sample data? — Richard, Aug 16 '12 at 08:24
That is strange. Are you sure you aren't taking on a group, and then unbundling, e.g. in a "with ties" type scenario? http://stackoverflow.com/questions/1342848/does-linq-to-sql-have-a-with-ties-option — StuartLC, Aug 16 '12 at 08:26
@Denis Mazourick: did you tried to execute resultng `raw SQL` and see if it happens again ? — Tigran, Aug 16 '12 at 08:37
Probably, simplifying the query and testing it separately could help finding the reason — horgh, Aug 16 '12 at 08:49
If I remove the sub-query, I don't get more than 10. But, as I noted before, when I manipulate with different pageId, I'm getting 10 most of time and 11 only for particular pageId-s. — Denis Mazourick, Aug 16 '12 at 08:53
@horgh: Yes, the query simplification will probably help. I agree. But it is interesting WHY that happens at all. — Denis Mazourick, Aug 16 '12 at 08:54
Can you give more information about those sub-items for those particular pageId's? — Maarten, Aug 16 '12 at 09:11
I would follow the comment by Daren Thomas and Tigran, i.e. try examine the raw sql query generated by ef then — horgh, Aug 16 '12 at 10:48
@horgh: The interesting thing is - in all 10 iterations the raw SQL is exactly the same and returns always the same number of data and the same data. But when the Entity Framework converts these data into entities, it sometimes produces 10 and sometimes 11 root objects — Denis Mazourick, Aug 16 '12 at 11:00
@Tigran: I had examined that the raw SQL in all 10 iterations was the same and had always returned 60 records (because it includes the child objects as well) — Denis Mazourick, Aug 16 '12 at 11:02
Linq to entities uses Row_Number() to do its paging, right? If you have exactly equal dates there might be some non-deterministic stuff going on. Try adding a second column to order by like the key or something ie: orderby m.CreatedDate descending, m.ElementId. — carlsb3rg, Aug 16 '12 at 21:44
@carlsb3rg: Sure, I didn't try ".ToList" - the DB can be really large and loading all the data first and then limiting doesn't make sense. I will try the second column though. — Denis Mazourick, Aug 17 '12 at 07:45
The `.ToList()` was more of a debugging suggestion than a production suggestion ;) — carlsb3rg, Aug 17 '12 at 11:48
Generally, what I did - I rewrote the query with some drawback in amount of data that will be returned. Generally, I made a Skip and Take for the main query only, but return all child elements for those 10 main records and then build the limited sets in C#. Not so good, but helps me so far. The next step will be to write the own SP that will return the necessary data and then map it to entities. — Denis Mazourick, Aug 17 '12 at 18:05
Try storing date more often, store the elements of the basic query. Check if everything is there. Store the elements after the skip, check if the right elements and the right amount where skipped. — MrFox, Aug 21 '12 at 10:20
I'm having the same problem and there's no way I'm calling ToList() before Skip(x) and Take(x); it's way too expensive and completely defeats the purpose of paging. My solution: Account for the varying number of books in the frontend, where it really doesn't matter if there is 10 or 13 books. :) — David Bachmann Jeppesen, Feb 04 '16 at 20:08

score 1 · Accepted Answer · answered Aug 22 '12 at 02:25

1

It would be difficult to test this answer because it depends on your schema and test data, etc. But I believe you may be having a problem mixing up IQueryAble results with IEnumerable results.

Remember, an linq-To-Entities query doesn't actually do a roundtrip to the database until a foreach or ToList() is done.

I would suggest first breaking this into pieces:

var elementsQuery = from m in entityContext.elements
                    where m.elementSearchText.Contains(filter)
                    orderby m.CreatedDate descending;

var elements = elementsQuery.Skip(pageId * countPerPage).Take(countPerPage)).ToList();

Then build you projection...

var elementsForWeb = from m in elements
                     select new DataContracts.ElementForWeb()
                     {
                     ...
                     }

answered Aug 22 '12 at 02:25

oshea00

181
6

That's actually what I finally did. However, that means that we're doing much more roundtrips to the database (for each element in result list), which may actually be more costly than transmitting extra data. – Denis Mazourick Aug 22 '12 at 08:59
Yes. Possibly. There are some really good tools for profiling the resulting database SQL. (good article here) http://msdn.microsoft.com/en-us/magazine/gg490349.aspx When you loop through the elementsForWeb, that will be with the in-memory List. However, the queries you do in the projection won't happen until you've foreached or ToListed, etc, that set of items. – oshea00 Aug 23 '12 at 06:45
1

I'm accepting this answer as it was mostly close to what we finally do. I am thinking about posting the issue to Microsoft Connect though. – Denis Mazourick Nov 14 '12 at 18:55

LINQ .Take() returns more elements than requested

1 Answers1

Linked