Select Random Rows In A TypedView

Posts   
 
    
hankddog
User
Posts: 52
Joined: 08-Apr-2005
# Posted on: 01-Mar-2022 18:08:13   

Is it possible to select random rows in a TypedView using something like newid()

I want to select 16 records out of 100 that are returned

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39614
Joined: 17-Aug-2003
# Posted on: 02-Mar-2022 09:43:29   

You can build a sortexpression based on a NEWID() call:

var field = new EntityField2("NewIDSorter", new Expression());
field.Alias = "NEWID()";
var sorter = new SortExpression(new SortClause(field, null, SortOperator.Ascending));

EntityCollection customers = new EntityCollection(new CustomerEntityFactory());
adapter.FetchEntityCollection(customers, filter, 0, sorter);

Which results in:

SELECT [Northwind].[dbo].[Customers].[Address],
       [Northwind].[dbo].[Customers].[City],
       [Northwind].[dbo].[Customers].[CompanyName],
       [Northwind].[dbo].[Customers].[ContactName],
       [Northwind].[dbo].[Customers].[ContactTitle],
       [Northwind].[dbo].[Customers].[Country],
       [Northwind].[dbo].[Customers].[CustomerID] AS [CustomerId],
       [Northwind].[dbo].[Customers].[Fax],
       [Northwind].[dbo].[Customers].[Phone],
       [Northwind].[dbo].[Customers].[PostalCode],
       [Northwind].[dbo].[Customers].[Region]
FROM   [Northwind].[dbo].[Customers]
WHERE  (([Northwind].[dbo].[Customers].[Country] = 'France' /* @p1 */))
ORDER  BY NEWID() ASC

The same can be done for typedview fetches

Frans Bouma | Lead developer LLBLGen Pro