Linq. Select.

[shapper]

Hello,

How do I select 10 random records from a table?

Thanks,
Miguel

 

[Lasse Vågsæther Karlsen]

Hello,

How do I select 10 random records from a table?

Thanks,
Miguel

The SQL way is to order by a GUID and select the top 10 rows.

Unfortunately, SQL is made to retrieve the rows you specify, not just
"any 10 rows, doesn't matter which ones".

I'm afraid you're going to have to come up with a way to specify the 10
random rows you want.

Could you for instance retrieve the primary key of all the rows, and
then grab 10 random items from that array and retrieve the full rows for
those keys?

 

[Marc Gravell]

I'll give it a try tomorrow, but you could try creating a UDF that
returns a GUID, map it to a DataContext method, mark it as composable
in the LINQ attributes, and use the mapped method in the query...

I'll give it a try tomorrow ;-p

Marc

 

[Marc Gravell]

Darn:

Msg 443, Level 16, State 1, Procedure fnRand, Line 6
Invalid use of side-effecting or time-dependent operator in 'newid'
within a function.


So no; I can't do it either...

 

[Frans Bouma [C# MVP]]

The SQL way is to order by a GUID and select the top 10 rows.

Unfortunately, SQL is made to retrieve the rows you specify, not just
"any 10 rows, doesn't matter which ones".

I'm afraid you're going to have to come up with a way to specify the
10 random rows you want.

Could you for instance retrieve the primary key of all the rows, and
then grab 10 random items from that array and retrieve the full rows
for those keys?

the query is:
select top 10 * from customers order by newid() asc

so what should be done is calling newid() in the orderby clause of the
linq query. THis isn't mapped by default, but you can define your own
function mapping, so after that's done it should be possible to orderby
on that function call.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[Marc Gravell]

I tried mapping NEWID via an UDF, but without joy (see last post); can
you explain how to map this more directly? Or maybe indirectly if it works?

Marc

 

[Marc Gravell]

Ignore me; I found it...

 

[Marc Gravell]

Well, whadya know; you don't need the UDF! It just works...

Marc

[Function(Name="NEWID", IsComposable=true)]
public Guid Random()
{
return Guid.NewGuid();
}

....
ctx.Log = Console.Out;
var query = from x in ctx.Suppliers
orderby ctx.Random()
select x;
var results = query.ToArray();
....
SELECT [t0].[SupplierID], [t0].[CompanyName], [t0].[ContactName],
[t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region],
[t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax], [t0].[HomePage]
FROM [dbo].[Suppliers] AS [t0]
ORDER BY NEWID()