PrimaryKey increment on Insert?

[dclark]

Using:
Visual Studio 2008 9.0.30729.1 SP
dotConnect for PostgreSQL 4.0.22.0 (eval version)
.NET 3.5 SP1
Windows XP, SP3

Here is some representative C# Linq code that's been working to date using Microsoft SQL Linq classes. For a table named "Foos" with just two columns "Id" and "Name", where Id is the primary key:

Foo foo = Foos.FirstOrDefault(f => f.Name == name);
if (foo == null)
{
foo = new Foo() { Name = name };
Foos.InsertOnSubmit(foo);
SubmitChanges();
}

Note that this code initializes the Name column but not the Id column. MS LinqToSql classes and SQL Server would assign a new unique value for the Id. This behavior doesn't seem to be happening when using PostgreSQL and Devart's corresponding Linq classes. The first insert succeeds, providing a default value of 0 for the foo's Id, but then any subsequent inserts fail (because the code attempts to use a default Id of 0 again, apparently).

In case it's relevant, here is the corresponding SQL Table. (It was generated by a tool I used to migrate the original MS SQL database to PostgreSQL):

CREATE TABLE "Foos"
(
"Id" bigserial NOT NULL,
"Name" character(64) NOT NULL DEFAULT ''::bpchar,
CONSTRAINT "Foos_pkey" PRIMARY KEY ("Id")
)
WITH (OIDS=FALSE);

ALTER TABLE "Foos" ADD COLUMN "Id" bigint;
ALTER TABLE "Foos" ALTER COLUMN "Id" SET STORAGE PLAIN;
ALTER TABLE "Foos" ALTER COLUMN "Id" SET NOT NULL;
ALTER TABLE "Foos" ALTER COLUMN "Id" SET DEFAULT nextval('"Foos_Id_seq"'::regclass);

Do the Devart-generated classes really fail to invoke nextval on insertion? Do they provide some other primary-key/row-insert support for us programmers to use for inserting rows into the database? Do the generated classes only do the primary key assignment if the PostgreSQL primary key is set up a particular way?

Regards,
D.Clark, Software Engineer

[AndreyR]

Please check that you have set the AutoGeneratedValue property to true for the "Id" column while editing the model in Entity Developer.
Also set the AutoSync property to OnInsert for synchronizing the values in code with the values in database.

[dclark]

Thank you for the quick and helpful reply.

(Is there a reason that the Entity Designer didn't make the AutoGeneratedValue and AutoSync settings automatically for my primary keys? It's something you don't have to do with the Microsoft Linq to MSSQL tools. Perhaps a future feature?)

I used Entity Designer to make the suggested changes to the AutoGeneratedValue and AutoSync settings, and regenerated the linq classes. I manually deleted all existing rows from the database before running this newly-generated code. The highest numbered primary key of the deleted rows was 3. So, I expected that when the new code executed the InsertOnSubmit/SubmitChanges for a new Foo row (from my example code posted previously), that the newly inserted Foo row would get a primary key of 4.

However, SubmitChanges throws IndexOutOfRange exception.

This seems like it ought to be really simple, so I'm guessing that there's probably just another little setting somewhere, but I'm at a loss to find it.

Is there an easy way for us to discover what the Devart-generated sql statement that ultimately got sent to the postgres server looked like? Perhaps that would reveal a clue.

Thanks again for your attention to this issue.
--dclark

[dclark]

Well here's another piece of information: the insertion succeeds! And the newly inserted rows have properly incrementing primary key values.

Why SubmitChanges then proceeds to throw an IndexOutOfRange exception aftwards is a bit mysterious. Because of the exception, my program only gets one row insertion per execution.

I've tried recreating new very simple Foo tables from scratch, but I keep getting the same results.

Still hoping there's just another simple setting to check.
--dclark

[AndreyR]

The new version of Entity Developer will set the AutoGeneratedValue and AutoSync properties automatically.
For obtaining the log you should use the following code:

Code: Select all

StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter(sb);
dataContext.Log = sw;
...
string log = sb.ToString();

As for the exception, the problem seems to be associated with the installation.
Try to uninstall dotConnect for PostgreSQL, then manually delete Devart.Data.Linq.dll and Devart.Data.PostgreSql.Linq.dll from GAC and reinstall dotConnect.
Please let me know if the problem persist.

[dclark]

Thanks again for looking into this. Good to hear about the changes you say are coming.

As you suggested, I uninstalled, deleted all Devart dlls and metadata files, etc, and reinstalled. I also started over with the database table and Entity Developer. Using PGAdmin, I created a table and columns with these scripts:

CREATE TABLE "Foos"
(
"Id" bigserial NOT NULL,
"Name" character(32),
"Info" text,
CONSTRAINT "PK_Foo" PRIMARY KEY ("Id")
)
WITH (OIDS=FALSE);
ALTER TABLE "Foos" OWNER TO postgres;

ALTER TABLE "Foos" ADD COLUMN "Id" bigint;
ALTER TABLE "Foos" ALTER COLUMN "Id" SET STORAGE PLAIN;
ALTER TABLE "Foos" ALTER COLUMN "Id" SET NOT NULL;
ALTER TABLE "Foos" ALTER COLUMN "Id" SET DEFAULT nextval('"Foos_Id_seq"'::regclass);

ALTER TABLE "Foos" ADD COLUMN "Name" character(32);
ALTER TABLE "Foos" ALTER COLUMN "Name" SET STORAGE EXTENDED;

ALTER TABLE "Foos" ADD COLUMN "Info" text;
ALTER TABLE "Foos" ALTER COLUMN "Info" SET STORAGE EXTENDED;

Then in Devart's Entity Developer I dragged that table onto the workspace. I changed these properties of the Id column:
Auto Generated Value -> True
Auto-Sync -> OnInsert

Then I added a try/catch and incorporated your suggested logging code into this snippet of the C# that I've been using to test out the Devart-generated classes:

StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter(sb);
Log = sw;
string[] names = new string[] { "Red", "Orange" };
string[] infos = new string[] { "Foo", "Bar" };
for(int i = 0; i < names.Length; ++i)
{
try
{
Foo foo = new Foo() { Name = names, Info = infos };
Foos.InsertOnSubmit(foo);
SubmitChanges();
}
catch (Exception e)
{
Console.WriteLine(e.Message + "\n" + sb.ToString());
}
}
}

The resulting console message looks like this:

Parameter name is missing.
Parameter name: "Id"
INSERT INTO public."Foos" ("Name", "Info") VALUES (:p1, :p2) RETURNING "Id"
ParameterName = p1
DbType = AnsiStringFixedLength
Value = Red
ParameterName = p2
DbType = AnsiString
Value = Foo
ParameterName = "Id"
DbType = Int64
Value =


Shouldn't the Devart-generated code for this have produced Value = DEFAULT in this case?

--dclark

[AndreyR]

This problem is fixed. Look forward to the new build.