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

Question

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:

Even if not using EF, if the User primary key is also the primary key of the Cart (as would be the case with a one-to-one relationship), and primary keys are unique, how could your database have multiple carts for a user? Either your EF configuration is wrong, or you missed deploying a migration. Check out https://stackoverflow.com/a/18254140/64279 — adam0101, Apr 12 '18 at 14:08
If I look at my DB, the User primary key is NOT also the primary key of the Cart. The EcommerceCarts table has a User_Id foreign key. I thought this was normal? — tocqueville, Apr 12 '18 at 14:14
So I would either make the Cart.UserId a foreign key AND a primary key, or if you want to keep it as is, at least add a unique constraint on it to keep your data integrity. This is a good article about how to set it up. http://www.entityframeworktutorial.net/code-first/configure-one-to-one-relationship-in-code-first.aspx — adam0101, Apr 12 '18 at 14:17
That makes sense but I wouldn't know how to do it. The tutorial you linked explains what I already did, but no mention on how to add a unique constraint to the foreign key. I tried to add `Property(x => x.User.Id).HasColumnAnnotation(IndexAnnotation.AnnotationName, new IndexAnnotation(new IndexAttribute { IsUnique = true }));` to the EcommerceCartConfiguration file, but when I try to generate a migration I get the error "The type 'User' has already been configured as an entity type. It cannot be reconfigured as a complex type." — tocqueville, Apr 12 '18 at 14:35
Your fluent API configuration code shown above is redundant. EF can infer all that from your models alone. I would first remove all the configuration and see if that clears up the problem. Although I have an inkling, from the way you described the error, that your problem is not in the models or the fluent API configuration, but in the business logic of adding or updating `EcommerceCart` objects to your database. That error sounds like your are making a call to `.Single()` or `.SingleOrDefault()` I would throw that in a try catch to help find out where more than one cart is being added. — Adam Vincent, Apr 12 '18 at 15:39

score 1 · Answer 1 · answered Apr 12 '18 at 17:21

1

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.

answered Apr 12 '18 at 17:21

tocqueville

5,270
2
40
54

Well, inside the `Up` method, instead of `Sql` you can use `CreateIndex`. But the main point applies - it has to be done in the migration. No fluent API exists. In general, one-to-one FK relationship support is quite limited (as opposed to the "standard" shared PK model). – Ivan Stoev Apr 12 '18 at 18:20
1

@IvanStoev with `CreateIndex`, I don't seem to be able to allow NULL fields like I did in the Sql statement `WHERE User_Id IS NOT NULL` – tocqueville Apr 13 '18 at 07:24

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

These are my entities:

These are my configuration files:

And this is how the EcommerceCarts table looks like:

1 Answers1