First LINQ statement in a program returns no more than 100

[migle]

Hi,

After having acquired the professional edition last month, we have been deploying it throughout our production application (we're not entirely newbies, so to say).

The very first LINQ to PostgreSQL statement in our program returns at most 100 entities.
We did a very short console program, which opens a connection to the database (PgSqlConnection), an then does the following:

Code: Select all

using (MyDataContext ctxt = new MyDataContext(conn))
{
  IQueryable entities =
     from e in ctxt.MyEntities
     orderby e.IdEntity
     select e;

  Console.WriteLine("{0} entities.", entities.Count());
  int i = 0;

  foreach (MyEntity e in entities)
  {
    Console.WriteLine("{0} - {1}", i++, e.IdEntity);
  }
}

Now, while the first WriteLine tells us that we have 114 entities, the foreach iteration only goes over the first 100.

We've tried using IEnumerable or List (using the ToList() method) and the same thing happens.
We get a 100 items list, instead of 114.

We've tried things on the PostgreSQL side (like vacuuming...), but there is no other method of querying the database that gives us this results.

We've used PgSqlMonitor and the statement sent has nothing wrong (it couldn't be simpler). The orderby clause is also not the culprit, it was added just to debug this issue.

However, this only happens to us with two of the tables we have, which currently have 106 and 114 entities respectively.

Please help!

Miguel

[migle]

I know that sounds crazy, but it's what happens. Here's more info, we have a schema more or less like this:

Code: Select all

CREATE TABLE MyEntity
(
    idEntity SERIAL PRIMARY KEY
)
WITHOUT OIDS;

CREATE TABLE MyEntityVersion
(
    idEntity INT NOT NULL
        REFERENCES MyEntity (idEntity )
	    ON DELETE CASCADE,
    nVersion INT NOT NULL DEFAULT 1,
    PRIMARY KEY (idEntity , nVersion),

    name VARCHAR(20),
    ...
)
WITHOUT OIDS;

The example above works when we do it over the MyEntity table and fails (that is, Count returns the right number, but foreach goes over only the first 100) when we use MyEntityVersion.

[AndreyR]

I have just created a sample with 110 entries and succeeded in querying using your sample code.
Could you please upgrade to the latest 4.85 Beta build?

[migle]

Hi,

Upgrading to the 4.85 beta did not help.
After a stage where we would verify this problem only on some machines, the problem remains and now is present on all machines (the relevant code did never change).

I have not been able to narrow down this problem. Of course, it does not surprise me that your isolated test with 110 entries works. I just can't narrow this down, and can't send you our entire source code and database.

The said MyEntityVersion table contains one XML and one TEXT field also.

Does the number 100 ring any bells to you?

So far, I'm working around the problem with code such as this:

Code: Select all

using (MyDataContext ctxt = new MyDataContext(conn)) 
{ 
  IQueryable entities = 
     from e in ctxt.MyEntityVersions
     orderby e.IdEntity 
     select e; 

  int count = entities.Count();

  List list = entities.ToList();

  if (list.Count < count)
  {
      list = (from e in ctxt.MyEntityVersions
               orderby e.IdEntity
               select e).ToList();
  }
}

The second query returns the right number of entities.

[AndreyR]

One more idea - please check that entity keys coincide with primary keys both in the model and in the generated code file.
Does the generated query return correct number of rows?

[migle]

They do coincide, the generated attributes look like this, in the EntityVersion table:

Code: Select all

[Column(Name = @"IdEntity", Storage = "_IdEntity", CanBeNull = false, DbType = "int4 NOT NULL", IsPrimaryKey = true)]
public int IdEntity
...

[Column(Name = @"nVersion", Storage = "_NVersion", CanBeNull = false, DbType = "int4 NOT NULL", IsPrimaryKey = true)]
        public int NVersion
...

The association mapping is also there, all with default attributes.



And in the Entity table:

Code: Select all

[Column(Name = @"IdEntity", Storage = "_IdEntity", AutoSync = AutoSync.OnInsert, CanBeNull = false, DbType = "serial NOT NULL AUTO_INCREMENT", IsDbGenerated = true, IsPrimaryKey = "true")]
        public int IdEntity

