EF Code First One-to-One AND One-to-Many mapping

Question

I have the following model and I am trying to construct a one-to-one and also one-to-many relationships from the same parent and child entities. The one-to-many relationship works with my current mappings but I am struggling with adding the new one-to-one relationship (for CoverPicture property). Here are the relevant model and EF mapping codes:

Category.cs:

public int Id { get; set; }
public string Name { get; set; }
public Guid? CoverPictureId { get; set; }
public virtual Picture CoverPicture { get; set; }
public virtual ICollection<Picture> Pictures { get; set; }

Picture.cs:

public Guid Id { get; set; }
public string FileName { get; set; }
public int CategoryId { get; set; }
public virtual Category Category { get; set; }

Relevant Category EntityTypeConfiguration<Category> mappings (incorrect):

this.HasOptional(t => t.CoverPicture)
    .WithRequired()
    .Map(m => m.MapKey("CoverPictureId"))
    .WillCascadeOnDelete(false);

Relevant Picture EntityTypeConfiguration<Picture> mappings (correct):

this.HasRequired(t => t.Category)
    .WithMany(t => t.Pictures)
    .HasForeignKey(k => k.CategoryId);

When I try to add a migration for the new CoverPicture property, EF tries to add a CoverPictureId column to the Category table (which is what I want) but also CoverPictureId to the Picture table (which is not what I want; Picture already has a key defined and mapped).

Here is the Up() migration code scaffolded by EF:

AddColumn("dbo.Category", "CoverPictureId", c => c.Guid());
AddColumn("dbo.Picture", "CoverPictureId", c => c.Int());
CreateIndex("dbo.Picture", "CoverPictureId");
AddForeignKey("dbo.Picture", "CoverPictureId", "dbo.Category", "Id");

What am I doing wrong?

Answer 1

I'm not sure what version of EF you are using, but newer versions won't allow you to do the type of mapping you're trying to do, you'll receive the following error:

The navigation property 'Pictures' declared on type 'YourProject.Category' has been configured with conflicting multiplicities.

So why is that? Let's look at your mappings, and what you're telling Entity Framework in plain English:

this.HasRequired(t => t.Category)  //A Picture must have 1 Category
.WithMany(t => t.Pictures)         //A Category can have many Pictures


this.HasOptional(t => t.CoverPicture) //A Category may or may not have 1 Picture
.WithRequired()                       //A Picture must have 1 Category

Compare that with octavioccl's answer:

this.HasOptional(p => p.CoverPicture) //A Category may or may not have 1 Picture 
.WithMany()                           //A Picture can have many Categories

The change from WithRequired to WithMany is swapping where the Foreign Key is being placed. Now you have the mapping that you're looking for... kind of:

CreateTable(
    "dbo.Categories",
    c => new
        {
            Id = c.Int(nullable: false, identity: true),
            Name = c.String(),
            CoverPicture_Id = c.Guid(),
        })
    .PrimaryKey(t => t.Id)
    .ForeignKey("dbo.Pictures", t => t.CoverPicture_Id)
    .Index(t => t.CoverPicture_Id);

CreateTable(
    "dbo.Pictures",
    c => new
        {
            Id = c.Guid(nullable: false),
            FileName = c.String(),
            Category_Id = c.Int(nullable: false),
        })
    .PrimaryKey(t => t.Id)
    .ForeignKey("dbo.Categories", t => t.Category_Id, cascadeDelete: true)
    .Index(t => t.Category_Id);

But let's stop and think about that for a second. Not only have you basically defined 1 to many relationships in both directions (not to be confused with many to many) but you've also broken the integrity of your model. Now you can assign a Picture as a Category's CoverPicture even if that Picture doesn't belong to that Category. That's not what you want, and eventually it's going to cause you a headache. Instead of explicitly defining a CoverPicture property on Category, how about this?

public class Category
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Picture> Pictures { get; set; }

    public SetCoverPicture(Picture picture)
    {
        if(!Pictures.Contains(picture))
        {
            throw new ArgumentException("Picture is not in this Category");
        }
        var currentCoverPicture = Pictures.FirstOrDefault(p=p.IsCoverPicture == true);
        if(currentCoverPictur e!= null)
        {
            currentCoverPicture.IsCoverPicture = false;
        }
        picture.IsCoverPicture = true;
    }

}

public class Picture
{
    public Guid Id { get; set; }
    public string FileName { get; set; }
    public int Category_Id { get; set; }
    public virtual Category Category { get; set; }
    public bool IsCoverPicture { get; protected internal set; }
}

This enforces your invariants (business rules) that state that...

1. a CoverPicture for a Category must belong to that Category (enforced by the database) and
2. there can be only 1 CoverPicture for a Category (enforced in code)

You could do something similar with the code provided by octavioccl, but the code provided here results in a cleaner and more understandable physical data model.

Answer 2

To resolve your issue try to map that relationship as I show below:

this.HasOptional(p => p.CoverPicture)
            .WithMany().HasForeignKey(p=>p.CoverPictureId)
            .WillCascadeOnDelete(false);

In one-to-one relation one end must be principal and second end must be dependent. When you are configuring this kind of relationship, Entity Framework requires that the primary key of the dependent also be the foreign key. With your configuration, the principal is Category and the dependend is Picture. That's way FK CoverPictureId is int instead Guid, because this is really the Category PK as your migration code has interpreted.