How do I create a table that has a composite key each member of which is a foreign key member to other tables

Question

I am trying to do code first with annotations (for the first time) on an MVC project.

I have created the following POCOs.

[Table("Customers")]
public partial class Customer
{
    public int Id { get; set; }

    [Required]
    [DisplayName("First Name")]
    public string FirstName { get; set; }

    [DisplayName("Last Name")]
    [Required]
    public string LastName { get; set; }
    //other properties...
 }

[Table("Vehicles")]
public partial class Vehicle
{
    [Required]
    public int Id { get; set; }

    [Required]
    public int CustomerId { get; set; }

    [Required]
    public string Make { get; set; }

    [Required]
    public string Model { get; set; }

    [Required]
    public string Year { get; set; }

    //other fields
    [ForeignKey("CustomerId")]
    public virtual Customer Customer { get; set; }
}


[Table("CustomerAppointments")]
public partial class CustomerAppointment
{

    [Key,Column(Order=0)]
    public int CustomerId { get; set; }

    [Key,Column(Order=1)]
    public int VehicleId { get; set; }

    public DateTime? AppointmentDate { get; set; }

    public DateTime? AppointmentTime { get; set; }

    public string AvailableDays { get; set; }

    //other fields

    [ForeignKey("CustomerId")]
    public virtual Customer Customer { get; set; }

    [ForeignKey("VehicleId")]
    public virtual Vehicle Vehicle { get; set; }
}

I think my intent here is fairly obvious. I have customers. Those customers have vehicles. I want to create a table CustomerAppointments where a customer and one of the customers vehicles is scheduled for a service.

For the record, this is not the whole model and has been simplified for the purposes of the question.

I am using MvcScaffolding to build out the EF items and the views.

Everything compiles but when I try to navigate to the Customers page (actually a class not mentioned that references customers) I am getting the following error...

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

I have tried different annotations and even tried to use the fluent API with something like this...

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<CustomerAppointment>()
        .HasRequired(ca => ca.Customer)
        .WithRequiredPrincipal()
        .WillCascadeOnDelete(false);

    modelBuilder.Entity<CustomerAppointment>()
        .HasRequired(ca => ca.Vehicle)
        .WithRequiredPrincipal()
        .WillCascadeOnDelete(false);

}

But I cannot get it to work. I have read every sample I can find on google and SO but to no avail.

PS...if this can work with Annotations only that would be my preference.

score 2 · Answer 1 · answered Jul 19 '13 at 22:31

Your model has two cascading delete paths from Customer to CustomerAppointment when a Customer is deleted:

Customer -> Vehicle -> CustomerAppointment
Customer -> CustomerAppointment

That's not allowed in SQL Server and causes the exception. You need to disable cascading delete for at least one of those three subpaths which is only possible with Fluent API. For example the Customer -> Vehicle path:

modelBuilder.Entity<Vehicle>()
    .HasRequired(v => v.Customer)
    .WithMany()
    .HasForeignKey(v => v.CustomerId)
    .WillCascadeOnDelete(false);

You could also make CustomerId nullable to have an optional relationship in which case EF will disable cascading delete by default. But changing a required to an optional relationship expresses a change in business rules which I wouldn't do just to avoid Fluent API.

BTW: Is it really correct that CustomerAppointment should have a composite primary key? It would mean that a given customer with a given vehicle could only have one service appointment. Couldn't there be many appointments for the same customer/vehicle combination at different appointment dates? If yes, you should rather have a separate key for CustomerAppointment and CustomerId and VehicleId would be just foreign keys without being part of the primary key.

+1 because I was just typing something similar, but this is more eloquent; it also confirms I wasn't crazy. — Chris, Jul 19 '13 at 22:38

Charlie Brown · Answer 2 · 2013-07-19T21:18:28.723

By convention, cascade deletes are handled by the introduction of the actual foreign key into your model. If you use a non-nullable foreign key, it will require delete. Use a nullable foreign key to turn it off.

Change your class to the following by making the foreign keys nullable:

[Table("CustomerAppointments")]
public partial class CustomerAppointment
{

    [Key,Column(Order=0)]
    public int? CustomerId { get; set; }

    [Key,Column(Order=1)]
    public int? VehicleId { get; set; }

    public DateTime? AppointmentDate { get; set; }

    public DateTime? AppointmentTime { get; set; }

    public string AvailableDays { get; set; }

    //other fields

    [ForeignKey("CustomerId")]
    public virtual Customer Customer { get; set; }

    [ForeignKey("VehicleId")]
    public virtual Vehicle Vehicle { get; set; }
}

Remember to also remove the fluent mapping.

From http://msdn.microsoft.com/en-US/data/jj679962

If a foreign key on the dependent entity is not nullable, then Code First sets cascade delete on the relationship. If a foreign key on the dependent entity is nullable, Code First does not set cascade delete on the relationship, and when the principal is deleted the foreign key will be set to null.

It won't work in this case because `CustomerId` and `VehicleId` are parts of the primary key which you can't make nullable. — Slauma, Jul 19 '13 at 22:34
@Slauma Correct, thanks for pointing that out. As you stated in your answer, its odd to have that composite key. — Charlie Brown, Jul 20 '13 at 12:34

score 0 · Answer 3 · answered Jul 19 '13 at 21:05

0

It seems like you are better off using the database-first approach and then generating the model using ado enity data model.

answered Jul 19 '13 at 21:05

RiceRiceBaby

1,546
4
16
30

2

Strongly disagree, code first is a much better choice and can handle this easily – Charlie Brown Jul 19 '13 at 21:16

How do I create a table that has a composite key each member of which is a foreign key member to other tables

3 Answers3