CODEWITHSUNDEEP
c#sqlentity-framework
Konrad Viltersten
Konrad Viltersten

Reputation: 39298

Can't specify identity column explicitly BUT ALSO can't leave it unspecified

I'm hitting a catch 22 here. When I create a new object like so and store it using EF, I get the error.

private void DataGrid_OnAddingNewItem(object sender, AddingNewItemEventArgs eventArgs)
{
  eventArgs.NewItem = new Customer
  {
    Id = Guid.NewGuid(),
    Discount = 0
  };
}

The only identity column in that table is LookUp and the error nags about it like so.

DEFAULT or NULL are not allowed as explicit identity values.

However (and this is the 22-catchy spot), when I make sure the value isn't a default nor null as follows, I get another problem.

private void DataGrid_OnAddingNewItem(object sender, AddingNewItemEventArgs eventArgs)
{
  eventArgs.NewItem = new Customer
  {
    Id = Guid.NewGuid(),
    LookUp = -1,
    Discount = 0
  };
}

And the problem manifests itself as follows.

Cannot insert explicit value for identity column in table 'Customers' when IDENTITY_INSERT is set to OFF.

I'm working with EF and I'm not sure how to set the identity inserting to off. In fact I'm not even sure if it's possible at all. And even if I knew how to, it still seems like hiding the problem, not resolving it.

My bet is that I need to be able to omit the specification of the LookUp field and still be able to insert the thing into the database. Not sure how, though, and I don't know how to approach it.

Upvotes: 2

Views: 706

Answers (2)

Konrad Viltersten
Konrad Viltersten

Reputation: 39298

Not a big fan of answering my own questions, especially when the answer I've found is more of a hide-the-issue not resolve-the-issue but here it goes.

  1. Remove the whole model (delete EDMX file).
  2. Save all files.
  3. Clear all files and then rebuild the solution (which will fail miserably).
  4. Close the project.
  5. Restart VS.
  6. Reload the project.
  7. Create a new model (add and configure EDMX file).
  8. Save all files.
  9. Rebuild the solution.

Poof - it works. Weird...

Upvotes: 0

Daniel Persson
Daniel Persson

Reputation: 2233

You need to tell EF that you want to generate the value yourself. You can either use data annotations thus:

using System.ComponentModel.DataAnnotations.Schema;

public class Customer
{
  [DatabaseGenerated(DatabaseGeneratedOptions.None)]
  public Guid Id { get; set; }
  ..
}

or by overriding OnModelCreating in your DbContext:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
  modelBuilder.Entity<Customer>().Property(x => x.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
  ..
}

If you prefer that the database generates the Id for you, you can exchange the DatabaseGeneratedOptions.None to DatabaseGeneratedOptions.Identity

Upvotes: 4

Related Questions