Code first causing required relation to be optional?

Question

public class Client
{
    public Int32 ClientID { get; set; }

    public virtual ICollection<Inquiry> InquiryManufacturers { get; set; }
    public virtual ICollection<Product> Products { get; set; }
    public virtual ICollection<Inquiry> InquiryRetailers { get; set; }
}

public class Product
{
    public Int32 ProductID { get; set; }

    public Int32 ClientID { get; set; }
    public virtual Client Client { get; set; }

    public virtual ICollection<Inquiry> Inquiries { get; set; }
}

public class Inquiry
{
    public Int32 InquiryID { get; set; }

    public Int32 ProductID { get; set; }
    public Int32 ManufacturerID { get; set; }
    public Int32 RetailerID { get; set; }
    public virtual Product Product { get; set; }
    public virtual Client Manufacturer { get; set; }
    public virtual Client Retailer { get; set; }
}

The Fluent Api is as

HasRequired(i => i.Product)
  .WithMany(p => p.Inquiries);
HasRequired(i => i.Manufacturer)
  .WithMany(p => p.InquiryManufacturers)
  .HasForeignKey(p => p.ManufacturerID);
HasRequired(i => i.Retailer)
  .WithMany(p => p.InquiryRetailers)
  .HasForeignKey(p => p.RetailerID);

So here are some classes that I have defined. They have relationships as follows: Client & Product have one to many, Client & Inquiry have one to many and Product & Inquiry have one to many. I am using Code First here. Now using fluent api I have defined the relationships, these relationships are supposed to be required ones, meaning Client & Product relationship can not be null as well as Client and Inquiry can't be null either.

However the relationship between Client & Inquiry is being forced to be an optional one with the Code First. When i try to make them required the EF does not generate the database.

Can someone tell me what is wrong with my model that it is causing the EF to not create a required relationsship between Client & Inruiry ? Is this due to cascade delete ? As I read some where the mssql can only have one cascade delete path between Client, Product and Inquiry. Any help explaination would be nice.

Answer 1

EF Code First sets the cascade delete true on the generated foreign keys constraint by default. And because you can only have one cascade paths you get an exception something like this:

"Introducing FOREIGN KEY constraint 'Inquiry_Retailer' on table 'Inquiries' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

In your model you have 3 cascade paths from Client to Inquiry:

Client --InquiryRetailers--> Inquiry
Client --InquiryManufacturers--> Inquiry
Client --Products--> Product --Inquiries--> Inquiry

So you need to set WillCascadeOnDelete(false) at least for two relations (depending on your requirements):

modelBuilder.Entity<Inquiry>().HasRequired(i => i.Product)
    .WithMany(p => p.Inquiries);
modelBuilder.Entity<Inquiry>().HasRequired(i => i.Manufacturer)
    .WithMany(p => p.InquiryManufacturers)
    .HasForeignKey(p => p.ManufacturerID).WillCascadeOnDelete(false);
modelBuilder.Entity<Inquiry>().HasRequired(i => i.Retailer)
     .WithMany(p => p.InquiryRetailers)
     .HasForeignKey(p => p.RetailerID).WillCascadeOnDelete(false);