Paging in SQLite EF Core
Posted: Wed 27 Jun 2018 19:32
We just upgraded to latest dot connect for SQLite with ef core 2.1.1 support. We are use paging as in the example code below.
This code runs on sql server and sqlite. We had no problems running on sql server. When running on sqlite when the select executes we get an IndexOutOfRangeException shown below. The code errors with an offset of 0 and a pageSize of 65.
The Gages table has 65 items in it. What is causing this to fail?
Exception Message:
Index was outside the bounds of the array.
Exception StackTrace:
at Devart.Data.SQLite.SQLiteDataReader.(Int32 )
at Devart.Data.SQLite.SQLiteDataReader.IsDBNull(Int32 i)
at lambda_method(Closure , DbDataReader )
at Microsoft.EntityFrameworkCore.Storage.Internal.TypedRelationalValueBufferFactory.Create(DbDataReader dataReader)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.BufferlessMoveNext(DbContext _, Boolean buffer)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.<_TrackEntities>d__17`2.MoveNext()
at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at Gt.Gateway.GageGateway.GetPaged(Int32 offset, Int32 pageSize, TargetDatabase target, SecurityToken securityToken)
Code:
This code runs on sql server and sqlite. We had no problems running on sql server. When running on sqlite when the select executes we get an IndexOutOfRangeException shown below. The code errors with an offset of 0 and a pageSize of 65.
The Gages table has 65 items in it. What is causing this to fail?
Exception Message:
Index was outside the bounds of the array.
Exception StackTrace:
at Devart.Data.SQLite.SQLiteDataReader.(Int32 )
at Devart.Data.SQLite.SQLiteDataReader.IsDBNull(Int32 i)
at lambda_method(Closure , DbDataReader )
at Microsoft.EntityFrameworkCore.Storage.Internal.TypedRelationalValueBufferFactory.Create(DbDataReader dataReader)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.BufferlessMoveNext(DbContext _, Boolean buffer)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.<_TrackEntities>d__17`2.MoveNext()
at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at Gt.Gateway.GageGateway.GetPaged(Int32 offset, Int32 pageSize, TargetDatabase target, SecurityToken securityToken)
Code:
Code: Select all
public new List<Gage> GetPaged ( int offset, int pageSize, TargetDatabase target, SecurityToken securityToken )
{
List<Gage> res = null;
LogProvider.Logger.LogInfo ( "GageGateway.GetPaged enter" );
try
{
using ( var context = Gt.Model.GtContextFactory.Create ( target ) )
{
if ( pageSize <= 0 )
pageSize = SqlConstants.DefaultPageSize;
res =
( from c in
context.Gages.
Include ( "Status" ).
Include ( "Supplier" ).
Include ( "Template" )
select c ).OrderBy ( g => g.Gage_ID ).Skip ( offset ).Take ( pageSize ).ToList ();
}
res.ForEach ( g => g.IsMinimum = true );
res.ForEach ( g => Clean ( g ) );
}
catch ( Exception ex )
{
LogProvider.Logger.LogException ( "GageGateway.GetPaged exception", ex );
throw ex;
}
LogProvider.Logger.LogInfo ( "GageGateway.GetPaged exit" );
return res;
}