I am attempting to order the result of a Linq query by a projected, non-entity field. The projected field in question originates from a related entity. I am posting to find out if this is a supported operation. The documentation seemed to indicate that sorting can be carried out on any field in the projection, though I am unsure whether this only applies to fields on the entity itself.

I am running what I believe is the latest version of LLBLGen application (7/22?) and grabbed updated runtime libraries from the forums as follows:

Linq Support: http://www.llblgen.com/tinyforum/GotoMessage.aspx?MessageID=78990&ThreadID=14189

ORM Support:

http://llblgen.com/TinyForum/Messages.aspx?ThreadID=14152

The version on the DLL files is listed as 2.6.8.828.

So given the following code from a method in my repository:

return from p in metaData.Product
                   select new Product
                              {
                                  Id = p.Id,
                                  UrlId = p.UrlId,
                                  Name = p.Name,
                                  Description = p.Description,
                                  Type = new Type
                                             {
                                                  Id = p.Type.Id,
                                                  Name = p.Type.Name
                                             }
                              };

This returns an IQueryable of my domain objects. In a unit test, I am attempting to sort products by the Name property of the Type property and loop through the results , as follows:

var q = from p in _repository.GetProducts()
                    orderby p.Type.Name
                    select p;

foreach (var prod in q)
            {
                Console.WriteLine(prod.Id);
            }

From which I receive the following debug output and exception:

: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[ProjectionTest.DAL.EntityClasses.ProductEntity]).Select(p => new Product() {Id = p.Id, UrlId = p.UrlId, Name = p.Name, Description = p.Description, Type = new Type() {Id = p.Type.Id, Name = p.Type.Name}}).OrderBy(p => p.Type.Name)
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSubQuery
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [LPA_L3].[Id], [LPA_L3].[UrlId], [LPA_L3].[Name], [LPA_L3].[Description], [LPA_L2].[Id] AS [Id0], [LPA_L2].[Name] AS [Name1] FROM ( [Products_Testing].[dbo].[Type] [LPA_L2]  RIGHT JOIN [Products_Testing].[dbo].[Product] [LPA_L3]  ON  [LPA_L2].[Id]=[LPA_L3].[TypeFK])

Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Generated Sql query: 
    Query: SELECT [LPA_L1].[Id], [LPA_L1].[UrlId], [LPA_L1].[Name], [LPA_L1].[Description], [LPA_L1].[Id0], [LPA_L1].[Name1] FROM (SELECT [LPA_L3].[Id], [LPA_L3].[UrlId], [LPA_L3].[Name], [LPA_L3].[Description], [LPA_L2].[Id] AS [Id0], [LPA_L2].[Name] AS [Name1] FROM ( [Products_Testing].[dbo].[Type] [LPA_L2]  RIGHT JOIN [Products_Testing].[dbo].[Product] [LPA_L3]  ON  [LPA_L2].[Id]=[LPA_L3].[TypeFK])) [LPA_L1] ORDER BY [].[Name] ASC

Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
Method Enter: DataAccessAdapterBase.OpenConnection
: Connection physically opened.
Method Exit: DataAccessAdapterBase.OpenConnection
Method Enter: DataAccessAdapterBase.CloseConnection
Method Exit: DataAccessAdapterBase.CloseConnection
TestCase 'ProjectionTest.UnitTests.ProductTestFixture.TestTypeSort'
failed: SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException : An exception was caught during the execution of a retrieval query: An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name. . Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.
  ----> System.Data.SqlClient.SqlException : An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name. 
    at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)
    at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchDataReader(IRetrievalQuery queryToExecute, CommandBehavior readerBehavior)
    c:\projects\tinker\projectiontest\projectiontest.dal\databasespecific\dataaccessadapter.cs(292,0): at ProjectionTest.DAL.DatabaseSpecific.DataAccessAdapter.FetchDataReader(IRetrievalQuery queryToExecute, CommandBehavior readerBehavior)
    at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List`1 valueProjectors, IGeneralDataProjector projector, IRetrievalQuery queryToExecute, Dictionary`2 typeConvertersToRun)
    at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List`1 valueProjectors, IGeneralDataProjector projector, IEntityFields2 fields, IRelationPredicateBucket filter, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IGroupByCollection groupByClause, Boolean allowDuplicates, Int32 pageNumber, Int32 pageSize)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteValueListProjection(QueryExpression toExecute)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.Execute()
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
    C:\projects\Tinker\ProjectionTest\ProjectionTest.UnitTests\ProductTestFixture.cs(60,0): at ProjectionTest.UnitTests.ProductTestFixture.TestTypeSort()
    --SqlException
    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
    at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
    at System.Data.SqlClient.SqlDataReader.get_MetaData()
    at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
    at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
    at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
    at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
    at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
    at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)

Please advise if this is something that simply is not supported.

Thanks again.

I think this was fixed in an internal build a couple of days ago (we haven't released that one yet, as some core issue is being solved by a rewrite of some internal code), though I'll check it out with the latest build.

We expect to release an updated build tomorrow (wednesday)

(edit) I've attached a tempbuild to this thread (last post) http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=14181

Tomorrow (it's getting a bit late here) we'll setup a repro case for you and test it more in depth. Please let us know if this tempbuild already fixes your situation (as it looks familar to a situation we had earlier this week)

I've rerun the test using the suggested temp build and it looks like I am still getting the same exception:

: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[ProjectionTest.DAL.EntityClasses.ProductEntity]).Select(p => new Product() {Id = p.Id, UrlId = p.UrlId, Name = p.Name, Description = p.Description, Type = new Type() {Id = p.Type.Id, Name = p.Type.Name}}).OrderBy(p => p.Type.Name)
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSubQuery
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [LPA_L3].[Id], [LPA_L3].[UrlId], [LPA_L3].[Name], [LPA_L3].[Description], [LPA_L2].[Id] AS [Id0], [LPA_L2].[Name] AS [Name1] FROM ( [Products_Testing].[dbo].[Type] [LPA_L2]  RIGHT JOIN [Products_Testing].[dbo].[Product] [LPA_L3]  ON  [LPA_L2].[Id]=[LPA_L3].[TypeFK])

Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Generated Sql query: 
    Query: SELECT [LPA_L1].[Id], [LPA_L1].[UrlId], [LPA_L1].[Name], [LPA_L1].[Description], [LPA_L1].[Id0], [LPA_L1].[Name1] FROM (SELECT [LPA_L3].[Id], [LPA_L3].[UrlId], [LPA_L3].[Name], [LPA_L3].[Description], [LPA_L2].[Id] AS [Id0], [LPA_L2].[Name] AS [Name1] FROM ( [Products_Testing].[dbo].[Type] [LPA_L2]  RIGHT JOIN [Products_Testing].[dbo].[Product] [LPA_L3]  ON  [LPA_L2].[Id]=[LPA_L3].[TypeFK])) [LPA_L1] ORDER BY [].[Name] ASC

Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
Method Enter: DataAccessAdapterBase.OpenConnection
: Connection physically opened.
Method Exit: DataAccessAdapterBase.OpenConnection
Method Enter: DataAccessAdapterBase.CloseConnection
Method Exit: DataAccessAdapterBase.CloseConnection
TestCase 'ProjectionTest.UnitTests.ProductTestFixture.TestTypeSort'
failed: SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException : An exception was caught during the execution of a retrieval query: An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name. . Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.
  ----> System.Data.SqlClient.SqlException : An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name. 
    at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)
    at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchDataReader(IRetrievalQuery queryToExecute, CommandBehavior readerBehavior)
    c:\projects\tinker\projectiontest\projectiontest.dal\databasespecific\dataaccessadapter.cs(292,0): at ProjectionTest.DAL.DatabaseSpecific.DataAccessAdapter.FetchDataReader(IRetrievalQuery queryToExecute, CommandBehavior readerBehavior)
    at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List`1 valueProjectors, IGeneralDataProjector projector, IRetrievalQuery queryToExecute, Dictionary`2 typeConvertersToRun)
    at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List`1 valueProjectors, IGeneralDataProjector projector, IEntityFields2 fields, IRelationPredicateBucket filter, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IGroupByCollection groupByClause, Boolean allowDuplicates, Int32 pageNumber, Int32 pageSize)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteValueListProjection(QueryExpression toExecute)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
    C:\projects\Tinker\ProjectionTest\ProjectionTest.UnitTests\ProductTestFixture.cs(60,0): at ProjectionTest.UnitTests.ProductTestFixture.TestTypeSort()
    --SqlException
    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
    at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
    at System.Data.SqlClient.SqlDataReader.get_MetaData()
    at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
    at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
    at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
    at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
    at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
    at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)

