Ozzah Ozzah - 2 months ago 12
C# Question

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

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

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();
}