Shuffle the results of a LINQ query based on SessionID-CodePudding

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?

CodePudding user response：

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();