V. 4.0 Final, 15th May. Selfservicing, SQL Server 2008 R2 SD.LLBLGen.Pro.ORMSupportClasses.PersistenceCore.ReadRowIntoFields line 723 // System.InvalidCastException: Unable to cast 'System.Int32' to type 'System.String'. I tried to reproduce it in Nortwind, but no luck. It is weird, I know. Sorry. Any help?

// Code inside SD.LLBLGen.Pro.ORMSupportClasses.PersistenceCore.ReadRowIntoFields
// where the Exception happens:
// v is an integer value (1 or 14...) of the record
// and (string)v gives error
foreach(var index in indicesForStringInterning)
{
    var v = valuesToSet[index];
    valuesToSet[index] = v == null ? v : stringCacheForFetcher.AddOrGet((string)v);
}

Use context: LlblgenDataSource select method on +1million records table Error conditions: Read first record on a new page && e.PageNumber >1 && excludedFields.Count > 0 && sorter.Count > 0 && not in a unique indexed field

protected override void GridDataSource_Select(object sender, PerformSelectEventArgs e)
{
    ISortExpression sorter = e.Sorter;
    if (sorter.Count == 0)
    { // Default sorter
        sorter = new SortExpression(MutualistaFields.FechaEstado | SortOperator.Descending);
    }
    ExcludeFieldsList excludedFields = new ExcludeFieldsList
        {
            MutualistaFields.Municipio,
        };

    e.ContainedCollection.GetMulti(e.Filter, e.MaxNumberOfItemsToReturn, sorter, e.Relations, null, excludedFields, e.PageNumber, e.PageSize);
}

[InvalidCastException: No se puede convertir un objeto de tipo 'System.Int32' al tipo 'System.String'.]
   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) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Persistence\PersistenceCore.cs:723
   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) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Persistence\PersistenceCore.cs:2535
   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, IEntityFactoryCore& entityFactoryToUse) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Persistence\PersistenceCore.cs:666
   SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.ExecuteMultiRowRetrievalQuery(IRetrievalQuery queryToExecute, ITransaction containingTransaction, IEntityCollection collectionToFill, Boolean allowDuplicates, IEntityFields fieldsUsedForQuery, IFieldPersistenceInfo[] fieldPersistenceInfos) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\SelfServicingSpecific\DaoBase.cs:1720
   SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.PerformGetMultiAction(ITransaction containingTransaction, QueryParameters parameters) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\SelfServicingSpecific\DaoBase.cs:1149
   SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.GetMulti(ITransaction containingTransaction, IEntityFactory entityFactoryToUse, QueryParameters parameters) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\SelfServicingSpecific\DaoBase.cs:928
   SD.LLBLGen.Pro.ORMSupportClasses.EntityCollectionBase`1.PerformGetMulti(QueryParameters parameters) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\SelfServicingSpecific\EntityCollectionBase.cs:798
   SD.LLBLGen.Pro.ORMSupportClasses.EntityCollectionBase`1.GetMulti(IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relations, IPrefetchPath prefetchPathToUse, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\SelfServicingSpecific\EntityCollectionBase.cs:767
   Controls_ListMutualistaInstances.GridDataSource_Select(Object sender, PerformSelectEventArgs e) in C:\Users\jlsanmartin.DGTESORO\Documents\OldXP_PC\LLBLGen Pro Projects\_Tesoro\Mapfre\GUI\Controls\ListMutualistaInstances.ascx.cs:177
   SD.LLBLGen.Pro.ORMSupportClasses.LLBLGenProDataSource.OnPerformSelect(PerformSelectEventArgs eventArgs) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses.Web\LLBLGenProDataSource.cs:125
   SD.LLBLGen.Pro.ORMSupportClasses.LLBLGenProDataSourceView.ExecuteSelectEntityCollection(Int32 pageSize, Int32 pageNumber, DataSourceSelectArguments arguments) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses.Web\LLBLGenProDataSourceView.cs:628
   SD.LLBLGen.Pro.ORMSupportClasses.LLBLGenProDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses.Web\LLBLGenProDataSourceView.cs:529
   System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +21
   System.Web.UI.WebControls.DataBoundControl.PerformSelect() +143
   System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +74
   System.Web.UI.WebControls.GridView.DataBind() +4
   System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +66
   System.Web.UI.WebControls.GridView.OnPreRender(EventArgs e) +26

Hi Jose,

• What is the relation between your first code snippet and the second one?
• Is there any generated SQL?
• Can you isolate the error? (i.e. not in a web application with LLBLGenProDataSource)

Same error. Confirmed that same code against v.3.5 works fine.

What is the relation between your first code snippet and the second one?

It is SD.LLBLGen.Pro.ORMSupportClasses.PersistenceCore.ReadRowIntoFields, where the exception is raised.

DBTime: 2.6ms (indexed on FechaEstado, non clustered) Notice in the SQL that IdTipoMutualista and IdProvincia are requested (even when in excludedFields). A clue?

ISortExpression sorter = new SortExpression(MutualistaFields.FechaEstado | SortOperator.Descending);

// Excluded fields of Mutualista in the list
ExcludeFieldsList excludedFields = new ExcludeFieldsList
{
    MutualistaFields.IdTipoMutualista,
    MutualistaFields.NumTransferencia,
    MutualistaFields.Cp,
    MutualistaFields.IdProvincia,
    MutualistaFields.Domicilio,
    MutualistaFields.Municipio,
    MutualistaFields.Telefono
};

MutualistaCollection mutualistas = new MutualistaCollection();
mutualistas.GetMulti(null, 0, sorter, null, null, excludedFields, 2, 20);
int c = mutualistas.Count;

... and the generated SQL

WITH __actualSet
     AS (SELECT *,
                ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) AS __rowcnt
         FROM   (SELECT TOP 40 [Aplicacion].[Mutualista].[IdMutualista],
                               [Aplicacion].[Mutualista].[Nif],
                               [Aplicacion].[Mutualista].[FechaPresentacion],
                               [Aplicacion].[Mutualista].[IdEstado],
                               [Aplicacion].[Mutualista].[FechaEstado],
                               [Aplicacion].[Mutualista].[IdPago],
                               NULL                                        AS [NumTransferencia],
                               NULL                                        AS [Telefono],
                               [Aplicacion].[Mutualista].[Nombre],
                               [Aplicacion].[Mutualista].[Apellido1],
                               [Aplicacion].[Mutualista].[Apellido2],
                               [Aplicacion].[Mutualista].[IdTipoMutualista],
                               NULL                                        AS [Cp],
                               NULL                                        AS [Domicilio],
                               NULL                                        AS [Municipio],
                               [Aplicacion].[Mutualista].[Provincia] AS [IdProvincia]
                 FROM   [Aplicacion].[Mutualista]
                 ORDER  BY [Aplicacion].[Mutualista].[FechaEstado] DESC) AS _tmpSet)
