100 Row Limit

[lalbin]

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]

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]

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]

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.