Version 4.0 (July 18th, 2013) Library: 4.0.13.725 .NET 4.5 SelfServicing SQL Server database

If I perform a fetch on a collection using paging to select something other than the first page of data, and I'm excluding some fields, then the fetch fails with an exception.

ItemCollection i = new ItemCollection();
IncludeFieldsList includes = new IncludeFieldsList(ItemFields.Amount);
i.GetMulti(ItemFields.EventId == 1, 10, null, null, null, includes, 1, 10); //works
i.GetMulti(ItemFields.EventId == 1, 10, null, null, null, includes, 2, 10); // fails

Here's the exception:

Unable to cast object of type 'System.Int32' to type 'System.String'.

  at SD.LLBLGen.Pro.ORMSupportClasses.PersistenceCore.ReadRowIntoFields(Object[] values, IEntityFieldsCore rowDestination, Dictionary`2 indicesForEnumConverts, Dictionary`2 indicesForTypeConverters, List`1 indicesForStringInterning, UniqueList`1 stringCacheForFetcher, Dictionary`2 hierarchyFieldValueArrayLengths, Dictionary`2 entityFieldStartIndexesPerEntity)
   at SD.LLBLGen.Pro.ORMSupportClasses.PersistenceCore.CreateEntityInstanceFromReaderRow(IEntityFactoryCore entityFactory, InheritanceHierarchyType typeOfHierarchy, Object[] valuesOfRow, UniqueList`1 stringCacheForFetcher, Dictionary`2 indicesForEnumConverts, Dictionary`2 indicesForTypeConverters, List`1 indicesForStringInterning, Dictionary`2 hierarchyFieldValueArrayLengths, Dictionary`2 entityFieldStartIndexesPerEntity, Boolean selfServicing, IEntityFactoryCore& entityFactoryToUse)
   at SD.LLBLGen.Pro.ORMSupportClasses.EntityMaterializerBase.HandleRowData(Object[] valuesOfRow, FetchCounters counters)
   at SD.LLBLGen.Pro.ORMSupportClasses.EntityMaterializerBase.Materialize(Func`4 valueReadErrorHandler, String& failureErrorText)
   at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.ExecuteMultiRowRetrievalQuery(IRetrievalQuery queryToExecute, ITransaction containingTransaction, IEntityCollection collectionToFill, Boolean allowDuplicates, IEntityFields fieldsUsedForQuery, IFieldPersistenceInfo[] fieldPersistenceInfos)
   at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.PerformGetMultiAction(ITransaction containingTransaction, QueryParameters parameters)
   at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.GetMulti(ITransaction containingTransaction, IEntityFactory entityFactoryToUse, QueryParameters parameters)
   at SD.LLBLGen.Pro.ORMSupportClasses.EntityCollectionBase`1.PerformGetMulti(QueryParameters parameters)
   at SD.LLBLGen.Pro.ORMSupportClasses.EntityCollectionBase`1.GetMulti(IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relations, IPrefetchPath prefetchPathToUse, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize)

Here's my guess at the cause:

When the first 10 are fetched, the generated SQL is like this:

SELECT TOP(10) [dbo].[ap_Item].[Amount],
                NULL AS [Best],
                                [dbo].[ap_Item].ItemId
FROM   [dbo].[ap_Item]
WHERE  [dbo].[ap_Item].EventId = 1

Specifically, note that the "Best" column, which I've not included in the query, is still given a column in the result set, it's just always set to NULL.

When the second 10 are fetched, the generated SQL is like this:

WITH __actualSet
     AS (SELECT *,
                ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) AS __rowcnt
         FROM   (SELECT TOP 20
                                [dbo].[ap_Item].[Amount],
                                NULL AS [Best],
                                [dbo].[ap_Item].ItemId
FROM   [dbo].[ap_Item]
WHERE  [dbo].[ap_Item].EventId = 1 ) AS _tmpSet)
SELECT
          Amount,
          ItemId
FROM   __actualSet
WHERE  [__rowcnt] > 10 
   AND [__rowcnt] <= 20
ORDER  BY [__rowcnt] ASC

Note how this second part of the second query doesn't return all the columns, only the ones that are not excluded. Then, the LLBLGen code gets out of sync because some columns are missing from the result set.

If this does turn out to be a LLBLGen issue, can you supply a fix in the Async Beta version, as that's what I'm using (although I did reproduce this issue in the regular released 4.0 version)

What is the DDL creation script for that table?

I see this behavior with all of the tables, so it's not something specific to any one table. As far as I know, you just need a few columns, at least one of which is neither a primary nor a foreign key, and you need to exclude that column (because you can't exclude primary or foreign key columns).

I'll put together an example table that shows the issue.

We fixed exactly this bug on May 23rd, in the SQL Server DQE. Can you make sure your application uses the latest SqlServer DQE?

Sql Server DQE, 2005 CompatibilityLevel, paging: When a paging query with excluded fields was issued, the paging query for page 2..n didn't fetch the excluded columns into the resultset (as NULL values) and the query processor therefore could run into a problem consuming the row values.

Also, what compatibility level do you use (if you don't switch to a different one, you're using 2005+) ? Compatibility level has influence on what paging query is used.

Frans,

Thanks for the help. You were right - I didn't have the latest SQL Server DQE. I probably got things confused when I took the latest ORMSupportClass.dll from the async beta but didn't also get the latest SqlServerDQE.dll from the regular build.

And then, my LinqPad test wasn't loading the version of the .dll that I thought it was, so even though I was pointing it to the latest version, it was loading an old version - so my test was invalid.

I had even checked the list of recent bug fixes on the website, but apparently didn't look far enough back to see the fix.

Thanks again,

Wesley