EF6 Code First, multiple cascade paths, and strange FK behaviour

Question

I'm going to try to put only the relevant parts of the model here, because there are quite a lot of classes. Hopefully it's enough to capture the problem:

public class Solve
{
    public int SolveID { get; set; }

    public int LocationID { get; set; }
    public virtual Location Location { get; set; }

    public int ProfileID { get; set; }
    public virtual Profile Profile { get; set; }

    public int BillID { get; set; }
    public virtual Bill Bill { get; set; }

    public int? PanelID { get; set; }
    public virtual Panel Panel { get; set; }
}

public class Location
{
    public int LocationID { get; set; }

    [Index]
    [StringLength(48)]
    public string Name  { get; set; }

    [Index]
    public State State { get; set; }

    public double Latitude  { get; set; }
    public double Longitude { get; set; }

    public virtual List<Profile> Profiles { get; set; }
}

public class Profile
{
    public int ProfileID { get; set; }

    public int LocationID { get; set; }
    public virtual Location Location { get; set; }

    public double Capacity { get; set; }

    public virtual List<ProfileSample> ProfileSamples { get; set; }
}

public class ProfileSample
{
    [Key, ForeignKey("Profile")]
    [Column(Order = 1)]
    public int ProfileID { get; set; }
    public virtual Profile Profile { get; set; }

    [Key]
    [Column(Order = 2)]
    [DataType(DataType.Date)]
    public DateTime Date { get; set; }

    [Key]
    [Column(Order = 3)]
    public TimeSpan TimeOfDay { get; set; }

    public double SampleValue { get; set; }
}

So it was all working fine until I introduced the Solve class, at which point it started complaining about "multiple cascade paths". I added the following to the context and it seemed to be OK from then on:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Solve>()
        .HasRequired(s => s.Location)
        .WithRequiredDependent()
        .WillCascadeOnDelete(false);
}

Except that it wasn't behaving properly:

using (Model.BlueData bd = new Model.BlueData())
{
    Random rng = new Random();

    s = new Model.Solve()
    {
        Location = bd.Locations.Find(rng.Next(0, bd.Locations.Count())),
        Bill     = bd.Bills.Find(rng.Next(0, bd.Bills.Count())),
        Profile  = bd.Profiles.Find(rng.Next(0, bd.Profiles.Count()))
    };

    bd.Solves.Add(s);
    bd.SaveChanges();

    s = bd.Solves
        .Where(u => u.SolveID == s.SolveID)
        .Include(u => u.Location)
        .Include(u => u.Profile)
        .Include(u => u.Profile.ProfileSamples)
        .Include(u => u.Bill)
        .FirstOrDefault();
}

So the above code just generates a random Solve object, adds it to the data context, and then retrieves it again along with all the associated data. There's certainly a more elegant way of doing this, but right now this is just testing code to make sure the other parts of my app are working.

So as expected, when I create the Solve s object, s.Location is a particular location, with ID, say, 1609, and of course s.LocationID and s.SolveID both equal 0.

Upon adding it to the data context and saving changes, the s.SolveID equals the location's ID (1609, in this example). It's very strange. I tried adding a [Key] attribute to SolveID and [ForeignKey("Location")] to LocationID in the Solve class, but it made no difference.

I've tried various things like removing Profile from Solve, or removing the List<Profile> Profiles from Location. I don't remember now exactly, but a few things did work to correct the s.SolveID set to the location's ID behaviour.

But these properties are all there for a reason, and if possible I'd prefer not to have to remove them just to get this working. I don't understand why this is happening, or how to correct it properly. I appreciate any assistance.

Answer 1

Firstly Location is being referred in Solve object, so location is the principal and solve is dependent, I think in that case this mapping is wrong -

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Solve>()
        .HasRequired(s => s.Location)
        .WithRequiredDependent()
        .WillCascadeOnDelete(false);
}

It should be -

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Solve>()
        .HasRequired(s => s.Location)
        .WillCascadeOnDelete(false);
}

Secondly, since Solve referencing with the foreign key the definition should be -

public class Solve
{
    public int SolveID { get; set; }

    [ForeignKey("Location")]
    public int LocationID { get; set; }
    public virtual Location Location { get; set; }

    [ForeignKey("Profile")]
    public int ProfileID { get; set; }
    public virtual Profile Profile { get; set; }

    [ForeignKey("Bill")]
    public int BillID { get; set; }
    public virtual Bill Bill { get; set; }

    [ForeignKey("Panel")]
    public int? PanelID { get; set; }
    public virtual Panel Panel { get; set; }
}

Thirdly, when saving objects, you have to save the 1) principal ones first, otherwise EF will try to create new entries or 2)you have to attach them manually. Among these the easiest that I found is (1), after saving the principal end I assign only the foreign key and EF works as expected.

using (Model.BlueData bd = new Model.BlueData())
{
    Random rng = new Random();

    s = new Model.Solve()
    {
        LocationID = bd.Locations.Find(rng.Next(0, bd.Locations.Count())).LocationID,
        BillID     = bd.Bills.Find(rng.Next(0, bd.Bills.Count())).BillID,
        ProfileID  = bd.Profiles.Find(rng.Next(0, bd.Profiles.Count())).ProfileID
    };
    s.Bill = s.Location = s.Profile = null; //otherwise EF tries to create them
    bd.Solves.Add(s);
    bd.SaveChanges();

    s = bd.Solves
        .Where(u => u.SolveID == s.SolveID)
        .Include(u => u.Location)
        .Include(u => u.Profile)
        .Include(u => u.Profile.ProfileSamples)
        .Include(u => u.Bill)
        .FirstOrDefault();
}

EDIT 1: The location class to be -

public class Location
{
    [Key]  //mark location ID as primary key
    public int LocationID { get; set; }

    [Index]
    [StringLength(48)]
    public string Name  { get; set; }

    [Index]
    public State State { get; set; }

    public double Latitude  { get; set; }
    public double Longitude { get; set; }

    public virtual List<Profile> Profiles { get; set; }
}