Limit and offset in EF Core

[dgxhubbard]

We have a postgresql statement shown below, and we use dotconnect for postgresql to process it.
We get an exception and the stack trace shown below. I have put the statement directly to PostgreSql using dbForge and it runs fine. What am I doing wrong? Note the sql statement is purely pseodo code and connection string is blank on purpose. They are just to show the ideas.

Code: Select all

            try
            {
            	var connStr = "";
            	var selectQuery = "select distinct "Buyers"."Name", "Buyers"."Description" from "MySchema"."Buyers" order by "Name"  LIMIT 50 OFFSET 0;"
            	
            	
                using ( var context = new MyContext ( connStr ) )
                {
                    using ( var conn = context.Database.GetDbConnection () )
                    {
                        conn.Open ();

                        using ( var cmd = conn.CreateCommand () )
                        {
                            cmd.CommandText = selectQuery;

                            dt = new DataTable ();
                            dt.Load ( cmd.ExecuteReader () );

                            rows = new List<DataRow> ();
                            for ( int i = 0; i < dt.Rows.Count; i++ )
                                rows.Add ( dt.Rows [ i ] );
                        }
                    }
                }

                Trace( "SQLGetItems offset: " + offset );
            }
            catch ( Exception ex )
            {
                LogProvider.Logger.LogException( "SQLGetItems failed", ex );
                throw;
            }

SQL Statement

Code: Select all

select distinct "Buyers"."Name", "Buyers"."Description" from "MySchema"."Buyers" order by "Name"  LIMIT 50 OFFSET 0;

StackTrace

Code: Select all

   at System.Data.Common.DbDataReader.GetSchemaTable()
   at System.Data.ProviderBase.SchemaMapping..ctor(DataAdapter adapter, DataSet dataset, DataTable datatable, DataReaderContainer dataReader, Boolean keyInfo, SchemaType schemaType, String sourceTableName, Boolean gettingData, DataColumn parentChapterColumn, Object parentChapterValue)
   at System.Data.Common.DataAdapter.FillMappingInternal(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 schemaCount, DataColumn parentChapterColumn, Object parentChapterValue)
   at System.Data.Common.DataAdapter.FillMapping(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 schemaCount, DataColumn parentChapterColumn, Object parentChapterValue)
   at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
   at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
   at System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler)
   at SQLPagedCollectionView.SQLGetItems(Int32 offset, Int32 pageSize)

[Shalex]

We cannot reproduce any issue with a similar SQL statement and your code at the moment.

In addition to your stack trace, also please specify the exact text of the error (Exception.Message).