100 Row Limit

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
lalbin
Posts: 13
Joined: Thu 06 Aug 2009 23:29
Location: Seattle

100 Row Limit

Post by lalbin » Fri 07 Aug 2009 00:08

I have found by doing various queries, that the results are limited to 100 rows. With PgSqlCommand, I have found that I can get around the 100 rows limit by using the FetchAll but with LINQ and Entity I have not found any way to handle this. Can you tell me how to get past the 100 row limit with both LINQ and Entity? I have read someplace that you specify the rows return via the form object, but I am not using form objects, I am writing excel fields with results.

PgSqlCommand, This works for more then 100 Rows.

Code: Select all

Dim conn As New PgSqlConnection()
Dim results As PgSqlCommand
results = conn.CreateCommand()
results.CommandText = SQL
results.FetchAll = True
pgReader = results.ExecuteReader()
For i = 1 To results.GetRecordCount()
     pgReader.Read()
Next i
LINQ - Limit 100 Rows

Code: Select all

Dim dbLINQ As New LoadRoleFTELINQContext.LoadRoleFTELINQContext(db.conn)
dbLINQ.Connection.Open()
Dim query = From it In dbLINQ.mpmybt_current_datas _
     Order By it.employee_id, it.sup_id _
     Select it
Dim comp As LoadRoleFTELINQ.mpmybt_current_data
MsgBox(query.Count()) ' Shows more than 100 rows.
For Each comp In query
     MsgBox (comp.employee_id) ' Fails after the first 100 rows
Next comp
Entity - Limit 100 Rows

Code: Select all

Dim dbEF As New LoadRoleFTEEntity2.LoadRoleFTEEntityEntities
dbEF.Connection.ConnectionString = "metadata=res://*/LoadROleFTEEntity.csdl|res://*/LoadROleFTEEntity.ssdl|res://*/LoadROleFTEEntity.msl;provider=Devart.Data.PostgreSql;provider connection string="" & db.conn.ConnectionString & """
dbEF.Connection.Open()
Dim query = From it In dbEF.mpmybt_current_datas _
     Order By it.employee_id, it.sup_id _
     Select it
Dim comp As LoadRoleFTEEntity2.mpmybt_current_data
MsgBox(query.Count()) ' Shows more than 100 rows.
For Each comp In query 
     MsgBox (comp.employee_id) ' Fails after the first 100 rows
Next comp
Lloyd

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Mon 10 Aug 2009 11:29

I have just tried both your sample code pieces using dotConnect for PostgreSQL 4.55.39 and everything succeeded.
The table I was testing on has 113K+ rows and I have iterated through all of them.
Please post the exception message and call stack.

lalbin
Posts: 13
Joined: Thu 06 Aug 2009 23:29
Location: Seattle

More info on the 100 rows issue

Post by lalbin » Mon 10 Aug 2009 19:24

Here is the example using LINQ

This exception happens on the "Next comp" line after having processed the 100th row and going to the 101st row. I did the test twice to show that the error is slightly different everything it happens.

Code: Select all

Devart.Data.PostgreSql.PgSqlException was unhandled by user code
  InnerException: portal "PORTAL1888755163293180109" does not exist

Code: Select all

Devart.Data.PostgreSql.PgSqlException was unhandled by user code
  InnerException: portal "PORTAL1873457628294686687" does not exist
Please Note:
Devart Data Version: 5.0.2.0
dotConnect for PostgreSQL Version: 4.55.37.0

Ok, I have now updated and re-ran the test and got the following error:

Code: Select all

Devart.Data.PostgreSql.PgSqlException was unhandled by user code
  InnerException: portal "PORTAL1888755163298500890" does not exist
Now:
Devart Data Version: 5.0.4.0
dotConnect for PostgreSQL Version: 4.55.39.0

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Tue 11 Aug 2009 08:27

Thank you for the detailed description.
We have fixed this error in LINQ to PostgreSQL, the fix will be included in the nearest build.
As for Entity Framework, the investigation is still in progress. I will let you know about the results.

Post Reply