Home / Community & Support / Developer Productivity Tools Forums / OpenAccess ORM > OpenAccess ORM Free Edition > PostgreSQL serial column

• Michael

  Posted on Nov 23, 2010 (permalink)

  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.

  Reply

• Damyan Bogoev Damyan Bogoev

  Posted on Nov 25, 2010 (permalink)

  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

  Accelerate your learning with industry's first Telerik OpenAccess ORM SDK. Download today.
  

  Reply

• Michael

  Posted on Nov 25, 2010 (permalink)

  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.

  Reply

• Answer Damyan Bogoev Damyan Bogoev

  Posted on Nov 25, 2010 (permalink)

  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

  Accelerate your learning with industry's first Telerik OpenAccess ORM SDK. Download today.
  

  Reply

Back to Top

Home / Community & Support / Developer Productivity Tools Forums / OpenAccess ORM > OpenAccess ORM Free Edition > PostgreSQL serial column