Efcore: Many-to-many (skip) navigation properties

[ajcvickers]

This is one of the building blocks for many-to-many. However, it is also more broadly useful than just many-to-many.

Problem

Consider a model typical for a many-to-many relationship:

```C#
public class Post
{
public int PostId { get; set; }
public string Title { get; set; }
public string Content { get; set; }

public List<PostTag> PostTags { get; set; }

}

public class Tag
{
public string TagId { get; set; }
public string Content { get; set; }

public List<PostTag> PostTags { get; set; }

}

public class PostTag
{
public int PostId { get; set; }
public Post Post { get; set; }

public string TagId { get; set; }
public Tag Tag { get; set; }

public DateTime LinkCreated { get; set; }

}

Now consider how to load a Post and include all associated Tags. This requires using two navigation properties--one to go from Post to PostTag, and a second to go from PostTag to Post. For example:

```C#
var postsAndTags 
    = context.Posts
        .Include(e => e.PostTags)
        .ThenInclude(e => e.Tag)
        .ToList();

Likewise, querying across Posts and Tags also requires use of PostTags:

```C#
var post
= context.Posts
.Where(e => e.PostTags.Select(e => e.Tag.Content).Contains("MyTag")).ToList();

### Proposal

The goal of forwarded navigation properties is to allow navigation properties to be defined which _forward to_ some of the direct navigation properties in the model.

For example, forwarded navigation properties could be added to Post and Tag in the model above:

```C#
public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }

    public List<Tag> Tags { get; set; } // Skips over PostTag to Tag

    public List<PostTag> PostTags { get; set; }
}

public class Tag
{
    public string TagId { get; set; }
    public string Content { get; set; }

    public List<Post> Posts { get; set; } // Skips over PostTag to Post

    public List<PostTag> PostTags { get; set; }
}

These forwarded navigation properties can then used to write simpler queries. For example, the queries from above can now be:
```C#
var postsAndTags
= context.Posts
.Include(e => e.Tags)
.ToList();

```C#
var post
    = context.Posts
        .Where(e => e.Tags.Select(e => e.Content).Contains("MyTag")).ToList();

Notes

Things to consider (non-exhaustive):

• Model building API will be needed for the general case
  
  
  
  • Conventions/sugar may be use later for many-to-many pattern
  
  
  • Configuration by attribute could be considered, but might be too complex
  
  
• It should be possible to define the skip-level navigation that skip over relationships that do not themselves expose navigation properties
  
  
  
  • This could involve shadow navigation properties if needed
  
  
  • However, also note that it is expected that "skipped" entity types (like for the join table in the example) can still be used explicitly as needed. This allows additional properties to be used, such as the LinkCreated property in the example above.
  
  

Tasks:

• [x] Model support
• [x] Fluent API
• [x] Change tracking support
• [x] Query support
• [x] Tracking query tests

[AndriySvyryd]

Implementation proposal

Fluent API

```C#
modelBuilder.Entity()
.HasMany(e => e.Posts)
.WithMany(e => e.Tags)
.UsingEntity(
pt => pt
.HasOne()
.WithMany(e => e.PostTags),
pt => pt
.HasOne()
.WithMany(e => e.PostTags))
.ToTable("PostTags");

This is the signature:
```C#
EntityTypeBuilder<TAssociationEntity> UsingEntity<TAssociationEntity>(
    Func<EntityTypeBuilder<TAssociationEntity>, ReferenceCollectionBuilder<TLeftEntity, TAssociationEntity>> configureLeft,
    Func<EntityTypeBuilder<TAssociationEntity>, ReferenceCollectionBuilder<TRightEntity, TAssociationEntity>> configureRight)

Plus nested closure, non-generic and string-based overloads

This shape has the following advantages:

• The types have equal standing in the relationship, there's no 'main' or 'principal' end.
• Navigations on the foreign keys can be omitted.
• Foreign keys are configured using existing API and can specify FK and PK properties in the same call chain.
• Allows to omit the foreign key configuration when by-convention configuration is supported.
• Allows to omit the association entity configuration when property bag entity types are supported.

Disadvantages:

• Not immediately clear what arguments should be specified, this is especially true for the non-generic version.

Metadata

C# public interface ISkipNavigation : IPropertyBase { IEntityType DeclaringEntityType { get; } IEntityType TargetEntityType { get; } IForeignKey ForeignKey { get; } IForwardedNavigation Inverse { get; } bool IsCollection { get; } bool IsOnPrincipal { get; } (Other methods with default implementations) }

