locked
Unique constraint in EF5 RRS feed

  • Question

  • I use VS 2012 with .NET 4.0. I created a DB first app that has a unique id to each table. I populate the class members corresponding to all the fields in the DB but when I save this to the DB, i obviously get the primary key validation exception because for multiple writes to the database the id field is 0 (not populated). I just want to write to the DB and not worry about this field as its set as auto-increment in the DB.

    Is it possible at all in EF5 to do this? If so, how?

    I know there are options around this (e.g. retrieve the last/highest id value from the DB first, then incrementing it manually and then writing it back), but surely I am not the only one to battle with this. Is there not any better way?

    Monday, October 29, 2012 1:45 PM

Answers

  • Hi HeinrichVermeulen;

    Yes as you stated the issue is corrected by modifying the schema such that it has the IDENTITY on the Primary key. This will increment the value of the primary key by 1 on each insert. Without this it will attempt to insert the new record with the same value zero.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Monday, October 29, 2012 5:09 PM

All replies

  • Please post the code you are using that has the issue.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Monday, October 29, 2012 2:10 PM
  • DB schema:

    CREATE TABLE [dbo].[Person] (
        [Id]   INT           NOT NULL,
        [Name] VARCHAR (MAX) NOT NULL,
        PRIMARY KEY CLUSTERED ([Id] ASC)
    );

    Code:

    private void Button_Click_1(object sender, RoutedEventArgs e)

        {
          using (var db = new DatabaseContext())
          {
            var person = new Person() { Name = "Bilbo" };
            db.People.Add(person);
            db.SaveChanges();
          }
        }

    Upon clicking the button the second time I get the exception as Id field is set to 0 by default and causes the exception.

    Monday, October 29, 2012 2:27 PM
  • Ok, I think I figured it out myself. I didn't set the UNIQUE constraint in the DB schema to this:

    CREATE TABLE [dbo].[Person] (
        [Id]   INT           IDENTITY (1, 1) NOT NULL,
        [Name] VARCHAR (MAX) NOT NULL,
        PRIMARY KEY CLUSTERED ([Id] ASC),
        UNIQUE NONCLUSTERED ([Id] ASC)
    );

    Also in the database model I needed to set the 'StoreGeneratedPattern' property for the Id field to 'Identity'. This seems to keep the database happy and insert the auto-incremented value for the field Id field into the DB correctly.

    Can someone please validate this?

    Monday, October 29, 2012 2:45 PM
  • Hi HeinrichVermeulen;

    Yes as you stated the issue is corrected by modifying the schema such that it has the IDENTITY on the Primary key. This will increment the value of the primary key by 1 on each insert. Without this it will attempt to insert the new record with the same value zero.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Monday, October 29, 2012 5:09 PM