Hi,

I'm trying to retrieve a collection of products in random order using Order By NEWID(). After searching through previous threads, I tried using the following methods to generate the SortClause:

IEntityField newIdField = EntityFieldFactory.Create(Arcadian.DAL.ProductFieldIndex.Code);
newIdField.Alias = "NEWID()";
newIdField.ExpressionToApply = new Expression();

ISortExpression sorter = new SortExpression();
sorter.Add(new SortClause(newIdField, SortOperator.Ascending));

as well as

IEntityField newIdField = new EntityField("NEWID", new Expression());
newIdField.Alias = "NEWID()";

ISortExpression sorter = new SortExpression();
sorter.Add(new SortClause(newIdField, SortOperator.Ascending));

however, both throws ORMQueryExecutionException indicating "Invalid column name NEWID()"

QueryExecuted:

SELECT [dbo].[Product].[Code], [dbo].[Product].[Name], ... (skip...) FROM [dbo].[Product] ORDER BY [NEWID()] ASC

Looking at the query executed, it seems like it might be because NEWID() is enclosed within "[]", so it's treating "NEWID()" as a column name instead. I'm not quite sure what I did wrong, is there anyway to get around that? or any other way to retrieve a collection in random order? Thanks ahead for any assistance~!

LLBLGen Pro version 2.5 Final Runtime file version 2.5.8.109 SelfServicing / .Net 2.0 MSSql 2005

Actual code used:

ProductCollection col = new ProductCollection();

IEntityField newIdField = EntityFieldFactory.Create(Arcadian.DAL.ProductFieldIndex.Code);
newIdField.Alias = "NEWID()";
newIdField.ExpressionToApply = new Expression();

ISortExpression sorter = new SortExpression();
sorter.Add(new SortClause(newIdField, SortOperator.Ascending));

col.GetMulti(null, 0, sorter);

StackTrace on outer exception: " at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)\r\n at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.ExecuteMultiRowRetrievalQuery(IRetrievalQuery queryToExecute, ITransaction containingTransaction, IEntityCollection collectionToFill, Boolean allowDuplicates, IEntityFields fieldsUsedForQuery, IFieldPersistenceInfo[] fieldPersistenceInfos)\r\n at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.PerformGetMultiAction(ITransaction containingTransaction, IEntityCollection collectionToFill, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPredicate selectFilter, IRelationCollection relations, IPrefetchPath prefetchPathToUse, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize)\r\n at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.GetMulti(ITransaction containingTransaction, IEntityCollection collectionToFill, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IEntityFactory entityFactoryToUse, IPredicate selectFilter, IRelationCollection relations, IPrefetchPath prefetchPathToUse, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize)\r\n at SD.LLBLGen.Pro.ORMSupportClasses.EntityCollectionBase1.GetMulti(IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relations, IPrefetchPath prefetchPathToUse, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize)\r\n at SD.LLBLGen.Pro.ORMSupportClasses.EntityCollectionBase1.GetMulti(IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses)\r\n at Arcadian.Util.UnitTest.Main(String[] args) in C:\\Projects\\code_base\\FileUtil\\UnitTest.cs:line 487\r\n at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)\r\n at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)\r\n at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()\r\n at System.Threading.ThreadHelper.ThreadStart_Context(Object state)\r\n at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)\r\n at System.Threading.ThreadHelper.ThreadStart()"

StackTrace on InnerException " at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)\r\n at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)\r\n at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)\r\n at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)\r\n at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()\r\n at System.Data.SqlClient.SqlDataReader.get_MetaData()\r\n at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)\r\n at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)\r\n at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)\r\n at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)\r\n at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)\r\n at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)\r\n at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)\r\n at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)"

My test works just fine . Please update to the latest build version as you are using an old one.

Update to new build worked, thank you!

Works great in v2.6. Thanks.