Hi,

            var customers = (from c in metaData.Customer
                             join o in metaData.Order on c.CustomerId equals o.CustomerId
                             select new { CustomerId = c.CustomerId, CompanyName = c.CompanyName, OrderId = o.OrderId, OrderDate = o.OrderDate });

            dGridTest.DataSource = customers.OrderByDescending(c2 => c2.OrderDate).ThenByDescending(c2 => c2.CompanyName).TakePage(2, 5);
            dGridTest.DataBind()

Generates:

[SqlException (0x80131904): The text, ntext, and image data types cannot be used in an ORDER BY clause.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +925466
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800118
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1932
   System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +31
   System.Data.SqlClient.SqlDataReader.get_MetaData() +62
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1005
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +122
   System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12
   System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +7
   SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) +75

[ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: The text, ntext, and image data types cannot be used in an ORDER BY clause.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.]
   SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) +224
   SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchDataReader(IRetrievalQuery queryToExecute, CommandBehavior readerBehavior) +24
   Northwind.DatabaseSpecific.DataAccessAdapter.FetchDataReader(IRetrievalQuery queryToExecute, CommandBehavior readerBehavior) in C:\Testing\VStudio2008\Projects\LLBLGenTesting\NorthwindLinqTesting\ClassLibs\DatabaseSpecific\DataAccessAdapter.cs:292
   SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List`1 valueProjectors, IGeneralDataProjector projector, IRetrievalQuery queryToExecute) +41
   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) +83
   SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteValueListProjection(QueryExpression toExecute) +290
   SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression) +172
   SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression) +20
   SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression) +4
   SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.IEnumerable.GetEnumerator() +13
   System.Web.UI.WebControls.PagedDataSource.GetEnumerator() +171
   System.Web.UI.WebControls.DataGrid.CreateAutoGeneratedColumns(PagedDataSource dataSource) +254
   System.Web.UI.WebControls.DataGrid.CreateColumnSet(PagedDataSource dataSource, Boolean useDataSource) +1629786
   System.Web.UI.WebControls.DataGrid.CreateControlHierarchy(Boolean useDataSource) +295
   System.Web.UI.WebControls.BaseDataList.OnDataBinding(EventArgs e) +56
   System.Web.UI.WebControls.BaseDataList.DataBind() +52
   _Default.Page_Load(Object sender, EventArgs e) +1390
   System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +15
   System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +33
   System.Web.UI.Control.OnLoad(EventArgs e) +99
   System.Web.UI.Control.LoadRecursive() +47
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1436

It's related to how #TempTable is created for .TakePage()

    Query: CREATE TABLE #TempTable ([__rowcnt][int] IDENTITY (1,1) NOT NULL,[CustomerId][NText] NULL,[CompanyName][NText] NULL,[OrderId][Int] NULL,[OrderDate][DateTime] NULL);INSERT INTO #TempTable ([CustomerId],[CompanyName],[OrderId],[OrderDate]) SELECT TOP 11 [LPA_L1].[CustomerId], [LPA_L1].[CompanyName], [LPA_L1].[OrderId], [LPA_L1].[OrderDate] FROM (SELECT DISTINCT [LPA_L2].[CustomerID] AS [CustomerId], [LPA_L2].[CompanyName], [LPA_L3].[OrderID] AS [OrderId], [LPA_L3].[OrderDate] FROM ( [Northwind].[dbo].[Customers] [LPA_L2]  INNER JOIN [Northwind].[dbo].[Orders] [LPA_L3]  ON  [LPA_L2].[CustomerID] = [LPA_L3].[CustomerID])) LPA_L1 ORDER BY [LPA_L1].[OrderDate] DESC,[LPA_L1].[CompanyName] DESC;SELECT [CustomerId],[CompanyName],[OrderId],[OrderDate] FROM #TempTable WHERE [__rowcnt] > @__rownoStart AND [__rowcnt] <= @__rownoEnd ORDER BY [__rowcnt] ASC;DROP TABLE #TempTable
    Parameter: @__rownoStart : Int32. Length: 4. Precision: 10. Scale: 0. Direction: Input. Value: 5.
    Parameter: @__rownoEnd : Int32. Length: 4. Precision: 10. Scale: 0. Direction: Input. Value: 10.

Any chance the TempTable create logic can create appropriate SQL nvarchar(nn) types for strings vs arbitrarily creating nText fields for strings?

Thanks,

Shawn

It can only do that if it knows the compatibility mode is set to sqlserver 2005. Otherwise it will think it's sqlserver 2000 and for long string fields (length > 8K) it has to use ntext/text, as varchar(max) isn't a type on sqlserver 2000.

Do you use sqlserver 2005?

Reproduced for sqlserver (also present in sybase ase). The thing is that the resultset is build with fields which target a derived table. This means that there's no persistence info present. So it has to assume the type of the field. For strings it assumes ntext. This appears to be insufficient, and it needs length info as well. This is available when the field is created in the query, but it's not stored in the derived table targeting field.

Will fix this.

Otis wrote:

It can only do that if it knows the compatibility mode is set to sqlserver 2005. Otherwise it will think it's sqlserver 2000 and for long string fields (length > 8K) it has to use ntext/text, as varchar(max) isn't a type on sqlserver 2000.

Do you use sqlserver 2005?

Thanks, I'm using SQL 2000 and it supports varchar and nvarchar data types Glad to see a fix is in the works. Currently, it kills data grid types of functionality which provide users with ordering capabilities.

Shawn

Fixed in next build. I fixed it in the ormsupportclasses lib, which simply fixes up fields targeting derived tables.

There are some more issues lined up to get examined, so expect a new build tomorrow.