C#•5mo ago

✅Shuffling db results using LINQ.

Is there a way to shuffle db results using linq? I've tried copying some code from online using .orderby(...) but it doesnt seem to work. I have this code that returns all words from a db, shuffles them all then returns just the first x but it's horribly inefficient.

c#
    [HttpGet]
    public async Task<List<WordDto>> Get(string? categoryName)
    {
        var words = _db.Words.AsQueryable();

        if (categoryName is not null)
        {
            words = words
                .Where(w => w.Category.Name == categoryName);
        }
        
        var wordsList = await words
            .Select(w => new WordDto(w.Id, w.Text, w.Length, w.Category))
            .ToListAsync();
        
        //Shuffling all words in a category. Horribly ineffcient. 
        wordsList.Shuffle();
        //Return first x words.
        return wordsList.Slice(0, 3);
    }

c#
    [HttpGet]
    public async Task<List<WordDto>> Get(string? categoryName)
    {
        var words = _db.Words.AsQueryable();

        if (categoryName is not null)
        {
            words = words
                .Where(w => w.Category.Name == categoryName);
        }
        
        var wordsList = await words
            .Select(w => new WordDto(w.Id, w.Text, w.Length, w.Category))
            .ToListAsync();
        
        //Shuffling all words in a category. Horribly ineffcient. 
        wordsList.Shuffle();
        //Return first x words.
        return wordsList.Slice(0, 3);
    }

13 Replies

ACiDCA7•5mo ago

well if the shuffling is the slowpoke why not show the code of shuffle?

Pobiega•5mo ago

uh why not let the database shuffle it? its probably faster than doing it by LINQ

Shadow Wizard Money GangOP•5mo ago

Sorry I wasn't clear. I mean the shuffle is shuffling all of the words from a category but I only need to return a few words. I didn't realise this was a thing. Just looked at sqlite3 doc and found order by random(). I'll try to figure it out after my lunch. Thank you

Pobiega•5mo ago

yep that'll do it

mtreit•5mo ago

What was your original shuffle implementation out of curiosity? The horribly inefficient one?

Shadow Wizard Money GangOP•5mo ago

c#
    public static void Shuffle<T>(this IList<T> list)  
    {  
        int n = list.Count;  
        while (n > 1) {  
            n--;  
            int k = rng.Next(n + 1);  
            T value = list[k];  
            list[k] = list[n];  
            list[n] = value;  
        }  
    }

c#
    public static void Shuffle<T>(this IList<T> list)  
    {  
        int n = list.Count;  
        while (n > 1) {  
            n--;  
            int k = rng.Next(n + 1);  
            T value = list[k];  
            list[k] = list[n];  
            list[n] = value;  
        }  
    }

I just copied it from stack overflow. In the orignal quesiton I meant shuffling the whole list of words was ineffcient, I would prefer to just shuffle the amount of words I needed so its like worst case 3^2, instead of 10^2.

mtreit•5mo ago

Ok, that's Fisher Yates which is about as efficient as you can get if you need to shuffle the entire list.

Shadow Wizard Money GangOP•5mo ago

Yeah I did skim the wiki page on Fisher Yates but I haven't sat down to figure out what it's doing

mtreit•5mo ago

Also, this seems like total premature optimization 🙂

Shadow Wizard Money GangOP•5mo ago

The number of users will be less than 1 so its all premature lol

mtreit•5mo ago

Fisher Yates shuffle of 100,000 items takes like 1 millisecond.

Shadow Wizard Money GangOP•5mo ago

I will still try to figure out shuffling in the db query instead, but will do it later.

Shadow Wizard Money GangOP•5mo ago

Getting close to a working single player mode now. Quite excited 🙂

Gaming

Programming

✅Shuffling db results using LINQ.