Paging in SQLite EF Core

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for SQLite
Post Reply
dgxhubbard
Posts: 45
Joined: Fri 14 Aug 2015 20:58

Paging in SQLite EF Core

Post by dgxhubbard » 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:

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;
        }

dgxhubbard
Posts: 45
Joined: Fri 14 Aug 2015 20:58

Re: Paging in SQLite EF Core

Post by dgxhubbard » Wed 27 Jun 2018 21:20

If I remove the OrderBy then the statement seems to work

dgxhubbard
Posts: 45
Joined: Fri 14 Aug 2015 20:58

Re: Paging in SQLite EF Core

Post by dgxhubbard » Wed 27 Jun 2018 21:36

I downloaded the .net full framework version of dot connect to get the latest version of sqlite3.dll but that did not fix the problem. With the OrderBy the paging still fails.


Refer to:

https://www.devart.com/productfile/down ... story.html

Shalex
Site Admin
Posts: 8595
Joined: Thu 14 Aug 2008 12:44

Re: Paging in SQLite EF Core

Post by Shalex » Thu 28 Jun 2018 19:06

Thank you for your report. We have reproduced the bug with OrderBy and are working on it. We will provide the fix as soon as possible.

dgxhubbard
Posts: 45
Joined: Fri 14 Aug 2015 20:58

Re: Paging in SQLite EF Core

Post by dgxhubbard » Thu 28 Jun 2018 20:36

Thanks Shalex. Can you give me any time frame?
We are in the middle of testing now.

Shalex
Site Admin
Posts: 8595
Joined: Thu 14 Aug 2008 12:44

Re: Paging in SQLite EF Core

Post by Shalex » Mon 02 Jul 2018 07:39

An approximate timeframe is one week. We will notify you when the issue is fixed.

dgxhubbard
Posts: 45
Joined: Fri 14 Aug 2015 20:58

Re: Paging in SQLite EF Core

Post by dgxhubbard » Tue 03 Jul 2018 00:14

thanks appreciate it

Shalex
Site Admin
Posts: 8595
Joined: Thu 14 Aug 2008 12:44

Re: Paging in SQLite EF Core

Post by Shalex » Fri 13 Jul 2018 20:18

The bug with paging in EF Core 2.1 is fixed. We are working on the internal build with the fix for you.

dgxhubbard
Posts: 45
Joined: Fri 14 Aug 2015 20:58

Re: Paging in SQLite EF Core

Post by dgxhubbard » Fri 13 Jul 2018 21:01

Sounds great let me know when i can get it

Shalex
Site Admin
Posts: 8595
Joined: Thu 14 Aug 2008 12:44

Re: Paging in SQLite EF Core

Post by Shalex » Mon 16 Jul 2018 17:13

We are planning to release the new public builds of dotConnect providers in the end of this week. Is this timeframe OK for you?

dgxhubbard
Posts: 45
Joined: Fri 14 Aug 2015 20:58

Re: Paging in SQLite EF Core

Post by dgxhubbard » Mon 16 Jul 2018 19:51

Yes that is good thanks

Shalex
Site Admin
Posts: 8595
Joined: Thu 14 Aug 2008 12:44

Re: Paging in SQLite EF Core

Post by Shalex » Fri 20 Jul 2018 11:14

New build of dotConnect for SQLite 5.11.1190 is available for download: viewtopic.php?f=29&t=37472.

Post Reply