How do I properly structure the following models in a way that will allow a successful code first migration?

Question

Thanks for looking.

I want to create the following Image domain model:

    [Table("Image")]    
    public class Image
    {
        [Key]
        public int Id { get; set; }
        [Required(ErrorMessage = "Please enter a title.")]
        public string Title { get; set; }
        [Required(ErrorMessage = "Please enter a caption.")]
        public string Caption { get; set; }
        [Required(ErrorMessage = "Please enter a file.")]
        public string File { get; set; }
    }

Then, I want multiple other models to make use of it. For example:

[Table("Product")]
public class Product
{
    [Key]
    public int Id { get; set; }
    [Required(ErrorMessage = "Please enter a title.")]
    public string Title { get; set; }
    [Required(ErrorMessage = "Please enter a description.")]
    public string Description { get; set; }
    public int ImageId { get; set; }
    public virtual Image Image { get; set; }       
}

[Table("User")]
public class User
{
    [Key]
    public int Id { get; set; }
    [Required(ErrorMessage = "Please enter a name.")]
    public string Name { get; set; }
    public int ImageId { get; set; }
    public virtual Image Image { get; set; }        
}

When managing (creating) a Product or User I do want for a SINGLE image to be required. Also, the image used for Product or User will not be used by any other entity.

The Problem

When I attempt to deploy this code first model to the database via Package Manager Console (PM> update-database), I get the following error:

Introducing FOREIGN KEY constraint 'FK_dbo.Product_dbo.Image_ImageId' on table 'Product' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

So the message returned is suggesting that I alter the SQL statements which won't work since I am automating this via Package Manager Console / CodeFirst.

The Question

That said, how do I create a common Image Entity Model, use it as a required property in multiple other entities, and still deploy via CodeFirst without violating multiple cascade paths rules?

Thanks in advance.

Answer 1

I reproduced your scenario, step by step, and I didn't get any errors. However, I believe that you already have an existing database, which might cause the error you described above.

To solve the cycles or multiple cascade paths error, just map the relationships using Fluent API, like this:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Product>()
        .HasRequired(i => i.Image)
        .WithMany()
        .HasForeignKey(i => i.ImageId)
        .WillCascadeOnDelete(false);

    modelBuilder.Entity<User>()
        .HasRequired(i => i.Image)
        .WithMany()
        .HasForeignKey(i => i.ImageId)
        .WillCascadeOnDelete(false);

    base.OnModelCreating(modelBuilder);
}

I believe it should be enough to solve your problem.

Your second problem is more complicated:

When managing (creating) a Product or User I do want for a SINGLE image to be required. Also, the image used for Product or User will not be used by any other entity.

With your current database structure, I can't see how it can be possible at database level. The easiest workarounds I can imagine are:

1. Create a separated Image entity (like UserImage, ProductImage) for Users and Projects (which doesn't worth in my opinion)
2. Validate the relationship at application level

Hope it helps!