CODEWITHSUNDEEP
c#sql.netentity-frameworkef-code-first
Tyler Pohl
Tyler Pohl

Reputation: 583

Entity Framework is incorrectly enforcing a unique constraint across multiple columns

Edit: I'm so sorry I wasted your time. I missed a line of code when debugging that was causing this problem. Someone had put a conditional that was checking if the name already existed in the database and threw this exception if it was.

I am using Entity Framework code first and I have an entity which contains an index with a unique constraint across two columns as shown in the code below. (I changed the names of properties to generic things so as to not show any of my company's code base)

[Table("TB_Table")]
public class Table : IEntity
{
    [Key]
    [Column("TBT_RID")]
    public int Id { get; set; }

    [ForeignKey("Something")]
    [Index("idxSomethingTableName", 1, IsUnique = true)]
    [Column("TBT_SOS_RID")]
    public int SomethingId { get; set; }

    [Index("idxSomethingTableName", 2, IsUnique = true)]
    [MaxLength(255)]
    [Column("TBT_Name")]
    public string Name { get; set; }

    // Navigation properties
    [JsonIgnore]
    public virtual Something Something { get; set; }

    [InverseProperty("Table")]
    [JsonIgnore]
    public virtual ICollection<AssetTag> ObjectTables { get; set; }

}

When inserting a record in SQL, the unique constraint is being enforced properly. Then, when trying to enter a record through Entity Framework, it tells me that it cannot add a record that has a different "SomethingId" value, but the same "Name" as another record.

For example, I can insert these records with SQL:

insert into TB_Table (TBT_SOS_RID, TGT_Name) values
(1, 'A'),
(1, 'B'),
(30, 'A')

But then I cannot add another (1, 'A') with SQL. Okay, great. The constraint is working correctly. Now, if I try to insert a record using entity framework with the values (30, 'B'), I should be able to do this, because the TBT_SOS_RID (SomethingId in C#) is different. Instead, I get an InvalidOperationException with the message "Invalid table, table already Exists". This happens on the DbSet.Add() method, before calling the SaveChanges() method.

Can you think of any reason why Entity Framework would think that this is a violation of the unique constraint when SQL does not?

Upvotes: 2

Views: 600

Answers (1)

Stephen P.
Stephen P.

Reputation: 2387

You decorated Name as follows:

[Index("idxSomethingTableName", 2, IsUnique = true)]
[MaxLength(255)]
[Column("TBT_Name")]
public string Name { get; set; }

As far as Entity is concerned, you have stated that Name must be unique, therefore, you cannot enter two names that are the same.

Upvotes: 0

Related Questions