[AndreyR]

Have you tried the query generated by the context?
Does it return the correct number of rows executed in pgAdmin or using PgSqlCommand?

[migle]

Oh, yes, definitely, I tried the query outside LINQ to SQL. That was where I started suspecting LINQ to SQL.
The funny thing is that when I use the Count member of the IQueryable interface, the correct number of entities is returned, and if I use foreach, even if it is right after, it goes over only the first 100. That's when I had to suspect of LINQ to SQL.

Look, right now we're in a bit of a rush, and are working around this with a solution analogous to the one posted above, it's not convenient for me to spend more time on this right now.

Mind you, this problem is easily reproduced by us with a console program which has only a few lines similar to the ones reproduced above. Which makes me think that the relevant elements for reproducing this problem must be the generated mapping and the database. I can't just give you a dump of our database and the full mapping right now, so, as soon as I can, I'll try to isolate only the relevant classes.


By the way, the database server is pretty loaded, some of the tables in our application have hundreds of millions of entities or more (although not the tables involved in this problem). Can timeouts have anything to do with this?

[AndreyR]

Unfortunately, we were not able to reproduce the problem.
Hope you will succeed in creating a sample.

[migle]

I have upgraded our projects to the new 4.90.124 version. I'm very happy with it, most of the bugs went away and it doesn't try to prepare statements for each execution.

However, this problem persists. It does even depend on some strange configuration of a server, I just installed a server here at home, and it also happens, and now it appears to happen more often and on tables with simpler relationships.

I have a table with a primary key comprised of three columns, like this:

Code: Select all

CREATE TABLE abc (
ida INT NOT NULL REFERENCES A (ida) ON DELETE CASCADE,
idb INT NOT NULL REFERENCES B (ida) ON DELETE CASCADE,
seq INT NOT NULL,
PRIMARY KEY (ida, idb, seq),
x REAL ARRAY,
n INT)
WITHOUT OIDS;

This table has relationships to A and B and B also has a relationship to A. Like this:

Code: Select all

CREATE TABLE A (ida SERIAL PRIMARY KEY, ...) WITHOUT OIDS;
CREATE TABLE B (
ida INT NOT NULL PRIMARY KEY REFERENCES A (ida) ON DELETE CASCADE,
...)
WITHOUT OIDS;

All of these relationships are mapped in LINQ to SQL; the ON DELETE is also in the mapping. Table abc quickly grows to 100000+ records.

Right now, the problem happens when I do something like:

Code: Select all

public class Key {
  public int IdA { get; set; }
  public int IdB { get; set; }
  public int Seq { get; set; }
}
...
using (AbcDataContext ctxt = new AbcDataContext())
{
  IEnumerable q =
    from a in ctxt.abcs
    where IdA = X && IdB == Y
    orderby Seq
    select new Key { IdA = a.IdA, IdB = a.IdB, IdC = a.IdC };

  foreach (Key k in q)
  {
     // This foreach goes over only 100 entities, even though the table
     // has 81715; in this case, if I remove the usage of the class Key,
     // and do select a (the Abc type), then the loop goes over the
     // correct set of entities.
  }
}

Now, this is not an isolated sample that I have tried and confirmed the bug. But the structure of these tables is quite analogous to the ones that are (now) exposing this behaviour.

I can't control and haven't got a clue as what are the factores that are correlated with this problem. Perhaps the only correlation is that in all cases the primary key of the table is comprised of several columns and I am querying it using less columns.

Server issues are unlikely, I have several versions of PostgreSQL 8.4 running several different OSes and 32/64 bit architectures.

The workaround I applied previously in other cases is impracticle for this table. In those other cases, I would use an IQueryable, Count the number of entities and then do a ToList(), if the length of the list was less than the previous Count, I would repeat the query.

I don't even want a fix, because I wish I wouldn't have to upgrade again. If only I knew how to avoid this or workaround it...

Thanks,

Miguel

[AndreyR]

Unfortunately, I'm unable to reproduce the problem using the script and sample code you have posted.
Could you please send us (suport * devart * com, subject "LINQ: 100 records limit") a test project reproducing the problem?