C#•2mo 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•2mo ago

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

Pobiega•2mo ago

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

Servant of Time•2mo 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•2mo ago

yep that'll do it

mtreit•2mo ago

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

Servant of Time•2mo 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•2mo ago

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

Servant of Time•2mo 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•2mo ago

Also, this seems like total premature optimization 🙂

Servant of Time•2mo ago

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

mtreit•2mo ago

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

Servant of Time•2mo ago

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

Servant of Time•2mo ago

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

Gaming

Programming

✅Shuffling db results using LINQ.