Efcore: Cannot create Many-To-Many self relationship with details

[cdie]

I'm trying to do a self Many-To-Many with details.

Considering this objects :

    class Sub
    {
        public Guid Id { get; set; }
        public DateTime ValidDate { get; set; }
        public Guid MasterId { get; set; }
        public Master Master { get; set; }

        public Guid MasterId2 { get; set; }
        public Master Master2 { get; set; }

        public string MasterCateg {get;set;}
    }

    class Master

    {
        public Guid Id { get; set; }
        public int Data { get; set; }

        public ICollection<Sub> Subs { get; set; }

    }

And this mapping :

            var subBuilder = modelBuilder.Entity<Sub>();
            subBuilder.HasOne(s => s.Master).WithMany(m => m.Subs).HasForeignKey(s => s.MasterId);
            subBuilder.HasOne(s => s.Master2).WithMany(m => m.Subs).HasForeignKey(s => s.MasterId2);

            var mBuilder = modelBuilder.Entity<Master>();
            mBuilder.HasMany(m => m.Subs).WithOne(m => m.Master).HasForeignKey(m => m.MasterId).IsRequired();
            mBuilder.HasMany(m => m.Subs).WithOne(m => m.Master2).HasForeignKey(m => m.MasterId2).IsRequired(false);

Adding a migration giving me the following exception :

Cannot create a relationship between 'Master.Subs' and 'Sub.Master2', because there already is a relationship between 'Master.Subs' and 'Sub.Master'. Navigation properties can only participate in a single relationship.

How can I do such a mapping ? I precise that I've already tried to comment subBuildermapping or mBuilder mapping, without success, always the same exception.

[ajcvickers]

@cdie The problem is that you cannot share navigation properties between two relationships. This is because changes made to the navigation property become ambiguous as to which relationship (i.e. FK) should be modified. You could do something like this:

``` C#
public class Sub
{
public Guid Id { get; set; }
public DateTime ValidDate { get; set; }

public Guid? MasterId { get; set; }
public Master Master { get; set; }

public Guid? MasterId2 { get; set; }
public Master Master2 { get; set; }

public string MasterCateg { get; set; }

}

public class Master

{
public Guid Id { get; set; }
public int Data { get; set; }

public IEnumerable<Sub> Subs 
    => Subs1.Concat(Subs2).Distinct();

private ICollection<Sub> Subs1 { get; set; }
private ICollection<Sub> Subs2 { get; set; }

}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity()
.Ignore(e => e.Subs);

modelBuilder.Entity<Sub>()
    .HasOne(s => s.Master)
    .WithMany("Subs1")
    .HasForeignKey(s => s.MasterId);

modelBuilder.Entity<Sub>()
    .HasOne(s => s.Master2)
    .WithMany("Subs2")
    .HasForeignKey(s => s.MasterId2);

}
```

This creates the two separate relationships and then exposes the union of both collections on the public surface of the entity.

[cdie]

@ajcvickers You surely might be right, on a data/EF perspective.

However, this will break our domain model, and moreover, our domain logic. In fact, the main idea behind the collection and the two Master reference is that they've not the same relation with it. I just create a sample to demonstrate our issue. Doing the way you said, it'll break our domain logic.

We're working in medical software, and this two objects represents a replacement session between 2 medics, with a start date and an end date. In our Session object, we need to have the "master" medic and the "slave" medic. This is making non-sense, from a business perspective, to have two collection within a medic object. Medic object should only have a list of Sessions, not SessionsAsMaster and SessionsAsSlave. It's pretty ugly, it have to exists another way to perform this properly ...

[ajcvickers]

@cdie I'm finding it hard to correlate the domain model you described to the code you posted. Could you post some code representing your domain model, without changing it in any way to try to fit with EF?

[cdie]

@ajcvickers My explaination was for another issue, sorry. Here the code to one-to-many self relationship :

public class LettreCle : AggregateRoot
{
[...]
        protected ICollection<LettreCleAssociation> ColLettreCleAssociations
        {
            get; set;
        }
        public IReadOnlyList<LettreCleAssociation> LettreCleAssociation
        {
            get
            {
                if (ColLettreCleAssociations == null || ColLettreCleAssociations.Count == 0)
                {
                    ColLettreCleAssociations.Load(this);
                }
                return ColLettreCleAssociations.ToList().AsReadOnly();
            }
        [...]
}

public class LettreCleAssociation : Entity
{
        [Ignore]
        [KeyStorageOf(nameof(LettreClePrincipale))]
        protected Guid LettreCle_Guid { get; set; }

        [Ignore]
        [KeyStorageOf(nameof(LettreCleSecondaire))]
        protected Guid? LettreCleSecondaire_Guid { get; set; }

