Random sort list with LINQ and Entity Framework in vb.net

Question

I've seen many examples in LINQ but i'm not able to reproduce the same result in vb.net.

I have following code:

Dim context As New MyModel.Entities()

Dim rnd As New System.Random()

Dim gardens As List(Of Tuin) = (From t In context.Gardens Where _
                                        t.Approved = True And _
                                        Not t.Famous = True _
                                        Order By rnd.Next() _
                                        Select t).ToList()

But i receive an error when binding this list to a control.

LINQ to Entities does not recognize the method 'Int32 Next()' method, and this method cannot be translated into a store expression.

Any suggestion on how to get this to work?

Answer 1

You can't use a Random object in the query like that, as the object exists in your VB code, not in the database.

Get the result into a list first, then scramble it. It's much more efficient to use a scrambling algorithm like Fischer-Yates/Knuth than sorting on a random value:

Dim rnd as New Random()
For i As Integer = gardens.Count To 2 Step -1
  Dim pos As Integer = rnd.Next(i)
  Dim x = gardens(i - 1)
  gardens(i - 1) = gardens(pos)
  gardens(pos) = x
Next

Also, to sort on a random value you either have to know that the sorting algorithm won't ever reevaluate the relation between two given items, or you have to assign a random value to each item so that it uses the same value throughout the sorting. If the method that you tried would have been possible, you could have gotten the same bad result as for the browser choise page.