SELECT [IdMutualista],
       [Nif],
       [FechaPresentacion],
       [IdEstado],
       [FechaEstado],
       [IdPago],
       [Nombre],
       [Apellido1],
       [Apellido2],
       [IdTipoMutualista],
       [IdProvincia]
FROM   __actualSet
WHERE  [__rowcnt] > 20 /* @p1 */
   AND [__rowcnt] <= 40 /* @p2 */
ORDER  BY [__rowcnt] ASC

Changing parameters sorter & excludedFields. Same error (but excluded fields are OK in SQL)

ISortExpression sorter = new SortExpression(MutualistaFields.FechaEstado | SortOperator.Descending)
                         & (MutualistaFields.IdPago | SortOperator.Descending)
                         & (MutualistaFields.NumTransferencia | SortOperator.Ascending);

// Excluded fields of Mutualista in the list
ExcludeFieldsList excludedFields = new ExcludeFieldsList
{
    MutualistaFields.Telefono
};

MutualistaCollection mutualistas = new MutualistaCollection();
mutualistas.GetMulti(null, 0, sorter, null, null, excludedFields, 2, 20);
int c = mutualistas.Count;

... and SQL generated

WITH __actualSet
     AS (SELECT *,
                ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) AS __rowcnt
         FROM   (SELECT TOP 40 [Aplicacion].[Mutualista].[IdMutualista],
                               [Aplicacion].[Mutualista].[Nif],
                               [Aplicacion].[Mutualista].[FechaPresentacion],
                               [Aplicacion].[Mutualista].[IdEstado],
                               [Aplicacion].[Mutualista].[FechaEstado],
                               [Aplicacion].[Mutualista].[IdPago],
                               [Aplicacion].[Mutualista].[NumTransferencia],
                               NULL                                        AS [Telefono],
                               [Aplicacion].[Mutualista].[Nombre],
                               [Aplicacion].[Mutualista].[Apellido1],
                               [Aplicacion].[Mutualista].[Apellido2],
                               [Aplicacion].[Mutualista].[IdTipoMutualista],
                               [Aplicacion].[Mutualista].[Cp],
                               [Aplicacion].[Mutualista].[Domicilio],
                               [Aplicacion].[Mutualista].[Municipio],
                               [Aplicacion].[Mutualista].[Provincia] AS [IdProvincia]
                 FROM   [Aplicacion].[Mutualista]
                 ORDER  BY [Aplicacion].[Mutualista].[FechaEstado] DESC,
                           [Aplicacion].[Mutualista].[IdPago] DESC,
                           [Aplicacion].[Mutualista].[NumTransferencia] ASC) AS _tmpSet)
SELECT [IdMutualista],
       [Nif],
       [FechaPresentacion],
       [IdEstado],
       [FechaEstado],
       [IdPago],
       [NumTransferencia],
       [Nombre],
       [Apellido1],
       [Apellido2],
       [IdTipoMutualista],
       [Cp],
       [Domicilio],
       [Municipio],
       [IdProvincia]
