There's a way to get a random record in MSSQL:

USE Northwind

SELECT
   TOP 1 CategoryID,
   CategoryName FROM
   Categories
ORDER BY
   NewID() 

Which I would think would translate to:

SortClauseFactory.Create( primarykeyfieldid, SortOperator.Ascending, Guid.NewGuid().ToString() ) 

but doesn't seem to. It returns a syntax error in the SQL. (Yes, the primary key is uniqueidentifier.) I tried doing it without the .ToString() but it won't build. Can you not sort on a uniqueidentifier?

Is there a better way? The point is to get a random record.

In the order by clause, it should pick the alias, odd... could you determine what the actual query was that was generated?

Yes, its:

Query: \r\nSELECT TOP 1 [adgracedb].[dbo].[AdvertiserAdvertisement].[advertiseradvertisementid] AS [Advertiseradvertisementid],[adgracedb].[dbo].[AdvertiserAdvertisement].[advertiseradgroupid] AS [Advertiseradgroupid],[adgracedb].[dbo].[AdvertiserAdvertisement].[createddatetime] AS [Createddatetime],[adgracedb].[dbo].[AdvertiserAdvertisement].[headline] AS [Headline],[adgracedb].[dbo].[AdvertiserAdvertisement].[descriptionone] AS [Descriptionone],[adgracedb].[dbo].[AdvertiserAdvertisement].[descriptiontwo] AS [Descriptiontwo],[adgracedb].[dbo].[AdvertiserAdvertisement].[displayurl] AS [Displayurl],[adgracedb].[dbo].[AdvertiserAdvertisement].[destinationurl] AS [Destinationurl] FROM [adgracedb].[dbo].[AdvertiserAdvertisement] ORDER BY 615d8c76-ed4c-4523-ae01-4dd90a6d25a6.[advertiseradvertisementid] ASC\r\n\r\nParameters:\r\n

The function is:

        static public AdvertiserAdvertisementEntity GetRandomAd()
        {
            AdvertiserAdvertisementEntity mResult = new AdvertiserAdvertisementEntity();

            EntityCollection mWorking = new EntityCollection( new AdvertiserAdvertisementEntityFactory() );
            DataAccessAdapter mAdapter = new DataAccessAdapter( CDatabase.connectionstring() );
            RelationPredicateBucket mBucket = new RelationPredicateBucket();
            ISortExpression mSorter = new SortExpression();

            mSorter.Add( SortClauseFactory.Create( adgrace.db.AdvertiserAdvertisementFieldIndex.Advertiseradvertisementid, SortOperator.Ascending, Guid.NewGuid().ToString() ) );

            try
            {
                mAdapter.FetchEntityCollection( mWorking, mBucket, 1, mSorter );
            }
            catch( ORMQueryExecutionException Ex )
            {
                Console.WriteLine( Ex.Message );
            }
            finally
            {
                mAdapter.Dispose();
            }

            if ( mWorking.Count == 1 )
            {
                mResult = (AdvertiserAdvertisementEntity)mWorking[0];
            }

            return mResult;
        }

And the exception message is:

An exception was caught during the execution of a retrieval query: Line 1: Incorrect syntax near 'a20607d'.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

(Note: the exception message was from a different run of the app, so the "a20607d" portion does not correspond to the above randomly generated Guid in the SQL statement.)

change:

mSorter.Add( SortClauseFactory.Create( adgrace.db.AdvertiserAdvertisementFieldIndex.Advertiseradvertisementid, SortOperator.Ascending, Guid.NewGuid().ToString() ) );

in

mSorter.Add( SortClauseFactory.Create( adgrace.db.AdvertiserAdvertisementFieldIndex.Advertiseradvertisementid, SortOperator.Ascending, "NewID()") );

Code used (there are no other sorts):

mSorter.Add( SortClauseFactory.Create( adgrace.db.AdvertiserAdvertisementFieldIndex.Advertiseradvertisementid, SortOperator.Ascending, "NewID()" ) );

Exception:

An exception was caught during the execution of a retrieval query: Line 1: Incorrect syntax near '.'.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

Query:

Query: \r\nSELECT TOP 1 [adgracedb].[dbo].[AdvertiserAdvertisement].[advertiseradvertisementid] AS [Advertiseradvertisementid],[adgracedb].[dbo].[AdvertiserAdvertisement].[advertiseradgroupid] AS [Advertiseradgroupid],[adgracedb].[dbo].[AdvertiserAdvertisement].[createddatetime] AS [Createddatetime],[adgracedb].[dbo].[AdvertiserAdvertisement].[headline] AS [Headline],[adgracedb].[dbo].[AdvertiserAdvertisement].[descriptionone] AS [Descriptionone],[adgracedb].[dbo].[AdvertiserAdvertisement].[descriptiontwo] AS [Descriptiontwo],[adgracedb].[dbo].[AdvertiserAdvertisement].[displayurl] AS [Displayurl],[adgracedb].[dbo].[AdvertiserAdvertisement].[destinationurl] AS [Destinationurl] FROM [adgracedb].[dbo].[AdvertiserAdvertisement] ORDER BY NewID().[advertiseradvertisementid] ASC\r\n\r\nParameters:\r\n

Very strange. What's the "ORDER BY NewID().[advertiseradvertisementid] ASC" all about?

I think I'm going about this all wrong.

Again, the goal is to get a random record. Websites recommend the query given at the top, with

ORDER BY NewID() 

where NewID() acts as a sortable column, though it's really not. I find nothing in the MSSQL documentation that shows why this should work, but it does. Go figure.

So, my using

mSorter.Add( SortClauseFactory.Create( adgrace.db.AdvertiserAdvertisementFieldIndex.Advertiseradvertisementid, SortOperator.Ascending, "NewID()" ) );

is wrong. What I really want is:

mSorter.Add( SortClauseFactory.Create( "NewID()", SortOperator.Ascending ) );

Though I don't know if that's possible in any way. Perhaps a new SortClauseFactory override?

Sorry, my bad, I should have known. The alias value of course replaces teh table name, not the entire field.

You can work around this though. Implement in a tiny class ISortClause, which emits NEWID() Then you're set Grab the code from the orm support classes for an example

Will do. I'll let you know the results later.

I do like to hear "there's a workaround..."

Did you manage to get this working? If so do you fancy sharing what you ended up with?

Cheers

Gareth

I played around with this in 2.0, and got this to work with SQL Server 2000

ISortClause randomClause = new EntityField("NEWID()", new Expression()) | SortOperator.Ascending;

THanks for your contribution !