ToList() incorrect evaluation when using Include

Question

Consider I have the following snippet:

IQueryable<BaseEntity> someQueryble = ApplicationDbContext.BaseEntities;
var randomQueryable = someQueryable.OfType<RandomEntity>()
                                   .Include(prop => prop.SomeRandomNavProp)
                                   .OrderBy(prop => prop.Id);

BaseEntities is just DbSet from ApplicationDbContext from EF 6. RandomEntity inherits from BaseEntity (Table-Per-Type hierarchy)

Then, something strange happens:

randomQueryable.Take(10).Count();
10
randomQueryable.Take(10).ToList().Count;
20

When Include is removed, everything works fine. Why does .include affect on ToList and how can I resolve that?

Edit: the generated SQL:

http://pastebin.com/SS3WD7P6

Attention - it's from production datababase, so readability can hurt. First query is about Count and the second one - about ToList()

Just want to confirm, you're certain you're using EF6 and not EF Core? Core has a few similar looking bugs to this. — cost, Nov 02 '16 at 19:08
Would help if you enable logging (ApplicationDbContext.Database.Log = Console.WriteLine) and share the output in both cases. — Evk, Nov 02 '16 at 19:25
Can't reproduce. Apparently the `Include` is ignored in the first scenario, but in my test `ToList` with `Include` also works. Anything unusual not shown there? — Ivan Stoev, Nov 02 '16 at 19:25
@IvanStoev try as `NoTracking`. I am preparing output right now for queries. — pwas, Nov 02 '16 at 19:30
This is hard to reproduce with the given information. Can you create a [mcve] including relevant models, context and configuration? What data is _in_ the list if you remove the `Count()`? — CodeCaster, Nov 02 '16 at 19:35
@IvanStoev and what about `OrderByDescending(k => Guid.NewGuid())` instead of showed one? — pwas, Nov 02 '16 at 19:39
Ha, that definitely is the key - the `Include` internally uses `ORDER BY` for correctly correlating the data, so it requires deterministic ordering. — Ivan Stoev, Nov 02 '16 at 19:43
@IvanStoev Hmmmmm - usually `Take` throws error when non-deterministic order was applied.. Qustion is.. how to get ten random records from DB with `Include` clausule... Can it be possible? — pwas, Nov 02 '16 at 19:44
Although this also works in my test. We really need to see the generated SQL with `ToList` call. Basically the output of `randomQueryable.Take(10).ToString()`. — Ivan Stoev, Nov 02 '16 at 19:46
@IvanStoev see output SQL (edited question). It will hurt (it's big, nasty etc) :) — pwas, Nov 02 '16 at 19:50
Man, that's a huuuge query! Could you verify if it works correctly w/o random ordering (although I guess that's the idea)? — Ivan Stoev, Nov 02 '16 at 19:56
Really hurts :) But I see it basically does "select top 10 ... UNION ALL select top 10 ..." so no wonder you have 20 records. Why it does that is another story though, cannot have a clue for such huge query. Is it really produced by exact LINQ query you provided? — Evk, Nov 02 '16 at 19:58
@IvanStoev - when Guid.NewGuid() is replaced with property (f.e entity.Id), it... works. No idea why Guid.NewGuid() prodces strange result. So question is - how can I reimplement it - getting random rows with `Include`... — pwas, Nov 02 '16 at 20:03
@Evk nice catch! Yep - only 7 Includes, OrderByDescending(k => Guid.NewGuid()) and Take.. No more (entities are just more complicated than showed above). — pwas, Nov 02 '16 at 20:04
Ahh. Seems related: http://stackoverflow.com/questions/7952147/entity-framework-include-orderby-random-generates-duplicate-data — pwas, Nov 02 '16 at 20:07
@pwas Yep, that's exactly the same. No good solution though - I guess you are trying to page on `IQueryable`? If not, i.e. if you are going to materialize the result, then some loading tricks instead of `Include` might help. Let me know if that's the case, but probably that should be a different question. — Ivan Stoev, Nov 02 '16 at 20:27
@IvanStoev - yes, paging is done on IQueryable. But what is more - I have chnaged ordering function. See my answer :) Thanks for help! — pwas, Nov 02 '16 at 20:31

score 1 · Accepted Answer · edited May 23 '17 at 11:53

Thanks to @Evk and @IvanStoev found a nice post about ordering by Guid.NewGuid(): https://stackoverflow.com/a/8140495/5417374

Replacing order function with the following extension method solved problem (based on other answer in mentioned question):

public static IOrderedQueryable<BaseEntity> Randomize(
                         this IQueryable<BaseEntity> queryable)
{
    var seed = Random.NextDouble();

    return queryable.OrderBy(o => SqlFunctions.Checksum(o.Id * seed))
                     .ThenBy(o => o.Id);
}

ToList() incorrect evaluation when using Include

1 Answers1