FROM   __actualSet
WHERE  [__rowcnt] > 20 /* @p1 */
   AND [__rowcnt] <= 40 /* @p2 */
ORDER  BY [__rowcnt] ASC

Ok, to get a good reprocase here:

it occurs when: - you fetch the page after the first one - you have excluded fields specified - you have specified a sorter - compatibility is set to 2005+

?

The exception occurs on a line which assumes the value fetched is a string. In v4, excluded fields are specified as 'NULL as <alias>' so they're always present in the projection.

what I think (but aren't sure of) happens is that the query for page 2...n has the fields re-ordered / differently than the query for the first page.

Could you compare the queries for the first page and the second page? I know the query for the first page doesn't have the WITH ... wrapper, but it's about the SELECT .... projection inside it. It likely contains fields in a different order, (namely the excluded fields). Am I correct? I'll see if I can repro it here too

The row reader uses a set of indices which point to the string fields in the resultset. It reads these values from each row and tries to get the unique string instance from a string cache. It apparently reads an int value where it expects a string value.

Same code as in the first example (but pageNumber = 1) => no error.

ISortExpression sorter = new SortExpression(MutualistaFields.FechaEstado | SortOperator.Descending);

// Excluded fields of Mutualista in the list
ExcludeFieldsList excludedFields = new ExcludeFieldsList
{
    MutualistaFields.IdTipoMutualista,
    MutualistaFields.NumTransferencia,
    MutualistaFields.Cp,
    MutualistaFields.IdProvincia,
    MutualistaFields.Domicilio,
    MutualistaFields.Municipio,
    MutualistaFields.Telefono
};

MutualistaCollection mutualistas = new MutualistaCollection();
mutualistas.GetMulti(null, 0, sorter, null, null, excludedFields, 1, 20);
int c = mutualistas.Count;

SELECT TOP(20 /* @p2 */) [Aplicacion].[Mutualista].[IdMutualista],
                [Aplicacion].[Mutualista].[Nif],
                [Aplicacion].[Mutualista].[FechaPresentacion],
                [Aplicacion].[Mutualista].[IdEstado],
                [Aplicacion].[Mutualista].[FechaEstado],
                [Aplicacion].[Mutualista].[IdPago],
                NULL                                           AS [NumTransferencia],
                NULL                                           AS [Telefono],
                [Aplicacion].[Mutualista].[Nombre],
                [Aplicacion].[Mutualista].[Apellido1],
                [Aplicacion].[Mutualista].[Apellido2],
                [Aplicacion].[Mutualista].[IdTipoMutualista],
                NULL                                           AS [Cp],
                NULL                                           AS [Domicilio],
                NULL                                           AS [Municipio],
                [Aplicacion].[Mutualista].[Provincia] AS [IdProvincia]
FROM   [Aplicacion].[Mutualista]
ORDER  BY [Aplicacion].[Mutualista].[FechaEstado] DESC

Thanks. Queries look the same, so it's something else. We'll look into it!

When you fetch the page after the first one

Not exactly: when pageNumber > 1 (the direct collection.GetMulti of the testing example is in the default page, nothing happens before). Note: the SQL gives no error when run directly in the SQL Manager.

TIA

raist wrote:

When you fetch the page after the first one

Not exactly: when pageNumber > 1 (the direct collection.GetMulti of the testing example is in the default page, nothing happens before). Note: the SQL gives no error when run directly in the SQL Manager.

TIA

yes, the pagenumber of the page after the first is 2 so we were talking about the same thing

The sql is indeed the same, the problem is with the interpreting of the values of each row. We'll see if we can reproduce it and what's the cause of the problem (which is somewhere in the runtime)

Reproduced. Interestingly, if I set compatibility to 2000 (so temptables for paging) it works OK. Odd!

The queries are not the same: the one giving the problem has no excluded fields in the SELECT, but the first page SELECT has them set to null.

I think there is the problem, as the valuesToSet variable has no the same content in the same indexes. I enclose a picture with both contents of valuesToSet and its relation

(edit) heh posted at the same time.

Found it. Check the SELECT query below the WITH... wrapper:

SELECT [IdMutualista],
     [Nif],
     [FechaPresentacion],
     [IdEstado],
     [FechaEstado],
     [IdPago],
     [Nombre],
     [Apellido1],
     [Apellido2],
     [IdTipoMutualista],
     [IdProvincia]
FROM __actualSet
WHERE [__rowcnt] > 20 /* @p1 */
AND [__rowcnt] <= 40 /* @p2 */
ORDER BY [__rowcnt] ASC

It doesn't include the excluded fields, so the resultset is wrong, it has not enough columns. Fixing it.

Workaround for now: set compatibility mode to 2000, in case you DONT use NEWSEQUENTIAL_ID or other 2005+ features.

Fixed. See attached DQE. New build released later today.

Frans, honestly. Bugfixing in the last two years has come from hours, to minutes, now to thought speed. Impressive

Thanks

Confirmed: fixed. Thanks again

heh Glad it's solved!