EF Code-First One-to-one relationship: Multiplicity is not valid in Role * in relationship

Question

I'm attempting to do the following:

public class class1
{
    public int Id {get;set;}
    [ForeignKey("Class2")]
    public int Class2Id {get;set;}
    public virtual Class2 Class2 {get;set;}
}

public class class2
{
    public int Id { get; set;}
    [Required]
    public virtual int Class1Id {get;set;}
    [Required]
    [ForeignKey("Class1Id")]
    public Class1 Class1 {get;set;}
}

However every time I try to migrate my database I get the following error:

Class1_Class2_Target: : Multiplicity is not valid in Role 'Class2_Class1_Target' in relationship 'Class2_Class1'. Because the Dependent Role properties are not the key properties, the upper bound of the multiplicity of the Dependent Role must be '*'.

What could be the issue here?

Solution 1

Your model is not a 1:1 association. You can still have many Class2 objects referring to the same one Class1 object. Also, your model doesn't guarantee that a Class2 referring to a Class1 is also referred back by this Class1 object — Class1 can refer to any Class2 object.

How to configure 1:1?

The common way to guarantee (sort of) a 1:1 association in SQL is to have a table for the principal entity and one for the dependent entity where the primary key in the dependent table also is a foreign key to the principal:

(Here Class1 is the principal)

Now in a relational database, this still doesn't guarantee a 1:1 association (that's why I said 'sort of'). It's a 1:0..1 association. There can be a Class1 without a Class2. The truth is, genuine 1:1 associations are impossible in SQL, because there is no language construct that inserts two rows in different tables synchronously. 1:0..1 is the closest we get.

Fluent Mapping

To model this association in EF you can use the fluent API. Here's the standard way to do it:

class Class1Map : EntityTypeConfiguration<Class1>
{
    public Class1Map()
    {
        this.HasKey(c => c.Id);
        this.Property(c => c.Id)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
        this.HasRequired(c1 => c1.Class2).WithRequiredPrincipal(c2 => c2.Class1);
    }
}

And in the context:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Configurations.Add(new Class1Map());
}

And this is left of your classes:

public class Class1
{
    public int Id {get;set;}
    public virtual Class2 Class2 {get;set;}
}

public class Class2
{
    public int Id {get;set;}
    public virtual Class1 Class1 {get;set;}
}

There is no way to configure alternate foreign key properties in the model, because the only FK involved has to be the dependent's primary key.

The strange thing about this model is that EF doesn't stop you from creating (and saving) a class1 object without a class2. I think EF should be capable of validating this requirement before saving changes, but, apparently, it doesn't. Likewise, there are ways to delete a class2 object without deleting its class1 parent. So this HasRequired - WithRequired pair is not as stringent as it looks (and should be).

Data annotations

The only way to get this right in code is by data annotations. (Of course the database model will still not be able to enforce 1:1)

public class Class1
{
    public int Id {get;set;}
    [Required]
    public virtual Class2 Class2 {get;set;}
}

public class Class2
{
    [Key, ForeignKey("Class1")]
    public int Id {get;set;}
    [Required]
    public virtual Class1 Class1 {get;set;}
}

The [Key, ForeignKey("Class1")] annotation tells EF that Class1 is the principal entity.

Data annotations play a role in many APIs, which can be a curse, because each API chooses its own subset to implement, but here it comes in handy, because now EF not only uses them to design the data model, but also to validate entities. Now if you try to save a class1 object without a class2 you'll get a validation error.

Solution 2

I had the exact same problem. What I wanted is the DB schema to have 2 tables that cross reference each other with [foreign key] --> [primary key]. Finally I found the way: Let's say we have 2 classes: Books and Authors. The Book class should have a foreign key to the author who authored it and the Author class should have a foreign key to the last book he wrote. The way to have EF understand this using code first is: (Note that this is done using a mixture of data annotations and fluent API)

public class Book {
    ...
    public Guid BookId
    ...
    public Guid AuthorId { get; set; }

    [ForeignKey("AuthorId")]
    public virtual Author author { get; set; }
}

public class Author {
    ...
    public Guid AuthorId
    ...
    public Guid? LatestBookId { get; set; }

    [ForeignKey("LatestBookId")]
    public virtual Book book { get; set; }

    public virtual ICollection<Book> books { get; set; }
}

// using fluent API
class BookConfiguration : EntityTypeConfiguration<Book> {

    public BookConfiguration() {
        this.HasRequired(b => b.author)
            .WithMany(a => a.books);
    }

}

This works and creates the exact DB schema I wanted. In SQL it would create tables and foreign keys corresponding to the following code:

CREATE TABLE [dbo].[Book](
    [BookId] [uniqueidentifier] NOT NULL,
    [AuthorId] [uniqueidentifier] NOT NULL,
    ...
 CONSTRAINT [PK_dbo.Book] PRIMARY KEY CLUSTERED 
(
    [BookId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

...

GO

ALTER TABLE [dbo].[Book] WITH CHECK ADD  CONSTRAINT [FK_dbo.Book.Author_AuthorId] FOREIGN KEY([AuthorId])
REFERENCES [dbo].[Author] ([AuthorId])
GO

...

CREATE TABLE [dbo].[Author](
    [AuthorId] [uniqueidentifier] NOT NULL,
    [LatestBookId] [uniqueidentifier] NULL,
    ...
 CONSTRAINT [PK_dbo.Author] PRIMARY KEY CLUSTERED 
(
    [AuthorId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

...

GO

ALTER TABLE [dbo].[Author]  WITH CHECK ADD  CONSTRAINT [FK_dbo.Author_dbo.Book_LatestBookId] FOREIGN KEY([LatestBookId])
REFERENCES [dbo].[Book] ([BookId])
GO

...

Comments

[JensOlsen112]

I tried adding [Required] data annotations to the foreign key in class2 but I still get the same error. (I've also updated my code to show this in my question)

[RizJa]

Ah, your foreign key values should be referencing the int value that you have in each class instead of the Class object. So instead of "Class1", the foreign key should be "Class1Id". The same goes for Class2 as well.

[JensOlsen112]

I tried that too (have updated my question code) but I still get the same error.

[JensOlsen112]

Strange thing: When I remove the Class1Id and Class2Id properties I don't get the error anymore - what gives?

[theTechRebel]

The fluent API method is amazing it makes sense of the configuration as compared to the annotations method. +1.

[Ozzy]

this is not 1:1 relationship

[Asiri Dissanayaka]

This is 1:*. Not a solution.