Jul 19 2013

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

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.

Jul 19 2013

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

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.