Randomized Linq2SQl query that's return too heavy SQL

Question

I use the following to implement Random ordered results in Linq2SQL:

  partial class OffertaDataContext
    {
        [Function(Name = "NEWID", IsComposable = true)]
        public Guid Random()
        {
            throw new NotImplementedException();
        }
    }

In the following query:

IEnumerable<Enquirys> visibleOnSite =  Enquirys.Where(e => 
    e.EnquiryPublished != null && 
    e.Status != 0 &&
    e.Status != 3 &&
    e.Status != 4 &&
    e.Status != 5
);

var linq = (
            from e in db.EnquiryAreas
            from w in db.WorkTypes
            where
            e.SeoPriority != 0 &&
            e.HumanId != null &&
            w.SeoPriority != 0 &&
            e.HumanId != null &&
            e.SeoPriority * w.SeoPriority > 20 &&
            visibleOnSite.Any(f => f.WhereId == e.Id && f.WhatId == w.Id)
            select new
            {
                HWhereId = e.Id,
                WhereDescription = e.DescriptionText,
                HWhatId = e.Id,
                WhatDescription = e.DescriptionText
            }
        ).OrderBy(e => db.Random()).Take(14);

I have a problem with the SQL result:

SELECT [t3].[Id] AS [HWhereId], [t3].[DescriptionText] AS [WhereDescription], [t3].[Id2] AS [HWhatId], [t3].[DescriptionText2] AS [WhatDescription]
FROM (
    SELECT TOP (7) [t0].[Id], [t0].[DescriptionText], [t1].[Id] AS [Id2], [t1].[DescriptionText] AS [DescriptionText2]
    FROM [dbo].[EnquiryAreas] AS [t0], [dbo].[WorkTypes] AS [t1]
    WHERE ([t0].[SeoPriority] <> 0) AND ([t0].[HumanId] IS NOT NULL) AND ([t1].[SeoPriority] <> 0) AND ([t0].[HumanId] IS NOT NULL) AND (([t0].[SeoPriority] * [t1].[SeoPriority]) > 20) AND (EXISTS(
        SELECT NULL AS [EMPTY]
        FROM [dbo].[Enquirys] AS [t2]
        WHERE ([t2].[EnquiryPlace] = ([t0].[Id])) AND ([t2].[EnquiryWorkType] = ([t1].[Id])) AND ([t2].[EnquiryPublished] IS NOT NULL) AND ([t2].[Status] <> 0) AND ([t2].[Status] <> 3) AND ([t2].[Status] <> 4) AND ([t2].[Status] <> 5)
        ))
    ORDER BY NEWID()
    ) AS [t3]
ORDER BY NEWID()

Where everything works fine if I remove the inner ORDER BY NEWID(). (With both, the query takes too long to finish). Is there any way I can modify my Linq2SQL to only result in the outer ORDER BY NEWID(). If not, any other workaround? Other ways to implement Random?

Answer 1

It all depends on how random do you want your data. If a pseudo-random result is acceptable then you can use the TABLESAMPLE option of SELECT, see Limiting Result Sets by Using TABLESAMPLE. Please read the link in MSDN as it explains some of the limitations around TABLESAMPEL, specially when used in conjuction with JOINs.

If a truly random sample is needed, then there are alternatives to ORDER BY NEWID(). A slightly better solution is to use NEWID() in the WHERE clause, combined with a row dependent scalar to force evaluation at each row:

SELECT * FROM ...
WHERE 0.01 >= CAST(CHECKSUM(NEWID(), ID) & 0x7fffffff AS float)

This does not sort the table, but it still does a prety hefty scan. Throw the LINQ paging on top of it and you won't be much better than the ORDER BY.

If neither solution is acceptable, then perhaps is time to revisit the requirements, this time with a better understanding of relational algebra 101. After a good grasp on the concepts of relation, tuple and key, a healthy natural urge to push back on the idea of 'random relation' should solve your problem...