100 Row Limit

100 Row Limit

Postby 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
lalbin
 
Posts: 13
Joined: Thu 06 Aug 2009 23:29
Location: Seattle

Postby 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.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

More info on the 100 rows issue

Postby 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
lalbin
 
Posts: 13
Joined: Thu 06 Aug 2009 23:29
Location: Seattle

Postby 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.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to dotConnect for PostgreSQL