Let me know if I can provide any further information.

thanks for testing. We'll try to repro it first thing in the morning (wednesday) so it can be included in tomorrows build release. I think it's the relation hop in the lambda in the order by (p.Type.Name) which causes this (the hop over Type, which results in a relation to be added to the full query).

In the other issue you posted today, could you also re-run that one with the tempbuild? Thanks.

Tricky situation, I've fixed it as it couldn't find an alias. I'm not sure if it will be correct in ALL situations, though those situations should be very rare.

I've tested this again with most recent release build and it's working just fine. Thanks for your help!

Edit:

Upon closer inspection it appears that, though an exception is not being thrown, the sort is not being carried out on the correct field. Can you confirm based on the query below?:

Below is the query from the debug output:

SELECT [LPA_L1].[Id], [LPA_L1].[UrlId], [LPA_L1].[Name], [LPA_L1].[Description], [LPA_L1].[Id0], [LPA_L1].[Name1] FROM (SELECT [LPA_L3].[Id], [LPA_L3].[UrlId], [LPA_L3].[Name], [LPA_L3].[Description], [LPA_L2].[Id] AS [Id0], [LPA_L2].[Name] AS [Name1] FROM ( [Products_Testing].[dbo].[Type] [LPA_L2]  RIGHT JOIN [Products_Testing].[dbo].[Product] [LPA_L3]  ON  [LPA_L2].[Id]=[LPA_L3].[TypeFK])) [LPA_L1] ORDER BY [LPA_L1].[Name] ASC

It looks to me like it is sorting on the name column on the product table.