Entity Framework Take() returns more elements

Question

Can someone please explain why following query returns list of 8 vessels?

 var vessels = await db.Vessels
                       .Include(m => m.Images.Select(c => c.Activity))
                       .Include(m => m.VesselAddresses.Select(c => c.Address))
                       .Where(m => m.Images.Any(c => c.Activity.Active))
                       .Where(m => m.Activity.Active)
                       .Where(m => m.Listed)
                       .Where(m => m.Activity.User.Active)
                       .OrderBy(m => Guid.NewGuid())
                       .Take(4)
                       .ToListAsync();

If i remove Include(m => m.VesselAddresses.Select(c => c.Address)) or OrderBy from the query, then it works just fine and returns 4 records, but if i leave it as it is, then it returns 8 records, even i specified Take(4)

EDIT

This is almost the same query for apartments table, but this query works just fine and always returns 4 rows:

 var apartments = await db.Apartments
                        .Include(m => m.Images.Select(c => c.Activity))
                        .Include(m => m.Address)
                        .Where(m => m.Images.Any(c => c.Activity.Active))
                        .Where(m => m.Activity.Active)
                        .Where(m => m.Listed)
                        .Where(m => m.Activity.User.Active).OrderBy(m => Guid.NewGuid())
                        .Take(4)
                        .ToListAsync();

You may want to have a look at this answer: http://stackoverflow.com/a/12065589/4550393. The statement there is: "Remember, an linq-To-Entities query doesn't actually do a roundtrip to the database until a foreach or ToList() is done." May be this gives a hint. — Norman, May 04 '16 at 11:36

MikeT · Answer 1 · 2016-05-04T11:49:05.597

0

Entity Framework doesn't run the query you are making until you call the ToListAsync, hence my guess would be that you Include can't be translated into SQL so its being ignored by the query builder until after it executes the SQL which because take converts into sql as TOP, means that the include is being applied after Take

moving the .Take(4) after the .ToListAsync() should correct

also i assumed you are using .OrderBy(m => Guid.NewGuid()) to randomise the results i would suggest instead Random.NextDouble() guid is overkill for randomisation

edited May 04 '16 at 11:49

answered May 04 '16 at 11:41

MikeT

5,398
3
27
43

what do you suggest as a solution? If i place Take(2), instead of Take(4), i get a desired effect, but then code looks missleading. Also, I have apartments table with almost exact same query, and it works just fine. I will update my question with that info. – Robert May 04 '16 at 11:47
Isn't moving Take(4) after ToListAsync() overkill and will fetch thousands of records to memory instead of just 4? – Robert May 04 '16 at 11:52

Entity Framework Take() returns more elements

1 Answers1