none
Entity Framework Code First - Cannot insert duplicate key in object 'dbo.T_CRProviders'

    Question

  • Hello,

    I have some urgent issue which I could not find answer for across the web.

    I am using CodeFirst EF 4.3 and I am getting an error. My code is:

    Models:

    public enum CRProviderEnums
    {
        PE_Abcd = 0,
        PE_Efgh
    }
     
    [Table("T_CRProviders")]
    public class CRProvider
    {
        [Key]
        [Required]
        public int Enum { getset; }
        [Required]
        public string Name { getset; }
    }
     
    [Table("T_CRSupportedResources")]
    public class CRSupportedResource
    {
        [Key]
        public Guid SupportedResourceId { getset; }
        [Required]
        public CRProvider Provider { getset; }
    }
     
    

    DbContext:

    public class RSContext : DbContext
    {
        public DbSet<CRProviderCRProviders { getset; }
        public DbSet<CRSupportedResourceCRSupportedResources { getset; }
    }

    Table T_CRProviders looks like this: Enum (PK), Name

    Table T_CRSupportedResources looks like this: SupportedResourceId (PK), Provider_Enum (FK).

    In the database table T_CRProviders I already have a provider with the following values:

    Enum: 0 (which is PE_Abcd)

    Name: "PE_Abcd"

    Now my main() calls a method AddSupportedResource. This method adds to table T_CRSupportedResources a new CRSupportedResource which refers to provider 0 (PE_Abcd). The method looks like this:

    public void AddSupportedResource()
        {
            CRSupportedResource supportedResource = new CRSupportedResource()
            {
                SupportedResourceId = Guid.NewGuid(),
                Provider = new CRProvider()
                {
                    Enum = (int)CRProviderEnums.PE_Abcd,
                    Name = "PE_Abcd"
                }
            };
     
            using (RSContext myContext = new RSContext())
            {
                myContext.CRSupportedResources.Add(supportedResource);
     
                myContext.SaveChanges();
            }
        }

    I expect that this method will leave table T_CRProviders untouched, and add a new row to table T_CRSupportedResources which will look like this:

    SupportedResourceId: DE532083-68CF-484A-8D2B-606BC238AB61

    Provider_Enum (FK): 0 (which is PE_Abcd).

    Instead, upon SaveChanges, Entity framework also tries to add Provider to the T_CRProviders table, and since such a provider already exists it throws the following exception:

    "An error occurred while updating the entries.

    Violation of PRIMARY KEY constraint 'PK_T_CRProviders'. Cannot insert duplicate key in object 'dbo.T_CRProviders'.

    The statement has been terminated."

    My question:

    How can I instruct the EF not to update table T_CRProviders upon updating table T_CRSupportedResources?

    Btw, in the SQL Server I see that table T_CRSupportedResources has a foreign key named "FK_RW_TCRSupportedCloudResources_RW_TCRCloudProviders_Provider_Enum", and its Update Rule has the value of "No Action".

    Thanks,

    Maya.

    Monday, March 12, 2012 8:58 AM

Answers

  • Hi Mayash,

    Welcome to MSDN Forum.

    If you want to insert a new record of "T_CRSupportedResources" into the database, and give him a CRProvider which has already exist in the database, you shouldn't create a new instance of "CRProvider", the correct way is to query that record out and assign it to the new record's provider property. Below is a demo.

    using (var context = new RSContext())
                {
                    CRProvider crp = (context.CRProviders.Where(x => x.Name == "test")).First();
                    CRSupportedResource crsr = new CRSupportedResource();
                    crsr.SupportedResourceId = Guid.NewGuid();
                    crsr.Provider = crp;
                    context.CRSupportedResources.Add(crsr);
                    context.SaveChanges();
                }

    There's a record of CRProvider exists in the database, its name property is "test", I queried it out, and then, create a new record of "CRSupportedResource", assgin the exist CRProvider record to the new instance's provider property. Finally, add the new record to the context and call SaveChanges() method.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, March 13, 2012 7:33 AM
    Moderator