Design limitations

• Can only be configured to skip over one entity type (spanning two foreign keys)
• Cannot cross aggregate boundries

Scoping limitations

• Won't be discovered by convention (Always needs UsingEntity)
  
  
  
  • Later we could support a parameterless UsingEntity<>() call that will find the foreign keys by convention
  
  
• Configuration by attribute will not be supported
• Has to be many-to-many and the involved foreign keys must be non-unique
• The skipped entity type is on the dependent end of both foreign keys
• Must have a non-shadow inverse and it can't use the same CLR property

[roji]

Here are some comments (remember I'm a beginner here so be patient, there may be nonsense ahead :)):

• TJoinEntity seems like it's a bit relational-specific, are we expecting this API to possibly be usable also in non-relational scenarios? If so a different name may be better (e.g. TIntermediateEntity).
• In the lambda parameters of Using, is HasOne missing a lambda parameter?
• It's a little odd for me that Using returns an EntityTypeBuilder over the join table - we start with modelBuilder.Entity() at the top, and find ourselves configuring a completely different table at the bottom (do we already have examples of this?)... I can imagine people thinking the ToTable should configure the Tags table and not PostTags. It may be cleaner/more consistent to require all configuration for the join table to be done by going back to ModelBuilder.

Can only be configured to skip over one entity type (spanning two foreign keys)

Is this only a limitation of the proposed fluent API, i.e. would users be able to drop down to metadata to set up a forwarded navigation spanning more than one entity type?

Here's a different proposal for a fluent API. There are probably several reasons why this makes no sense, am proposing this mainly to learn:

c# modelBuilder.Entity<Post>() .HasMany(p => p.Tags) .Through(p => p.PostTags) .ThenThrough(pt => pt.Tag) .WithMany(t => t.Posts) .Through(t => t.PostTags) .ThenThrough(pt => pt.Post);

The Through/ThenThrough (can be called Using/Via/Whatever) allows skipping over more than one entity type and is consistent with Include/ThenInclude (and OrderBy). It's also defined based on the paths, from one side all the way to the other, rather than putting the join table in the center. This also may allow for some wackier setups, e.g. where the paths in the two directions don't go through the same entity types (no concrete/compelling scenario yet though :)).

[AndriySvyryd]

TJoinEntity seems like it's a bit relational-specific, are we expecting this API to possibly be usable also in non-relational scenarios? If so a different name may be better (e.g. TIntermediateEntity).

I think an entity that is joining two other entities also makes sense in non-relational context and has the benefit of being familiar in relational context. Intermediate is also ok, but is too long for my liking.

In the lambda parameters of Using, is HasOne missing a lambda parameter?

No, it's optional

It's a little odd for me that Using returns an EntityTypeBuilder over the join table

After you call HasOne/HasMany or OwnsOne/OwnsMany without nested lambda you can no longer get back the original EntityTypeBuilder, so this is an established pattern

It may be cleaner/more consistent to require all configuration for the join table to be done by going back to ModelBuilder.

For shared type entity types it would be more verbose as the Type and entity type name would need to be specified again.

Is this only a limitation of the proposed fluent API, i.e. would users be able to drop down to metadata to set up a forwarded navigation spanning more than one entity type?

No, in metadata IForwardedNavigation just has a foreign key and the inverse IForwardedNavigation, there's no way to specify any intermediate foreign keys. This was done to simplify implementation as I couldn't think of compelling scenarios requiring skipping more than one entity type (this also becomes confusing quickly if more than two many-to-one FKs are involved)

Here's a different proposal for a fluent API.

A big downside is that it doesn't allow to configure FK and principal key properties

[roji]

It's a little odd for me that Using returns an EntityTypeBuilder over the join table

After you call HasOne/HasMany or OwnsOne/OwnsMany without nested lambda you can no longer get back the original EntityTypeBuilder, so this is an established pattern

Right. What I meant to say is that Using returning an EntityTypeBuilder could be problematic, as using may assume the can configure the original entity type instead of the join table's entity type. In your example above, it kinda looks like the ToTable affects the Tag entity rather than the PostTag entity.

A big downside is that it doesn't allow to configure FK and principal key properties

I guess there's one fundamental thing I'm misunderstanding... I was assuming that the basic navigations (Post.PostTags and Tag.PostTags) need to be configured independently and separately, and that the forwarded navigation is only configured on top of other existing (underlying) navigations. If that were the case, it seems like the forwarded navigation wouldn't need to deal with FK/PK properties, just reference the underlying navigations. In metadata, the IForwardedNavigation would then effectively contain a list of underlying navigations (which is where the FK/PK info would be taken from).

