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

Question

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

I am using CodeFirst EF 4.3.1 and I am getting an error: Violation of PRIMARY KEY constraint 'PK_T_CRProviders'. Cannot insert duplicate key in object 'dbo.T_CRProviders'.

My code is:

Models:

public enum CRProviderEnums
{
    PE_Abcd = 0,
    PE_Efgh
}

[Table("T_CRProviders")]
public class CRProvider
{
    [Key]
    [Required]
    public int Enum { get; set; }
    [Required]
    public string Name { get; set; }
}

[Table("T_CRSupportedResources")]
public class CRSupportedResource
{
    [Key]
    public Guid SupportedResourceId { get; set; }
    [Required]
    public CRProvider Provider { get; set; }
}

DbContext:

public class RSContext : DbContext
{
    public DbSet<CRProvider> CRProviders { get; set; }
    public DbSet<CRSupportedResource> CRSupportedResources { get; set; }
}

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.

Answer 1

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

No it will not happen. You are creating detached entity graph consisting of existing entity a and new entity. EF doesn't know about the existence of your entity until you inform it about it - there are no DB queries validating existence performed by EF on behind.

If you call Add method all entities in your entity graph are added as new. If you don't want to insert all of them you can start with using Attach and manually change state for new ones. For example like:

myContext.CRSupportedResources.Attach(supportedResource);
myContext.Entry(supportedResource).State = EntityState.Added;

Answer 2

Actually, there is a way to do this.

See the answer to my question in the following link:

http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/62f3e5bc-c972-4622-b830-e7d7fe710101