One to zero-or-one relationship: Cannot insert explicit value for identity column in table when IDENTITY_INSERT is OFF

user_4520453 0
Viewed 467

I have two entities:

public class Subscription
{
    public int SubscriptionId { get; set; }

    public virtual ICollection<SubscriptionError> SubscriptionErrors { get; set; }
}

public class SubscriptionError
{
    public int SubscriptionErrorId { get; set; }
    public int SubscriptionId { get; set; }

    public virtual Subscription Subscription { get; set; }
}

Originally, I defined the relationship between them as one-to-many in the SubscriptionErrorMap as follows:

this.HasRequired(t => t.Subscription)
            .WithMany(t => t.SubscriptionErrors)
            .HasForeignKey(d => d.SubscriptionId)
            .WillCascadeOnDelete(false);

I am using the following code for saving SubscriptionError:

context.SubscriptionErrors.Add(subscriptionError);

where subscriptionError is the entity and I am not explicitly setting the primary key field.

This used to work fine. But, when I changed this relationship to one to zero-or-one, it started to throw the following exception on saving:

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

The new mapping is:

this.HasRequired(t => t.Subscription)
            .WithOptional(t => t.SubscriptionError)
            .WillCascadeOnDelete(false);

Is there something wrong with the mapping?

entity-framework asp.net c#
2 Answers

This is what worked for me:

Remove the PK field SubscriptionErrorId and set SubscriptionId as both PK and FK for SubscriptionError.

Entities would now look like the following:

public class Subscription
{
     public int SubscriptionId { get; set; }

     public virtual SubscriptionError SubscriptionError { get; set; }
}

public class SubscriptionError
{
    [ForeignKey("Subscription")]
    public int SubscriptionId { get; set; }

    [Required]
    public virtual Subscription Subscription { get; set; }
}
user_45204530

From what I have seeing the DB's model script is something like below 

CREATE TABLE Subscription(
    [SubscriptionId] [int] IDENTITY(1,1) NOT NULL,
    --[FieldName1] [nchar](100) NULL,
    --[FieldNameN] [nchar](100) NULL,
 CONSTRAINT [PK_Subscription] PRIMARY KEY CLUSTERED 
(
    [SubscriptionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE SubscriptionError(
    [SubscriptionErrorId] [int] NOT NULL,
    [SubscriptionId] [int] NULL,
    --[FieldName1] [nchar](100) NULL,
    --[FieldNameN] [nchar](100) NULL,
 CONSTRAINT [PK_SubscriptionError] PRIMARY KEY CLUSTERED 
(
    [SubscriptionErrorId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE SubscriptionError  WITH CHECK ADD  CONSTRAINT [FK_SubscriptionError_Subscription] FOREIGN KEY([SubscriptionId])
REFERENCES Subscription ([SubscriptionId]) 
ALTER TABLE [dbo].[SubscriptionError] CHECK CONSTRAINT [FK_SubscriptionError_Subscription]

Which [SubscriptionErrorId] is key but it is not auto increment field so for one-to-many relation model should be like below :

public partial class Subscription
{ 
    public Subscription()
    {
        SubscriptionErrors = new HashSet<SubscriptionError>();
    } 
    public int SubscriptionId { get; set; } 
    public virtual ICollection<SubscriptionError> SubscriptionErrors { get; set; }
}

and for SubscriptionError:

public partial class SubscriptionError
{
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int SubscriptionErrorId { get; set; } 
    public int? SubscriptionId { get; set; } 
    public virtual Subscription Subscription { get; set; }
}

That works like a charm but for one-to-one or one-to-zero relation that SubscriptionError for Subscription is not collection and the relation should be defined by fluent api like below:

modelBuilder.Entity<Subscription>()
    .HasOptional(s => s.SubscriptionError)
    .WithRequired(ad => ad.Subscription);

and the models:

public partial class Subscription
{
    public Subscription()
    {
    }
    public int SubscriptionId { get; set; }
    public virtual SubscriptionError SubscriptionError { get; set; }
}
public partial class SubscriptionError
{
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int SubscriptionErrorId { get; set; }
    public int? SubscriptionId { get; set; }
    public virtual Subscription Subscription { get; set; }
}

so please take a look at your DB's SubscriptionError and check out what properties set for SubscriptionError anyway in EF's conventions when an Id pk (SubscriptionErrorId ends with Id and EF interpret it as primary key) int property be found, it will configure that column as identity which mean database generate value for the key property, for example the auto SubscriptionErrorId will be mapped to identity key automatically and there is no need to decorate it by DatabaseGeneratedOption explicitly but DatabaseGeneratedOption.None is used here because that key should be generated by code pragmatically.

about your problem when we defined one-to-one relation this means that the child does not mean if it's parent does not exist for this reason the foreign key is primary key in this situation !!! for yours model for SubscriptionError mode the SubscriptionId prop is also key because of one to one or zero relation, if you look at the sample in here there is no PK in StudentAddress anyway in you situation that you have SubscriptionError with auto generate SubscriptionErrorId if you don't define the PK you will got

SubscriptionError: EntityType: EntitySet 'SubscriptionError' is based on type 'SubscriptionError' that has no keys defined.

if you define that column both as key with order like :

[Key, Column("SubscriptionErrorId", Order = 1)]
public int SubscriptionErrorId { get; set; } 
[Key, Column("SubscriptionId", Order = 2)]
public int SubscriptionId { get; set; }

you will also get

Entities in 'Model.SubscriptionError' participate in the 'Subscription_SubscriptionError' relationship. 0 related 'Subscription_SubscriptionError_Source' were found. 1 'Subscription_SubscriptionError_Source' is expected.'

but if your model be something like below, defined SubscriptionErrorId as Identity and define SubscriptionId as key which SubscriptionErrorId is auto increment:

public partial class SubscriptionError
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int SubscriptionErrorId { get; set; } 
    [Key]
    public int SubscriptionId { get; set; } 
    public virtual Subscription Subscription { get; set; }
}

then your code will work like a charm:

using (var context = new DbContext())
{
    var se = new SubscriptionError()
    {
        FieldName1 = "Value",
        SubscriptionId = 1,//Parent key
        //SubscriptionErrorId is auto 
    };
    context.SubscriptionError1.Add(se);
    context.SaveChanges();
}
user_18626580
Related