Paging in PostgreSQL EF Core

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

Paging in PostgreSQL EF Core

Post by dgxhubbard » Tue 03 Jul 2018 00:21

We are running latest dot connect for Postgresql with ef core 2.1.1 support. We are use paging as in the example code below. This code runs on sql server, sqlite and postgres. We had no problems running on sql server. When running on postgres 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?

Stack Trace:

at Devart.Data.PostgreSql.PgSqlDataReader.(Int32 , String )
at Devart.Data.PostgreSql.PgSqlDataReader.GetValue(Int32 i)
at Devart.Common.DbDataReaderBase.IsDBNull(Int32 ordinal)
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) in C:\Repository\GtNext\Source\Base\Next\GtGateway\Repos\GageGateway.cs:line 723

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

            }
            catch ( Exception ex )
            {
                LogProvider.Logger.LogException ( "GageGateway.GetPaged exception", ex );
                throw ex;
            }

            LogProvider.Logger.LogInfo ( "GageGateway.GetPaged exit" );

            return res;
        }


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

Re: Paging in PostgreSQL EF Core

Post by Shalex » Tue 03 Jul 2018 08:10

Thank you for your report. We have reproduced the bug with OrderBy and are working on it. An approximate timeframe is one week. We will notify you when the issue is fixed.

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

Re: Paging in PostgreSQL EF Core

Post by dgxhubbard » Tue 03 Jul 2018 15:29

Thanks Shalex

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

Re: Paging in PostgreSQL EF Core

Post by Shalex » Fri 13 Jul 2018 20:19

The bug with paging in EF Core 2.1 is fixed. The internal build with the fix: https://www.devart.com/pub/nuget_postgr ... 1_1186.zip.

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

Re: Paging in PostgreSQL EF Core

Post by dgxhubbard » Fri 13 Jul 2018 22:06

Hi Shalex
Not sure how to manually install I looked around and did this:
1) Unzipped to a folder
2) Renamed ".nupkg" to ".zip"
3) Added folder "5.0.1978" to .nuget\packages\devart.data.
4) Added folder "7.11.1186" to .nuget\packages\devart.data.postgresql and
.nuget\packages\devart.data.postgresql.efcore.
5) Open Devart.Data.5.0.1978.zip and copy to version directory in devart.data
6) Open Devart.Data.PostgreSql.7.11.1186.zip and copy all to version directory in devart.data.postgresql
7) Open Devart.Data.PostgreSql.EFCore.7.11.1186.zip and copy all to version directory in
devart.data.postgresql.efcore
8) Manually search and replace to get version to 7.11.1186
9) Rebuild and get multiple errors:
Unable to find package Devart.Data.PostgreSql with version (>= 7.11.1186)

How do I install this manually?

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

Re: Paging in PostgreSQL EF Core

Post by Shalex » Mon 16 Jul 2018 10:33

Please try this walkthrough:

1. Download https://www.devart.com/pub/nuget_postgr ... 1_1186.zip and unzip it to some folder on your file system.

2. Navigate to Visual Studio > Tools > NuGet Package Manager > NuGet Manager Settings > Package Sources and add the new package source which references to the folder with the Devart.* packages.

3. Open your Visual Studio project, go to Visual Studio > Tools > NuGet Package Manager > Package Manager Console, set Package source to the one just created and execute:
PM> install-package Devart.Data.PostgreSql.EFCore

Does this help?

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

Re: Paging in PostgreSQL EF Core

Post by dgxhubbard » Mon 16 Jul 2018 17:06

Thanks Shalex.

I have a new error. Before we get a page of data we setup our page collection and the total item count.
Our API has GetPaged methods with offset and page size. We calculate the the number of items using statements as shown below. These statements have an exception "function COUNT() does not exist". The sql statement (psuedo) being executed is shown below. I believe the error comes from keyword count being enclosed in double quotes. Is there a way to get this resolved and coordinate with EF Core dotConnect for SQLite because the same paging error exists there.

Refer to:

viewtopic.php?f=3&t=37432

Sql Statement

Code: Select all

SELECT "COUNT"(*)
FROM (
    SELECT DISTINCT g."Manufacturer"
    FROM "MYSCHEMA"."MyItems" g
    WHERE g."Manufacturer" IS NOT NULL AND (Trim(g."Manufacturer") <> '')
) AS t
StackTrace

Code: Select all

   at   .    (   )
   at   .()
   at Devart.Data.PostgreSql.PgSqlCommand.InternalPrepare(Boolean implicitPrepare, Int32 startRecord, Int32 maxRecords)
   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
   at   .     ​  ​     (CommandBehavior )
   at  .ExecuteDbDataReader(CommandBehavior )
   at   .ExecuteDbDataReader(CommandBehavior )
   at  .Execute(IRelationalConnection , DbCommandMethod , IReadOnlyDictionary`2 )
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.BufferlessMoveNext(DbContext _, Boolean buffer)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.QueryMethodProvider.GetResult[TResult](IEnumerable`1 valueBuffers, Boolean throwOnNullResult)
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ResultEnumerable`1.GetEnumerator()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
   at System.Linq.Enumerable.First[TSource](IEnumerable`1 source)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass15_1`1.<CompileQueryCore>b__0(QueryContext qc)


Code

Code: Select all


            try
            {
                using ( var context = new MyContext ( connStr ) )
                {
                    count = context.MyItems.Where ( g => g.Manufacturer != null && g.Manufacturer.Trim () != String.Empty ).Select ( g => g.Manufacturer ).Distinct ().Count ();
                }

            }
            catch ( Exception ex )
            {
                throw ex;
            }

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

Re: Paging in PostgreSQL EF Core

Post by Shalex » Tue 17 Jul 2018 09:44

Thank you for your report. We have reproduced the "function COUNT() does not exist" issue and are investigating it.

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

Re: Paging in PostgreSQL EF Core

Post by Shalex » Fri 20 Jul 2018 08:15

The bug with using the aggregate functions in EF Core 2.1 is fixed: viewtopic.php?f=3&t=37473.

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

Re: Paging in PostgreSQL EF Core

Post by dgxhubbard » Fri 20 Jul 2018 21:25

Great. I will download it

Post Reply