[AndriySvyryd]

I was assuming that the basic navigations (Post.PostTags and Tag.PostTags) need to be configured independently and separately

Yes, that's what happens in the metadata, but we can add Fluent API that circumvents that restriction to allow terser configuration. We don't yet have shared-type entity types, but if we did separate configuration would look something like this:

```C#
modelBuilder.Entity()
.HasMany(e => e.PostTags, "PostTag")
.WithOne()
.HasForeignKey(e => e.TagId);

modelBuilder.Entity()
.HasMany(e => e.PostTags, "PostTag")
.WithOne()
.HasForeignKey(e => e.PostId);

modelBuilder.Entity>("PostTag")
.HasKey("TagId", "PostId")
.ToTable("PostTags");

modelBuilder.Entity()
.HasMany(e => e.Posts)
.WithMany(e => e.Tags)
.UsingEntity>(
pt => pt
.HasOne()
.WithMany(e => e.PostTags),
pt => pt
.HasOne()
.WithMany(e => e.PostTags),
"PostTag");

As opposed to
```C#
modelBuilder.Entity<Tag>()
  .HasMany(e => e.Posts)
  .WithMany(e => e.Tags)
  .UsingEntity<IDictionary<string, object>>(
    pt => pt
      .HasOne()
      .WithMany(e => e.PostTags)
      .HasForeignKey("PostId"),
    pt => pt
      .HasOne()
      .WithMany(e => e.PostTags)
      .HasForeignKey("TagId"),
    "PostTag")
  .ToTable("PostTags")
  .HasKey("TagId", "PostId");

[smitpatel]

It cannot be totally based on navigations since navigations could be missing in CLR type. Especially if we are going to use property bag entity to represent join entity in future so API needs to use FKs.

Questions for @AndriySvyryd

• In your code snippets above, in snippet 1, HasMany has both lambda and string (new pattern?). How would it refer to PostTag entity backed by dictionary? Further, the navigations to join entity would not be present in general case.
• In snippet 2, HasKey would return KeyBuilder, you cannot configure ToTable on it.

Still thinking about how to integrate shared entityType configuration if any so that we are future proof.

[AndriySvyryd]

In your code snippets above, in snippet 1, HasMany has both lambda and string (new pattern?). How would it refer to PostTag entity backed by dictionary?

The string is necessary to name the shared-type entity type.

Further, the navigations to join entity would not be present in general case.

Navigations are optional.

In snippet 2, HasKey would return KeyBuilder, you cannot configure ToTable on it.

Fixed

[smitpatel]

I will present this in design meeting on Wednesday.

[davidtkeshela]

Hello guys, I've searched the whole internet but I couldn't find any solutions for my problem, so here it is :
I'm using .net core 3.1, ef core 3.1.1
I have 3 classes, with many to many relationship :

```C#
public class Item
{
public int Id { get; set; }
public string OuterCode { get; set; }

    [Required]
    [StringLength(100)]
    public string Name { get; set; }
    public string BarCode { get; set; }
    public string Articul { get; set; }
    public int BoxCoefficient { get; set; }
    // public ICollection<Photo> Photos { get; set; }  
    public ICollection<ItemPrice> ItemPrices { get; set; }
    public ICollection<ItemStock> ItemStocks { get; set; }

    public Item()
    {
        ItemPrices = new Collection<ItemPrice>();
        ItemStocks = new Collection<ItemStock>();
    }
}

public class PriceType
{
public int Id { get; set; }
public string Name { get; set; }
public ICollection ItemPrices { get; set; }

}

[Table("ItemPrices")]
public class ItemPrice
{
public int ItemId { get; set; }
public Item Item { get; set; }
public PriceType PriceType { get; set; }
public int PriceTypeId { get; set; }
public double Price { get; set; }
}
}

When calling GET method I get an error "A possible object cycle was detected which is not supported. This can either be due to a cycle or if the object depth is larger than the maximum allowed depth of 32."

Get method looks like this : 

```C#
var itemprices = await _context.Items
                .Include(e => e.ItemStocks)
                .ThenInclude(e => e.Warehouse)
                .Include(e => e.ItemPrices)
                .ThenInclude(e => e.PriceType)
                .ToListAsync();

I'll be thankful if you can help me.

[davidtkeshela]

Warehouse class is :

