PostgreSQL serial column

[Michael]

Hi,

I port MSSQL "Music Store" database to PostgreSQL database for testing MVC2. Now tables have primary keys and is type of SERIAL. Now when I add new record, exception is raised for duplicate key value! In reality, it is added to a record earlier in AlbumId field, value of 0. Now adding a new record also trying to enter the 0! How can I get OA ORM to do a sequence of key columns?

Here is code for new album :

//
        // GET: /StoreManager/Create

        public ActionResult Create()
        {
            var viewModel = new StoreManagerViewModel
            {
                Album = new Album(),
                Genres = storeDB.Genres.OrderBy(b => b.Name).ToList(),
                Artists = storeDB.Artists.OrderBy(c => c.Name).ToList()
            };

            return View(viewModel);
        }

And here is for adding :

//
        // POST: /StoreManager/Create

        [HttpPost]
        public ActionResult Create(Album album)
        {
            if (ModelState.IsValid)
            {
                // Save Album
                storeDB.Add(album);
                storeDB.SaveChanges();

                return RedirectToAction("Index");
            }
            else
            {
                // Invalid - Redisplay with errors
                var viewModel = new StoreManagerViewModel
                {
                    Album = album,
                    Genres = storeDB.Genres.OrderBy(b => b.Name).ToList(),
                    Artists = storeDB.Artists.OrderBy(c => c.Name).ToList()
                };

                return View(viewModel);
            }
        }

Here is table definition :

CREATE TABLE "public"."Album" (
  "AlbumId" SERIAL,
  "GenreId" INTEGER NOT NULL,
  "ArtistId" INTEGER NOT NULL,
  "Title" VARCHAR(160),
  "Price" NUMERIC(10,2) NOT NULL,
  "AlbumArtUrl" VARCHAR(1024) DEFAULT '/Content/Images/placeholder.gif'::character varying,
  CONSTRAINT "PK__Album__97B4BE370AD2A005" PRIMARY KEY("AlbumId"),
  CONSTRAINT "FK_Album_Genre" FOREIGN KEY ("GenreId")
    REFERENCES "public"."Genre"("GenreId")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE,
  CONSTRAINT "FK__Album__ArtistId__276EDEB3" FOREIGN KEY ("ArtistId")
    REFERENCES "public"."Artist"("ArtistId")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE
) WITH OIDS;

CREATE INDEX "ArtistId_idx_Album" ON "public"."Album"
  USING btree ("ArtistId");

CREATE INDEX "GenreId_idx_Album" ON "public"."Album"
  USING btree ("GenreId");

Thanks, Michael.

[Damyan Bogoev]

Hello Michael,

I successfully reproduced this error on my side.
It seems that PostgreSql cannot find the correct sequence for the Album table because its name is not lowercase. You could see under the Sequences node that this sequence is named “Album_AlbumId_seq”. In order to avoid the error you should set the sequence’s name to lowercase("album_albumid_seq") and update your domain model using Update from database dialog.
I think that will help you.

Sincerely yours,
Damyan Bogoev
the Telerik team

[Michael]

Hi  Damyan,

Sequence name is automatic created by Database, if ORM can't work with mixed case I will change sequence name. Finaly i saw that exception about sequence name is not found ... But to get to that step, I had to manually in the generated model to explore and sets the IDENTITY column, because model mapping wizard is not have that made. It's a real problem. So I got the first entry in the column to 0 (zero) and when the new row is added again, exception is raised because ORM try to insert 0 (zero) again. He actualy did not understand that is sequence, than ordinary INTEGER field.

Michael.

[Damyan Bogoev]

Hello Michael,

We found the cause for this problem and have fixed it. Currently we are in a testing cycle of the service pack and I hope that we will be able to release it by the end of the week.
I am sorry for the inconvenience caused.

All the best,
Damyan Bogoev
the Telerik team