Randomizing LINQ to SQL Query Results

Another stumper solved today.. Something as simple as randomizing LINQ results

It's not something built-in. There is no Random query operator provided by Microsoft. In addition, it can't be done simply by using the System.Random class, because everything in a LINQ to SQL query must be translatable to SQL. That being the case...Let's detail the solution that uses a SQL user-defined function. The most common way to sort records randomly is to use the NEWID SQL Server function. This is what this solution uses.
First, create the following view:

CREATE VIEW RandomView
AS
SELECT NEWID() As ID
Then create the following function that uses the view:CREATE FUNCTION GetNewId
(
)
RETURNS uniqueidentifier
AS
BEGIN
RETURN (SELECT ID FROM RandomView)
END

The view is required because it's not possible to directly use NEWID in a scalar function.

And now we can use this in any LINQ to SQL query to retrieve truly random records.

 Dim p = (From u In db.DBUserPhotos
                Join t2 In db.DBUsers On u.UserID Equals t2.ID
        Where (u.IsMain = True)
        Where (t2.IsActive = True)
        Where (u.IsFlagged = False)
        Where (t2.IsFlagged = False)
        Where (u.IsPublic = True)
        Where (u.IsEnabled = True)
        Order By db.GetNewId
                Select New MainPhotoInfo With { _
                    .ID = t2.ID, _
                    .Username = t2.Username, _
                    .PhotoLocation = u.PhotoLocation, _
                    .Email = t2.Email}).Take(10)

Happy Coding