Entity Framework Include OrderBy random generates duplicate data

by

As one can sort it out by reading AakashM answer and Nicolae Dascalu answer, it strongly seems Linq OrderBy requires a stable ranking function, which NewID/Guid.NewGuid is not.

So we have to use another random generator that would be stable inside a single query.

To achieve this, before each querying, use a .Net Random generator to get a random number. Then combine this random number with a unique property of the entity to get randomly sorted. And to ‘randomize’ a bit the result, checksum it. (checksum is a SQL Server function that compute a hash; original idea founded on this blog.)

Assuming Person Id is an int, you could write your query this way :

// Random instances should be stored and reused, not instanciated at each usage.
// But beware, it is not thread safe. If you want to share it between threads, you
// would have to use locks, see its documentation.
// https://learn.microsoft.com/en-us/dotnet/api/system.random.
// But using locks is a bad idea for scalability, especially in a Web context.
var randomGenerator = new Random();
// ...

var rnd = randomGenerator.NextDouble();
var persons = db.Persons
    .Include(p => p.Addresses)
    .OrderBy(p => SqlFunctions.Checksum(p.Id * rnd));

Like the NewGuid hack, this is very probably not a good random generator with a good distribution and so on. But it does not cause entities to get duplicated in results.

Beware:
If your query ordering does not guarantees uniqueness of your entities ranking, you must complement it for guarantying it. By example, if you use a non-unique property of your entities for the checksum call, then add something like .ThenBy(p => p.Id) after the OrderBy.
If your ranking is not unique for your queried root entity, its included children may get mixed with children of other entities having the same ranking. And then the bug will stay here.

Note:
I would prefer use .Next() method to get an int then combine it through a xor (^) to an entity int unique property, rather than using a double and multiply it. But SqlFunctions.Checksum unfortunately does not provide an overload for int data type, though the SQL server function is supposed to support it. You may use a cast to overcome this, but for keeping it simple I finally had chosen to go with the multiply.

More Related Contents:

  1. Why LINQ to Entities does not recognize the method ‘System.String ToString()?
  2. Converting flattened hierarchical data from SQL Server into a structured JSON object with C#/Linq
  3. One DbContext per request in ASP.NET MVC (without IOC container)
  4. Multiple DB Contexts in the Same DB and Application in EF 6 and Code First Migrations
  5. using stored procedure in entity framework
  6. Should services always return DTOs, or can they also return domain models?
  7. ASP.NET MVC3 and Entity Framework Code first architecture
  8. Filter/Search using Multiple Fields – ASP.NET MVC
  9. How to change type of id in Microsoft.AspNet.Identity.EntityFramework.IdentityUser
  10. Saving Many to Many relationship data on MVC Create view
  11. How to group by on 2 child entities and get total of both this child entities?
  12. Defining multiple Foreign Key for the Same table in Entity Framework Code First
  13. ‘Microsoft.SqlServer.Types’ version 10 or higher could not be found on Azure
  14. LINQ to Entities does not recognize the method ‘System.Web.Mvc.FileResult’
  15. The operation cannot be completed because the DbContext has been disposed using MVC 4
  16. Use Entity framework I want to include only first children objects and not child of child(sub of sub)
  17. Export Model to DataTable
  18. keyword not supported data source
  19. Only primitive types or enumeration types are supported in this context
  20. LINQ OrderBy not ordering .. changing nothing .. why?
  21. Model backing a DB Context has changed; Consider Code First Migrations
  22. Incompatible Data Reader Exception From EF Mapped Objects
  23. There is already an object named ‘AspNetRoles’ in the database
  24. The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value
  25. The type or namespace name ‘DbContext’ could not be found [closed]
  26. The model backing the ‘ApplicationDbContext’ context has changed since the database was created
  27. EntityType ‘Category’ has no key defined. Define the key for this EntityType [duplicate]
  28. Strongly-Typed ASP.NET MVC with ADO.NET Entity Framework [closed]
  29. DataAnnotations validation (Regular Expression) in asp.net mvc 4 – razor view
  30. RenderAction RenderPartial

Leave a Comment