This (northwind in LINQPad, adapter)

(from p in Product
select new {p}).OrderBy(x=>x.p.ProductName)

produces this

SELECT [LPA_L1].[CategoryId],
  [LPA_L1].[Discontinued],
  [LPA_L1].[ProductId],
  [LPA_L1].[ProductName],
  [LPA_L1].[QuantityPerUnit],
  [LPA_L1].[ReorderLevel],
  [LPA_L1].[SupplierId],
  [LPA_L1].[UnitPrice],
  [LPA_L1].[UnitsInStock],
  [LPA_L1].[UnitsOnOrder]
FROM
 (SELECT [LPLA_1].[CategoryID] AS [CategoryId],
  [LPLA_1].[Discontinued],
  [LPLA_1].[ProductID] AS [ProductId],
  [LPLA_1].[ProductName],
  [LPLA_1].[QuantityPerUnit],
  [LPLA_1].[ReorderLevel],
  [LPLA_1].[SupplierID] AS [SupplierId],
  [LPLA_1].[UnitPrice],
  [LPLA_1].[UnitsInStock],
  [LPLA_1].[UnitsOnOrder]
FROM
 [dbo].[Products]  [LPLA_1]  ) [LPA_L1] ORDER BY [LPA_L1].[ProductName] ASC

which runs fine but then you get

InvalidCastException
Unable to cast object of type 'System.Int16' to type 'Northwind.DAL.EntityClasses.ProductEntity'.

 at lambda_method(Closure , Object[] , Int32[] )
   at SD.LLBLGen.Pro.LinqSupportClasses.DataProjectorToObjectList`1.AddRowToResults(IList projectors, Object[] rawProjectionResult)
   at SD.LLBLGen.Pro.ORMSupportClasses.ProjectionUtils.FetchProjectionFromReader(List`1 valueProjectors, IGeneralDataProjector projector, IDataReader datasource, Int32 maxNumberOfItemsToReturn, Int32 pageNumber, Int32 pageSize, Boolean clientSideLimitation, Boolean clientSideDistinctFiltering, Boolean clientSidePaging, UniqueList`1 stringCache, Dictionary`2 typeConvertersToRun)
   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.System.Linq.IQueryProvider.Execute(Expression expression)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.IEnumerable.GetEnumerator()

SD.LLBLGen.Pro.LinqSupportClasses.NET35.dll version 3.5.12.0807 SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll version 3.5.12.0824

Similar question was answered here: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=17364 Please post here if the issue remains unsolved for you.

Walaa wrote:

Similar question was answered here: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=17364 Please post here if the issue remains unsolved for you.

While the stack trace in that post was similar that was for version 2.6 and that was fixed by moving to v3.5. My problem is caused by the order by and still exists in v3.5.

The problem comes from the fact that the entity-in-projection is also the end projection. We use some tricks to detect an entity-in-projection and use a specific piece of code to make that work (it didn't work before as you might remember). I think, because the order by is placed after the select, the detection of the entity-in-projection fails and it falls back to what it always did: an object projection into an anonymous type, with the same error as before.

The .OrderBy is in that location because it's appended dynamically?

Otis wrote:

The .OrderBy is in that location because it's appended dynamically?

Yes, maybe by a user clicking on grid column for example. I'm experimenting with using Entity Instance to populate a View DTO vs projecting on to properties vs projecting properties via a constructor.

Is there any change that you don't use anonymous type? Something like:

(from p in Product
select).OrderBy(x=>x.ProductName)

daelmo wrote:

Is there any change that you don't use anonymous type? Something like:

(from p in Product
select).OrderBy(x=>x.ProductName)

Huh?

Not sure what you are asking but it makes no difference whether the type being projected onto is anonymous or not, same results with ProductViewDto here: http://rapiddevbookcode.codeplex.com/SourceControl/changeset/view/100147#2021089

I suspect the method which checks whether the special code path for fetching the entities is activated doesn't succeed when it runs into a query where the projection isn't the final query, as it's wrapped.

value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[ NW26.Adapter.EntityClasses.CustomerEntity]).Select(c => new <>f__AnonymousType6c`1(c = c)).OrderBy(x => x.c.Country)

gives:

Executed Sql Query: 
    Query: SELECT [LPA_L1].[Address], [LPA_L1].[City], [LPA_L1].[CompanyName], [LPA_L1].[ContactName], [LPA_L1].[ContactTitle], [LPA_L1].[Country], [LPA_L1].[CustomerId], [LPA_L1].[Fax], [LPA_L1].[Phone], [LPA_L1].[PostalCode], [LPA_L1].[Region] FROM (SELECT [LPLA_1].[Address], [LPLA_1].[City], [LPLA_1].[CompanyName], [LPLA_1].[ContactName], [LPLA_1].[ContactTitle], [LPLA_1].[Country], [LPLA_1].[CustomerID] AS [CustomerId], [LPLA_1].[Fax], [LPLA_1].[Phone], [LPLA_1].[PostalCode], [LPLA_1].[Region] FROM [Northwind].[dbo].[Customers]  [LPLA_1]  ) [LPA_L1] ORDER BY [LPA_L1].[Country] ASC

which shows there's a wrapped inner query to append the order by on, instead of having the order by being part of the main query, as the projection of the query ordered is marking it as a full query, so outer operators have to be appended to that query instead of being merged with it. (so the order by isn't merged with the query but appended to it).

As the projection of the query is actually the one of the inner query, I'll check whether the code which should use the special code path is doing its job or that it misses something or that it simply can't do what's expected from it with queries like this. If that last situation is the case, we can't fix it.

(edit) yep, the method to detect this special case doesn't create the proper data to make it happen. What it should do is create pre projection buckets which make sure the data coming in to project into an object is first projected to the right object (in this case an entity) before it's handed to the actual projection to the anonymous type (or dto or whatever). Looking into why it fails

Fixed! 1 line of code was all it took. The clone made from the projection didn't copy the pre projection buckets. All clear now. Hopefully it fixes your problems

Otis wrote:

Fixed! 1 line of code was all it took. The clone made from the projection didn't copy the pre projection buckets. All clear now. Hopefully it fixes your problems

Yep can now order and page after the projection , so great now have another option for projecting onto a view DTO.