CODEWITHSUNDEEP
c#sql-serverentity-frameworkentity-framework-6
Franc
Franc

Reputation: 5496

EF 6 Configured for one-to-one relationship sometimes violates the constraint

I'm using Fluent API in EF 6.1.3 to define a one-to-one relationship between the entity User and the entity EcommerceCart. Everything seems to work fine 99.9% of the time, but every once in a while our exception logger tells us that while trying to access User.Cart the following exception is thrown:

A relationship multiplicity constraint violation occurred: An EntityReference can have no more than one related object, but the query returned more than one related object.

We checked the database, and it seems that Entity Framework managed to create 2 Carts for the same User, despite the one-to-one relationship of the entity types.

And what strikes me the most is that the exception never occurs when the entities are created, but when the code tries to access User.Cart, finding more than one result in the database.

Any idea of how this could happen?

PS: I'm using Lazy Loading, although I don't think this should make a difference.

These are my entities:

public class User
{
    public Guid Id { get; set; }
    public virtual EcommerceCart Cart{ get; set; }
}

public class EcommerceCart
{
    public Guid Id { get; set; }
    public virtual User User { get; set; }
}

These are my configuration files:

public class UserConfiguration : EntityTypeConfiguration<User>
{
    public UserConfiguration()
    {
        HasKey(x => x.Id);
        Property(x => x.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity).IsRequired();
        HasOptional(x => x.Cart).WithOptionalPrincipal(y => y.User).Map(x => x.MapKey("User_Id"));
    }
}

public class EcommerceCartConfiguration : EntityTypeConfiguration<EcommerceCart>
{
    public EcommerceCartConfiguration()
    {
        HasKey(a => a.Id);
        Property(a => a.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity).IsRequired();
    }
}

And this is how the EcommerceCarts table looks like:

enter image description here

Upvotes: 1

Views: 162

Answers (1)

Franc
Franc

Reputation: 5496

Following adam0101 advice, I created the following migration, which forces the foreign key User_Id to be unique:

public partial class EcommerceCart_User_Index : DbMigration
{
    public override void Up()
    {
        DropIndex("dbo.EcommerceCarts", new[] { "User_Id" });
        Sql(
            @"CREATE UNIQUE NONCLUSTERED INDEX IX_User_Id
            ON dbo.EcommerceCarts(User_Id)
            WHERE User_Id IS NOT NULL");
    }
}

Unfortunately, I couldn't find any other solution using Fluent API, because it seems like there is no way to create a unique index on Navigation properties without using SQL.

If anyone knows a better solution, please let me know.

Upvotes: 1

Related Questions