Multiple self-referencing naviation properties

Question

We sell a product for which we issue license numbers and that the customer can upgrade annually. I'd like to setup a License POCO that keeps track of this upgrade information by defining UpgradedTo and UpgradedFrom navigation properties, which would allow us to easily move up/down the "chain" of related licenses. So basically something like the following:

public class License
{
    [Key]
    public string LicenseNum { get; set; }
    // Other properties relating to license omitted...

    // Optional relationship.
    public License UpgradedTo { get; set; }

    // Optional relationship.
    public License UpgradedFrom { get; set; }
}

I'm really struggling how to define this with EF Annotations and Fluent API. I think the self-referencing aspect is what is tripping me up.

We'd also like to be able to set either one of these UpgradeTo/UpgradeFrom properties on a give License and have EF take care of the "opposite" Upgrade property on the other end of the relationship. So something like the following:

// Licenses upgraded 1 > 2 > 3
License lic1 = CreateLicense('1');
License lic2 = CreateLicense('2');
License lic3 = CreateLicense('3');

using (var db = new Model1())
{
    // Insert into database
    db.Licenses.Add(lic1);
    db.Licenses.Add(lic2);
    db.Licenses.Add(lic3);
    db.SaveChanges();

    // Specify UpgradeFrom/UpgradeTo info only on lic2.
    lic2.UpgradedFrom = lic1;
    lic2.UpgradedTo = lic3;
    db.SaveChanges();

    // lic1 and lic3 automatically update possible?
    Debug.Assert(lic1.UpgradedTo == lic2);
    Debug.Assert(lic3.UpgradedFrom == lic2);
}

Answer 1

This scenario is very tricky because how dependency is working.

The trick is to add one or more additional "fake" properties to make the job.

This class will automatically set the UpgradedFrom property if you set an UpgradeTo value.

Example:

using (var ctx = new TestContext2())
{
    var license1 = ctx.Licenses.Add(new License() { LicenseNum = "1.0.0"});
    ctx.SaveChanges();

    var license2 = license1.UpgradeTo = new License() { LicenseNum = "1.0.2"};
    ctx.SaveChanges();

    var license3 = license2.UpgradeTo = new License() { LicenseNum = "1.0.3" };
    ctx.SaveChanges();
}

Entities

public class License
{
    [Key]
    public string LicenseNum { get; set; }

    private License _upgradeTo;
    private License _upgradedFrom;


    public License UpgradeTo
    {
        get { return _upgradeTo; }
        set
        {
            _upgradeTo = value;
            if (_upgradeTo != null && _upgradeTo.UpgradedFrom != this)
            {
                _upgradeTo.UpgradedFrom = this;
            }
        }
    }

    public License UpgradedFrom
    {
        get { return _upgradedFrom; }
        set
        {
            _upgradedFrom = value;
            if (_upgradedFrom != null && _upgradedFrom.UpgradeTo != this)
            {
                _upgradedFrom.UpgradeTo = this;
            }
        }
    }

    internal License InternalUpgradedTo
    {
        get { return UpgradeTo; }
    }

    internal License InternalUpgradedFrom
    {
        get { return UpgradedFrom; }
    }
}

Context

public  class TestContext2 : DbContext
{
    public TestContext2() : base(My.Config.ConnectionStrings.TestDatabase)
    {

    }
    public DbSet<License> Licenses { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<License>()
            .HasOptional(v => v.UpgradeTo)
            .WithOptionalDependent(x => x.InternalUpgradedFrom);

        modelBuilder.Entity<License>()
            .HasOptional(v => v.UpgradedFrom)
            .WithOptionalDependent(x => x.InternalUpgradedTo);
    }
}