Entity Framework using Postgresql and Npgsql

Question

I am total n00b at Entity framework. Have installed Postgresql and Npgsql. Just wanted to begin with a simple test of DB, but I get problems already with the table's ID column.

I start with creating a simple table Score and its PK constraint manually in postgres (sql below). I run edmgen2.exe with parameter /retrofitmodel. In the list i check only to include the Score table and edmgen2.exe then outputs 4 files. I copy the files to VS2010 project directory and also include the edmx to the project. When I open its diagram, the ScoreID is marked with a Key symbol. I write code to insert a simple object. It works the first time executed, but next time I get:

{"ERROR: 23505: duplicate key value violates unique constraint \"Score_PK\""}

I look in the database, and the one item there has got ScoreID 0. To me it seems for some reason EF is trying to create another object with ID 0 instead of incrementing the ID value. It drives me nut since this is only a stupid simple test before I get started with the real db model.

I have tried:

Changing StoreGeneratedPattern attribute from None to Identity in diagram, and also to Computed.
Injecting those values also into the ssdl file for ScoreID attribute

I have attached some of the involved code below. Please help me out!!

TABLE CREATION:

CREATE TABLE "Score"
(
  "ScoreID" integer NOT NULL,
  "ScorePoint" integer,
  CONSTRAINT "Score_PK" PRIMARY KEY ("ScoreID" )
)
WITH (
  OIDS=FALSE
);
ALTER TABLE "Score"
  OWNER TO postgres;

SAVE METHOD:

            using (BoringDBContext dbContext = new BoringDBContext())
            {
                Score aScore = new Score();
                aScore.ScorePoint = 9;
                dbContext.AddToScore(aScore);
                dbContext.SaveChanges();
            }

SSDL FILE (removed <>):

?xml version="1.0" encoding="utf-8"?
Schema Namespace="BoringDB.store" Alias="Self" Provider="Npgsql" ProviderManifestToken="9.1.2" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2009/02/edm/ssdl">
  <EntityContainer Name="BoringDBstoreContainer">
    <EntitySet Name="Score" EntityType="BoringDB.store.Score" store:Type="Tables" Schema="public" /
  /EntityContainer>
  EntityType Name="Score"
    Key
      PropertyRef Name="ScoreID"
    /Key
    Property Name="ScoreID" Type="int4" Nullable="false" StoreGeneratedPattern="Identity" 
    Propert Name="ScorePoint" Type="int4" 
  /EntityType
/Schema

PART OF EDMX FILE (removed <>):

edmx:ConceptualModels
  Schema Namespace="BoringDB" Alias="Self" xmlns="http://schemas.microsoft.com/ado/2008/09/edm"
    EntityContainer Name="BoringDBContext"
      EntitySet Name="Score" EntityType="BoringDB.Score" /
    /EntityContainer
    EntityType Name="Score"
      Key
        PropertyRef Name="ScoreID" /
      /Key
      Property Name="ScoreID" Type="Int32" Nullable="false" a:StoreGeneratedPattern="Identity" xmlns:a="http://schemas.microsoft.com/ado/2009/02/edm/annotation" /
      Property Name="ScorePoint" Type="Int32" Nullable="true" /
    /EntityType
  /Schema
/edmx:ConceptualModels

Larsson · Accepted Answer · 2012-02-15 18:12:34Z

3

I found the short answer to my long quesion. Changing ScoreID datatype to BIGSERIAL instead of integer in the database made the auto increment work. Manually creating a sequence and setting it as Default value never did, don' know why.

answered Feb 15, 2012 at 18:12

Larsson

411 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

markmnl Over a year ago

sounds like you reset your sequence once data was already in hence the duplicate key error

VoidMain Over a year ago

No, the problem its not resetting, the problem is that the column must own the sequence for this to work with EF.

Collectives™ on Stack Overflow

Entity Framework using Postgresql and Npgsql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related