Shuffle the results of a LINQ query based on SessionID

Question

I'm working on an ASP.NET MVC application with Entity Framework 6 and a SQL Server database.

I'm trying to shuffle the results of a query by adding a SortingCode which I'd like to assign a value based on the current SessionId, so that every time the returned rows are shuffled without affecting the pagination. SortingCode in this attempt is a string, but it can be any type, as long as it allows me to get shuffled results. I have something like this:

var sessionId = Session.SessionID.GetHashCode();
var rnd = new Random(sessionId);

var query = (from l in _context.Adverts
             select new AdvertSummary
             {
                 Id = l.Id,
                 Title = l.Title,
                 Description = l.Description,
                 SortingCode = l.Title.OrderBy(x => rnd.Next()).ToString(),
             });

The IQueryable result is then converted into a list later on in my code with:

var pagedResults = query.Skip(skip).Take(pageSize).ToList();

The above attempt with the Random class doesn't work, and results in an error

DbExpressionBinding requires an input expression with a collection ResultType

Is there anything that I can do to get shuffled results?

What is `_repository.MyTable()`? And what is `l.Title.OrderBy(x => rnd.Next()).ToString()` supposed to produce? — Gert Arnold, Nov 03 '21 at 18:48
Many thanks for your attention. I've updated the post, hope it's clearer now. — AMKC7, Nov 03 '21 at 21:06
There is solution how to [sort randomly](https://stackoverflow.com/questions/7781893/ef-code-first-how-to-get-random-rows), but I also cannot understand why you sort characters of `Title`. — Svyatoslav Danyliv, Nov 04 '21 at 05:41
@SvyatoslavDanyliv the solution in the post you linked uses `something.OrderBy(r => Guid.NewGuid()).Take(5)`. Guid.NewGuid() doesn't work in pagination, as the order would be recreated at each change of page. I need to retain the same order while scrolling through the pages. That's why I need a sorting based on SessionID. — AMKC7, Nov 04 '21 at 14:25

score 1 · Accepted Answer · answered Nov 04 '21 at 14:36

1

I would suggest to use SqlFunctions.Checksum for such task. SortingCode will be nearly close to the seeded Random.

var sessionId = Session.SessionID;

var query = 
    from l in _context.Adverts
    select new AdvertSummary
    {
        Id = l.Id,
        Title = l.Title,
        Description = l.Description,
        SortingCode = SqlFunctions.Checksum(sessionId, l.Title)
    };

var pagedResults = query
    .OrderBy(x => x.SortingCode)
    .ThenBy(x => x.Id)
    .Skip(skip)
    .Take(pageSize)
    .ToList();

answered Nov 04 '21 at 14:36

Svyatoslav Danyliv

21,911
3
16
32

Svyatoslav, it worked, thanks a lot! I also had to add a change in my Global.asax.cs as stated [here](https://stackoverflow.com/a/2874174/5740294), as my SessionID was changing at each request: ` protected void Session_Start(Object sender, EventArgs e) { Session["init"] = 0; } ` – AMKC7 Nov 04 '21 at 20:34

Shuffle the results of a LINQ query based on SessionID

1 Answers1