```C#
public class Warehouse
{
public int Id { get; set; }

    public string OuterCode { get; set; }

    [Required]
    [StringLength(100)]
    public string Name { get; set; }

    public string City { get; set; }

    [Required]
    public string Address { get; set; }
}

And ItemStocks : 

```C#
  public class ItemStock
    {
        public int ItemId { get; set; }
        public int WarehouseId { get; set; }
        public Item Item { get; set; }
        public Warehouse Warehouse { get; set; }
        public int NumberInStock { get; set; }
        public int NumberAvailable { get; set; }
    }
}

I want to include in my GET method NumberInStock , NumberAvailable and Price(from ItemPrices) values

[ajcvickers]

@davidtkeshela The JSON serialization built into .NET Core 3.0/3.1 does not support cycles. This has been implemented for .NET 5 which will ship in November--see https://github.com/dotnet/runtime/issues/30938

For now, the workaround is to use Newtonsoft.Json, as is described in the issue I linked.

Also, for future reference, it's usually better to create a new GitHub issue rather than commenting on an existing issue unless you are sure that the existing issue is the same as yours. Comments on existing issues tend to get lost.

[davidtkeshela]

@davidtkeshela The JSON serialization built into .NET Core 3.0/3.1 does not support cycles. This has been implemented for .NET 5 which will ship in November--see dotnet/runtime#30938

For now, the workaround is to use Newtonsoft.Json, as is described in the issue I linked.

Also, for future reference, it's usually better to create a new GitHub issue rather than commenting on an existing issue unless you are sure that the existing issue is the same as yours. Comments on existing issues tend to get lost.

Thanks a lot, I found this workaround a day after I posted my question.

Also, thanks for the advice, about posting a question in the future, I'm relatively new to programming and you know how it goes

[MattAltieri]

Some of my clients are very particular about including an identity column to use as the PK even on associative entity tables. In EF Core 3 the documentation says to configure a many-to-many relationship using a composite key on the joining table, but it's not required as far as I can tell. Currently when my client is firm about not using composite keys I'll add the surrogate key and use HasAlternateKey to enforce the unique composite key instead, which seems to work fine.

When this new functionality is implemented, will I still be able to take that approach for those clients, or will the composite primary key be required?

[AndriySvyryd]

@MattAltieri This will allow to use any PK on the association table

[JonPSmith]

Sorry, I'm a bit late to this discussion, but I wanted to ask about many-to-many relationships where the order of one side of the relationship matters. My example is authors linked to a book - the authors of a book have to be listed in a specific order.

I see that the linking class, PostTag in your example, can be set up by fluent API. Does that mean I could add an Order property? Also, I would then have to have access to the linking class to order by the Order property.

NOTE: I am NOT saying you should support this. I just wanted to know if it is possible in your existing design. If not than I can continue to use the existing way of setting up many-to-many relationships.

[AndriySvyryd]

@JonPSmith Yes, PostTag is a regular entity type.

[HorunS]

Hello, @AndriySvyryd!
Is there any chance to test new fluent api? Maybe you know when it will be possible. Thanks!

[AndriySvyryd]

@HorunS The Fluent API is already available in 5.0.0-preview3, but query support is not implemented yet.

[AndriySvyryd]

@JonPSmith General ordered collection support is tracked by https://github.com/dotnet/efcore/issues/2919. Since it would be client-side you would be able to define (almost) any type of ordering.

[smitpatel]

@lajones/ @ajcvickers - This is unblocked from query perspective to make required changes to change tracker.

[smitpatel]

@ajcvickers - Did you enable all tracking query tests for M2M?

[ajcvickers]

If you enumerate the tests, I can see if they are enabled. :-)

[ajcvickers]

Many-to-many is now ready to try in the daily builds. Note that we are still finishing up a few things and fixing bugs--see the weekly status update this week for more information and a simple end-to-end example.

[neoGeneva]

Hey, is there plans to add scaffolding for many-to-many relationships? It doesn't look like it's included in the tasks for this issue, and I couldn't see any other issues tracking it.

[ajcvickers]

@neoGeneva Detecting existing join tables in reverse engineering is not something we have done--thanks for bringing this up. I have filed #22475 to track this.

[neoGeneva]

Awesome, thanks @ajcvickers !

[gojanpaolo]

Hello, is there a way to avoid defining the property for one of the entities?
e.g.
c# builder.HasMany(p => p.Tags).WithMany(); // notice no parameter in `WithMany`

Thank you.

[AndriySvyryd]

@gojanpaolo Not yet https://github.com/dotnet/efcore/issues/3864