        [Column("LTC_ID_1")]
        [ForeignKey(OnDeleteAction.Cascade)]
        [Required]
        public LettreCle LettreClePrincipale
        {
            get
            {
                if (_principale == null)
                    _principale = this.Load<LettreCle>(LettreCle_Guid);
                return _principale;
            }
            protected set
            {
                _principale = value;
            }
        }

        [Column("LTC_ID_2")]
        [ForeignKey(OnDeleteAction.None)]
        public LettreCle LettreCleSecondaire
        {
            get
            {
                if (_secondaire == null && LettreCleSecondaire_Guid.HasValue)
                    _secondaire = this.Load<LettreCle>(LettreCleSecondaire_Guid.Value);
                return _secondaire;
            }
            protected set
            {
                _secondaire = value;
            }
        }

[some more info...]
}

Please note that this attributes are our custom attributes to auto-generate mapping for EF or another ORM we've designed.
The generated mapping is :

var builder = model.Entity<LettreCleAssociation>();
builder.HasOne("LettreClePrincipale").WithMany("ColLettreCleAssociations").HasForeignKey("LettreCle_Guid").IsRequired();
builder.HasOne("LettreCleSecondaire").WithMany("ColLettreCleAssociations").HasForeignKey("LettreCleSecondaire_Guid");

[ajcvickers]

@cdie Couple of questions about the domain model:

• What is supposed to happen to a new LettreCleAssociation if it is added to the LettreCleAssociations of a LettreCle. Is this supposed to not be allowed? If it is allowed, then should LettreClePrincipale now point to the LettreCle, or should LettreCleSecondaire now point to the LettreCle, or should neither, or should both?
• In the same situation--that is, a new LettreCleAssociation if it is added to the LettreCleAssociations of a LettreCle, what FK values should be saved in the database when this change is persisted?

[cdie]

@ajcvickers

We've defined a method to manage the collection, because the LettreCle domain entity should be responsible of its relationship

public void AddLettreCleLink(LettreCle ltc)
{
    if (ltc.Code != this.Code)
    {
        var link = new LettreCleAssociation(this, ltc);
        if (!ParentLettreCleAssociations.Any(l => l == link))
        {
            ParentLettreCleAssociations.Add(link);
        }
    }
}

As you can see, the LettreClePrincipale (first parameter of ctor) is always the entity that is currently managed, so it should point to current entity. The LettreCleSecondaire is another instance with a different Code (the Code is a member that's unique per LettreCle).

In a save situation, both members of LettreCleAssociation table should point to different LettreCle Id. In fact, the LettreCleAssociation table just contains two columns, each one pointing to a LettreCle.Id member, to make relationship.

As a current workaround, just know that the solution you point out in your first answer just work, but not appear to be optimal as a domain perspective in our case.

[ajcvickers]

@cdie So what you have is two relationships where the domain model constrains and merges those relationships on one side into a single collection. The way to handle this in EF is to either:

• Have two collection navigation properties (which can be private) and implement business logic to handle the domain constraints and expose a single collection.
• Omit EF's navigation properties all together and manage the loading of the related entities from the database manually.

EF can't just use a single collection because there are two relationships and so modifications to that single collection would be ambiguous. In other words, that single collection must have additional, non-EF, logic that determines how to handle it.

I'm curious how your other ORM handles this collection. Maybe there is something we can learn from it. Two specific questions:

• How does it handle changes to the collection?
• How does it know what to load for the collection?

[cdie]

@ajcvickers To be compatible with EF Core, I made two collections, one as parent, and one as child, to link back things, and everything seems to be fine. Also, because it makes sense to navigate only from one parent to its children in our case, we've just expose the childrenCollection.

The other ORM was a custom one from our company, made by me, based on a SQLite one. It considers this kind of relationship "many-to-many link" like this as "pure virtual table", which only contains both Id for both side. In this particular case, the many-to-many is self-reference, but it doesn't make changes when it's done with another object. I created this LettreCleAssociation object just to works with EF.
My previous iteration of the ORM just load it as Lazy loading as you can see in property, when accessing the property, by the Load(this) method (we keep them until EF Core handles Lazy Loading).

Handling changes in collection was pretty straight-forward, in fact, a single instance of LettreCle has many LettreCle in its collection, telling me that the current instance is in fact the parent, and all LettreCle within the collection are children. This is how I can handle the hierarchy stuff. To lazy load them, because I'm passing the current instance, I now that I have to load all child for this specific parent's id.

[ajcvickers]

@cdie Thanks for the additional info.

[christiandelbianco]

Works for me too! Thanks!!!