Efcore: Discussion on many-to-Many relationships (without CLR class for join table)

[0xdeafcafe]

Note:

• Issues #10508 and #19003 have been created to track actual work on many-to-many relationships. The new issue is locked so that it can be subscribed to for updates on the implementation without noise.
• This older issue has been re-purposed to be discussion about many-to-many relationships in EF Core. It will not (for the time being) be locked because it is important to us that we don’t shut down channels of communication with the community.

With regard to feedback, I think it is worth reiterating some comments made a few months ago. We read and consider all feedback (and try to make the right choices based on it) no matter how it is delivered; that is, whether it is provided politely and with respect or not. Yelling at us or others with different opinions at best makes you feel good and us/them feel bad. It doesn’t achieve anything concrete. We are doing our best to prioritize features and implement them in the best way we can. We really appreciate your feedback and it makes a big difference on shaping the product. We personally appreciate it more when feedback is delivered in a respectful way, but please don’t stop providing constructive feedback.

---

Original issue:

I'm assuming there isn't a way to do Many-to-Many relationships in EF Core yet, as I've found nothing mentioning how to do it. Are there any examples on getting round this? Or am I just completely missing it, and it's actually there after all.

[bricelam]

The workaround is to map the join table to an entity.

``` C#
class Product
{
public int Id { get; set; }
public ICollection Categorizations { get; set; }
}

// NOTE: Entity key should be (ProductId, CategoryId)
class Categorization
{
public int ProductId { get; set; }
public Product Product { get; set; }

public int CategoryId { get; set; }
public Category Category { get; set; }

}

class Category
{
public int Id { get; set; }
public ICollection Categorizations { get; set; }
}

To navigate, use a Select:

``` C#
// product.Categories
var categories = product.Categorizations.Select(c => c.Category);

[0xdeafcafe]

Thanks!

[tonysneed]

@bricelam,

Are there plans to support many-to-many without mapping the intersection table?

Also, will there be support for marking related entities as added or deleted to indicate they should be removed from the relationship and not the table? With EF6 and prior the only way to do this was to dip down to the ObjectContext API and work with independent associations. Thanks!

[bricelam]

Yes, many-to-many associations will eventually be enabled via shadow state entities (#749) and skip-level navigation properties (not sure if there's a bug for that; I'll re-open this one).

I'm not sure I follow your second question, but @divega or @ajcvickers will know if it'll be covered by our "graph behavior" improvements.

[divega]

@tonysneed Re your second question, not sure I understand it either, but here is some data that might help:

We should have similar default behavior in EF Core as in previous versions of EF for the general case of removing an entity from a collection navigation property: this will cause the relationship to be marked as removed, not necessarily the entity.

Removing the relationship is promoted to an actual removal of the entity for identifying relationship only, i.e. when the primary key of the dependent entity contains the primary key of the principal entity and hence the dependent cannot be orphaned or re-parented. I believe we will have similar behavior for that too, although we have talked about deferring to the SaveChanges to detect if there are orphans entities in that state rather than trying to delete them immediately. @ajcvickers can elaborate/correct me on that.

If your question is about having API that allows to control the state of relationships without manipulating the collection navigation property directly on the entity, as described in https://entityframework.codeplex.com/workitem/271, then yes, I believe that would be something nice to have, however we haven't prioritized it. Your feedback would be helpful.

Does this help answer your question?

[tonysneed]

@bricelam, thanks for answering my first question. I'll be interested to learn more about implementing many-to-many via shadow state entities.

@divega, I'll clarify my second question. Let's say I have two entity classes that have a many-to-many relationship, for example, Employee and Territory from the Northwind sample database. If I wanted to add or remove a Territory from Employee.Territories, in a disconnected fashion using EF6, I would not be able to do so by setting EntityState on the Territory to Added or Deleted. Instead, I would call ObjectStateManager.ChangeRelationshipState, specifying the property name. Since ObjectContext is going away in EF Core, I'm just wondering how changing relationship state will work for entities in many-to-many relationships when performed in a disconnected manner (for example, from within a Web API controller). Hopefully this helps clarify my question a bit. Thanks!

[tonysneed]

Alight, I think I can clear everything up now that I've had a chance to try out a few things. The way to add or remove entities from a relationship in a disconnected fashion in vCurrent is not very straightforward or consistent when it comes to many-to-many relations. Stating that it is not possible without resorting to the OSM was incorrect. It can be done via the DbContext API but it's awkward. And what I'm wondering is if the API for EF Core could be improved to allow direct manipulation of the relationship, similar to what could be done via the OSM (albeit without needing to specify the navigation property name)?

For those following this conversation, here is what the OSM API looks like in vCurrent:

// Attach territory then set relationship state to added
context.Territories.Attach(territory);
var osm = ((IObjectContextAdapter)context).ObjectContext.ObjectStateManager;
osm.ChangeRelationshipState(employee, territory, "Territories", EntityState.Added);

So let's start with the vCurrent behavior of adding and removing entities from a m-m relation. Assuming that Employees and Territories have a m-m relationship, if I add a territory to an employee, then the territory will be marked as Added and EF will attempt to add it to the Territories table. If I want to just add the relationship to an existing territory, then I need to _explicitly mark it as Unchanged_, which makes sense but isn't all that intuitive. It's also inconsistent with the behavior for removing entities from a relationship, where removing a territory from an employee will not mark it as Deleted, _without the need to explicitly mark it as Unchanged_.

It seems to me that the remove behavior is a bit more straightforward. Adding a territory to the Territories collection of an employee could also leave it in an Unchanged state but result in adding it to the m-m relation. Then explicitly marking the entity as Added or Deleted would mean that you wanted to add to or remove from the Territories table as well as the m-m relation. This is the behavior that I would recommend for EF Core.

So this leads to the question of an API that would let you deal with the relationship directly. My question (which is hopefully clear by now) is whether could be a way in EF Core to change the state of a relationship. Following the proposal from CodePlex issue 271, it could look something like this:

context.Entry(employee).Member(e => e.Territories).Add(territory);

Even though may look somewhat intimidating, the important thing would be the ability to use the API from within the callback for AttachGraph (#1248), which is an Action. But for this to work, there would have to be a way to infer how the current entity in the graph is related to its parent (1-1, M-1, M-M). (This is what I do in my Trackable Entities project.) So we might need to add an overload of AttachGraph as follows:

void AttachGraph(object root, Action<EntityEntry, DbMemberEntry, EntityEntry> callback)

I'm probably getting this wrong, but we would need a parameter that represents how the current entity being iterated is related to its parent. (Here DbMemberEntry represents the navigation property and the second EntityEntry represents the parent of the current entity.) That way, we could figure out what kind of relationship it is and use the correct approach to attach the entity. Please let me know if I'm making sense here. :)

Cheers,
Tony

[ctolkien]

Just chiming in on this issue. Many-to-many is the one scenario that is blocking us from EF Core adoption. I realise that you can use intermediate mapping classes, but that requires significant changes to how we'd like to model things.

I note this is still tagged for the backlog. Is this feature on the roadmap for v1?

[damccull]

Sooo....gimme. Many-To-Many is probably the only thing left that I NEED in order to do what I want...I WANT more features, but many-to-many is all I NEED...probably.

[popcatalin81]

@bricelam, @rowanmiller Will this feature make into the RTM? I did not see it in the list of upcoming feature in the latest blog post. For me this is the only remaining feature preventing a full migration from EF6 to EF Core.

[rowanmiller]

@popcatalin81 you can have a many-to-many relationship but you need to include an entity that represents the join table. This is how it will be for our first stable release. In the future we will enable shadow state entities (entities that don't have a CLR type) and then you will be able to model it without a CLR class to represent the join table.

[lajones]

Note: see also #3352 where the user has a requirement to support a join table which defines the combination of the 2 foreign keys as unique, but does not provide a primary key - which makes defining an entity type for it impossible.

[bragma]

Regarding the original suggestion with the CLR class for join table, how should the "Categorization" class be used? Is it application's duty to create and delete instances of those class directly?
Something like...

var product = new Product();
var category = new Category();
var categorization = new Categorization()
{
Product = product,
Category = category
};

dbContext.Add(product);
dbContext.Add(category);
dbContext.Add(categorization);
dbContext.SaveChanges();

And to remove a relationship preserving product and category:

dbContext.Remove(categorization);
dbContext.SaveChanges();

[mjrousos]

I'll add a +1 for wanting many-to-many relationships without CLR objects for the join table. I have a website using EF6 that I'm trying to move forward to EF Core and it would save some hassle changing the model on the CLR side to be able to use the direct many-to-many modeling option that EF6 had.

[HerbertBodner]

+1 for wanting "many-to-many relationships without CLR objects for the join table" from my side as well.

[sthiakos]

What's interesting is when a Many-To-Many relationship has additional attributes/columns (e.g. DateOfAssociation, Priority, etc.) it becomes an observed entity. I can see that consistency would drive towards creating an intermediate Entity and I'm comfortable with the current implementation.

With that, requiring that we implement an intermediate Many-To-Many entity forces our object model to conform to database implementation, whereas hiding this detail leaves it in the hands of the ORM; not requiring an explicit intermediate makes the relationship seem more natural and represents the majority of Many-To-Many relationships.

[sven-n]

@sthiakos Well, it depends. Most of the data models and their relations I saw, do not need additional attributes/columns. In this cases the additional entity classes would just pollute the data model with unnecessary bloat. And depending on the size of a project, upgrading from EF6 could be a tremendously big task.

So I'm also voting for a solution without additional entity classes, and looking forward for the shadow state entities.

[bragma]

i'm not sure I understand how this feature request would work. Do you mean that no entity class is required but a table is still created and managed automatically by Entity?
I'm asking because after some initial confusion in creating navigations, I'd say that handling many-to-many mapping entities manually is not that bad...

[rowanmiller]

@bragma yep that is exactly what this feature is about. If the join table only contains the foreign key properties to each side of the relationship, then we want to allow folks not to have a join entity.

[Cowlephant]

How exactly are you intended to go about creating an entity that has one or more many to many relationships, and adding to those before saving changes to the database?

How do I work with the join table, assuming it is initially empty for this new entity? I already have a list of currently existing entities I wish to add to it, but not sure how to approach.

[rowanmiller]

@twilliamsgsnetx Given we don't support modelling this without a join entity just yet, it's the same as any other one-to-many relationship. In the following example, we're inserting a new Post, a new Tag, and an entry in the PostTag join entity/table that represents the many-to-many.

``` c#
var blog = new Blog { ... };
context.Blogs.Add(blog);

var tag = new Tag { ... };
context.Tags.Add(tag);

var postTag = new PostTag { Tag = tag, Post = post };
context.PostsTags.Add(postTag);

context.SaveChanges();
```

[zezelazo]

Really waiting for this now i need to move my code back to EF6

[TomGroeneboer]

Is there any progress on this? Cause I really would like this feature :D

[rowanmiller]

@TomGroeneboer - this will be a post 1.0.0 feature. In the meantime, you can model many-to-many with a join entity http://docs.efproject.net/en/latest/modeling/relationships.html#many-to-many.

[bdelaney]

@rowanmiller - Been fiddling with this for a few days now and still have questions. I reviewed the last link to the docs here. Not really sure what the OnModelCreating code is doing except setting up for creating foreign keys in the SQL table. I'll try building it tomorrow. My current experience is based on a non-trivial app with at least two many:many, one of which is a simple join table with just the two foreign keys.
But, as I review what you replied to @twilliamsgsnetx I am not clear how an anonymous type will automagically populate the join table if on one side of the many:many you are creating a new entity. In my case, the ID for the new entity is an auto-identity in the SQL table. _(guessing that I'm going to need to return the new PK and use it to create the join table record)_

Sorry if this is obvious to old EF hands, but the code first and EF Core approach are still pretty new to me.
Working with RC1 bits ASPNET Core MVC project. Just trying to get in line with best practices.

Also, in the example in the docs, the navigation properties are written like this:
public List<PostTag> PostTags { get; set; }
as opposed to pre EF Core where I usually wrote them like this:
public virtual ICollection<PostTag> PostTags { get; set; }
Does that make any difference?

Lastly, in the example there is no property DbSet, just the two parent entities:
public DbSet<Post> Posts { get; set; } public DbSet<Tag> Tags { get; set; }

This means some operations, like say Seed, will not be able to call upon the many:many entity. Is there anything wrong with setting the DbSet property for the join table?

[rowanmiller]

@bdelaney there really isn't anything here different than other entities, the many-to-many is just modelled as two separate one-to-many relationships. There is nothing magic that happens with the join entity, you need to add and remove instances of it the same as you would for other entities.

Also, in the example in the docs, the navigation properties are written like this:
public List<PostTag> PostTags { get; set; }
as opposed to pre EF Core where I usually wrote them like this:
public virtual ICollection<PostTag> PostTags { get; set; }
Does that make any difference?

Using virtual in EF6.x would give you lazy loading proxies, but lazy loading is not implemented in EF Core, so it makes no difference.

Lastly, in the example there is no property DbSet, just the two parent entities:

public DbSet<Post> Posts { get; set; } 
public DbSet<Tag> Tags { get; set; } 

This means some operations, like say Seed, will not be able to call upon the many:many entity. Is there anything wrong with setting the DbSet property for the join table?

You can add it. The entity is discovered as part of the model because it is referenced from other entities that do have a DbSet. So totally up to you if it has a DbSet or not. BTW you can use DbContext.Add() to add entities that don't have a DbSet exposed

[BradleyDHobbs]

Shouldn't this be a higher priority?

Especially for enterprise level applications that have a high number of many-to-many relationships.

It would be annoying to have to create two separate one-to-many relationships for each many-to-many relationship.

[rowanmiller]

@BradleyDHobbs it's just a matter of ordering that features make sense to add to the new EF Core code base. This is a high priority feature, but some of the other things we are working on make more sense to implement first.

[VSG24]

This is soooooooo needed!

[arthurspa]

High priority on this feature, please!

+1

[lpunderscore]

Has anyone been able to get the many-to-many relations working using a join entity?

I have a simple scenario with 3 entities and 2 join tables:

• Person (abstract) setup for tph.
• Member : Person
• Child : Person
• Spouse : Person

join entities:

• MemberChildren
• MemberSpouses

 public class Member : Person
    {
        public List<MemberChildren> Children { get; set; }
        public List<MemberSpouse> Spouses { get; set; }
    }

setup tph in modelBuilder

 modelBuilder.Entity<Person>().HasDiscriminator<string>("PersonType")
                .HasValue<Member>("Member")
                .HasValue<Child>("Child")
                .HasValue<Spouse>("Spouse");

setup relations

//setup complex key for relationnal tables

   modelBuilder.Entity<MemberChildren>()
                .HasKey(t => new { t.ChildId, t.ParentId});

            modelBuilder.Entity<MemberSpouse>()
               .HasKey(t => new { t.MemberId, t.SpouseId});

        //setup relations.. many to many

            modelBuilder.Entity<MemberChildren>().HasOne(p => p.Parent).WithMany(p => p.Children)
                .HasForeignKey(fk => fk.ParentId)
                .OnDelete(Microsoft.EntityFrameworkCore.Metadata.DeleteBehavior.Restrict);

            modelBuilder.Entity<MemberChildren>().HasOne(p => p.Child).WithMany(p => p.Parents)
                .HasForeignKey(fk => fk.ChildId)
                .OnDelete(Microsoft.EntityFrameworkCore.Metadata.DeleteBehavior.Restrict);

            modelBuilder.Entity<MemberSpouse>().HasOne(p => p.Member).WithMany(p => p.Spouses)
                .HasForeignKey(fk => fk.MemberId)
                .OnDelete(Microsoft.EntityFrameworkCore.Metadata.DeleteBehavior.Restrict);

            modelBuilder.Entity<MemberSpouse>().HasOne(p => p.Spouse).WithMany(p => p.Members)
                .HasForeignKey(p => p.SpouseId)
                .OnDelete(Microsoft.EntityFrameworkCore.Metadata.DeleteBehavior.Restrict);

I can then add relations simply by using:

mem1.Children.Add(new MemberChildren() { Child = child1, Parent = mem1 });
mem1.Children.Add(new MemberChildren() { Child = child2, Parent = mem1 });

and if still in the same context (still in the using) I can fetch that information back

var member = context.Members.Include(c => c.Children).Where(m => m.Id == 1).FirstOrDefault();
                var children = member.Children.Select(c => c.Child);

                foreach(var child in children)
                {
                    Console.WriteLine(child.FirstName);
                }

But if I exit the console, rerun the same code above to fetch a member and its children, the children collection will be 2 null entries.

[VSG24]

@lpmeunier Here's how I do it: (Works as of RC2)

We have an entity named User that has many to many relations with 3 other entities:

public class User
{
    public User()
    {
        UserClaims = new HashSet<UserClaim>();
        UserPayments = new List<UserPayment>();
        ShoppingOrders = new HashSet<ShoppingOrder>();
    }

    [Key]
    public long UserId { get; set; }

    [StringLength(256)]
    [Required]
    public string Email { get; set; }
    public bool EmailConfirmed { get; set; }

    // other stuff

    public ICollection<UserClaim> UserClaims { get; set; }
    public ICollection<UserPayment> UserPayments { get; set; }
    public ICollection<ShoppingOrder> ShoppingOrders { get; set; }
}

now for example for the relation with Claim entity:

public class Claim
{
    public Claim()
    {
        UserClaims = new HashSet<UserClaim>();
    }

    [Key]
    public long ClaimId { get; set; }

    // other stuff

    public ICollection<UserClaim> UserClaims { get; set; }
}

and the join entity:

public class UserClaim
{
    public long UserId { get; set; }
    public User User { get; set; }

    public long ClaimId { get; set; }
    public Claim Claim { get; set; }
}

finally this should go in the OnModelCreating of your db context class:

modelBuilder.Entity<UserClaim>().HasKey(t => new { t.UserId, t.ClaimId });

modelBuilder.Entity<UserClaim>()
    .HasOne(pt => pt.User)
    .WithMany(p => p.UserClaims)
    .HasForeignKey(pt => pt.UserId);

modelBuilder.Entity<UserClaim>()
    .HasOne(pt => pt.Claim)
    .WithMany(t => t.UserClaims)
    .HasForeignKey(pt => pt.ClaimId);

modelBuilder.Entity<UserClaim>().Property(pt => pt.Count);

[lpunderscore]

@VSG24 how does fetching related entities look in your code?

For example,

var user = context.Users.Include(c => c.Claims).FirstOrDefault(u => u.Id == 3);

Do you then have access to:

var claims = user.Claims.Select(c => c.Claim);

make sure you have not created the user and claims in the current context instance IE: make sure this is fetched from the db in a fresh context.

My setup is pretty much exacly like yours, and the claims variable will return null for me.

The only difference is that im using tph on my entities..

[VSG24]

@lpmeunier Get the user, also included related UserClaims and THEN include Claims:

var user = _db.Users.Include(d => d.UserClaims).
ThenInclude(d => d.Claim).First(d => d.UserId == 3);

And now I have access to claims.

var claims = user.UserClaims.Select(d => d.Claim);

[lpunderscore]

@VSG24 Thanks a lot, the ThenInclude() extension did the trick!

[Lutando]

Just wondering how is it possible in the identity repository, where the requirement of CLR classes in the join table was not needed...

If you notice there is a join table entity called IdentityUserRole which maps the entities IdentityUser and IdentityRole however this join table entity has no CLR object in its definition.

Currently moving my code into 1.0.0 to get many to many relationships working. Got it working on a small sample piece of code.

[rowanmiller]

@Lutando You can have a relationship with a navigation property on only one side, so IdentityUser has a Roles property (that contains IdentityUserRole), but there is no navigation pointing back the other way. Here is the line of code that sets this up in their model.

[rowanmiller]

@Lutando you may have already seen this, but here are the docs on setting up many-to-many https://docs.efproject.net/en/latest/modeling/relationships.html#many-to-many

[Lutando]

@rowanmiller thanks for the heads up. I dont think I need a reverse nav prop in my use case, but that's good to know.

[DanielYKPan]

@rowanmiller If I set up the many-to-many model like the doc you recommended Post and Tag scenario how could I Remove a relationship (remove a tag from a post)? I mean how I could detach a tag from a post?

[pMonfared]

var user = _db.Users.Include(d => d.UserClaims).
ThenInclude(d => d.Claim).First(d => d.UserId == 3);

how to use Take & Skip on Claims before this line?

.First(d => d.UserId == 3);

[pMonfared]

Linq : (use Skip & Take)

var post1Tags = context.BlogPosts
      .Where(blogPosts => blogPosts.Id == 1)
      .SelectMany(blogPosts => blogPosts.BlogPostsJoinTags)
      .Select(blogPostsJoinTags => blogPostsJoinTags.Tag)
      .Skip(0)
      .Take(10)
      .ToList();

Sql Export :

SELECT   [blogPosts.BlogPostsJoinTags.Tag].[Id],
         [blogPosts.BlogPostsJoinTags.Tag].[Name]
FROM     [BlogPosts] AS [blogPosts]
         INNER JOIN
         [BlogPostsJoinTags] AS [blogPosts.BlogPostsJoinTags]
         ON [blogPosts].[Id] = [blogPosts.BlogPostsJoinTags].[BlogPostId]
         INNER JOIN
         [Tags] AS [blogPosts.BlogPostsJoinTags.Tag]
         ON [blogPosts.BlogPostsJoinTags].[TagId] = [blogPosts.BlogPostsJoinTags.Tag].[Id]
WHERE    [blogPosts].[Id] = 1
ORDER BY @@ROWCOUNT
OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY;

Source

[leonardoporro]

I'm working on a library similar to GraphDiff for EF Core and added a very rudimentary patch that allows to implement simple many to many association scenarios like User -> Roles.
It uses a generic ManyToManyEntity<,> as a Clr type and a convention to map table and property names.
I know it is not optimal and doesn't support a lot of scenarios, but if you can adapt it for your needs, the project is here: EF Core Detached.
It doesn't pollute the model, just need a [ManyToMany] attribute to be added to the property.
BTW, EF core model metadata is just awesome!
I would like to implement it directly on EF, but I'm not up to the task.

[ghost]

So curious about when, if ever, this is going to be implemented.

This is a complete blocker for our adoption of EntityFramework Core, and obviously a blocker for a lot of other teams based on the commentary. I recognize the complexity of the problem, but this, along with lazy loading, are the reason we've stayed away from core in general.

[divega]

@nicklbailey thanks for your feedback. Although we have made a lot of progress, the comment @rowanmiller made at https://github.com/aspnet/EntityFramework/issues/1368#issuecomment-205406766 still applies: Many-to-many competes with other fundamental features we need to implement. We will use your feedback in our upcoming prioritization discussions.

[rpedretti]

Guys, I'm having some thoughts about the way I'm getting data from a many-to-many relationship. Could not find some examples besides de Blog/Post.

Given the classes

public sealed class Rule: BaseDomain
{
    public List<Option> Options{ get; set; }
    ///Some More props
}

public sealed class Option: BaseDomain
{
    ///Some More props
}

public sealed class Menu : BaseDomain
{
    [Required]
    [JsonIgnore]
    public ICollection<MenuDishes> MenuDishes { get; set; }

    public List<Dish> Dishes { get; set; }
    ///Some More props 
}

public sealed class MenuDishes
{
    public int MenuId { get; set; }
    public Menu Menu { get; set; }

    public int DishId { get; set; }
    public Dish Dish { get; set; }
}

public sealed class Dish: BaseDomain
{
    [Required]
    public Rule Rule{ get; set; }

    [JsonIgnore]
    public ICollection<MenuDish> MenuDishes { get; set; }

    ///Some More props
}

On My Db Context I have configured the relation:

modelBuilder.Entity<MenuDishes>()
    .HasKey(t => new { t.MenuId, t.DishId });

modelBuilder.Entity<MenuDish>()
    .HasOne(md=> md.Menu)
    .WithMany(m => m.MenuDishes)
    .HasForeignKey(md => md.MenuId);

modelBuilder.Entity<MenuDish>()
     .HasOne(md => md.Dish)
     .WithMany(d => d.MenuDishes)
     .HasForeignKey(md => md.DishId);

The Menu class has the ICollection for Db storage and a List of dish to be returned by a web api, since the user don't need to know how i persist data. So, at my MenuDAO I do the following to get a full object

public Menu GetFull(int id)
{
    var menu = Context.Menus
    .Include(m => m.MenuDishes)
    .Where(m => m.Id == id)
    .FirstOrDefault();

    menu.Dishes = menu.MenuDishes.Select(md => 
    {
        Context.Entry(md).Navigation(nameof(MenuDishes.Dish)).Load();
        Context.Entry(md.Dish).Navigation(nameof(Dish.Rule)).Load();

        return cp.Dish
    }).ToList();

    return menu;
}

And since MenuDishes is a collection I could't use the ThenInclude approach. Am I doing something wrong? I sense some bad smell here. What if i had a deeper hierarchy? I would have to replicate the `Load()" as many time as goes my tree?

Insertion is going like this:

public override Menu Add(Menu instance)
{
    instance.MenuDishes = instance.Dishes.Select(d =>
        new MenuDish { Menu = instance, DishId = d.Id }
    ).ToList();

    Context.Add(instance);
    Context.SaveChanges();

    return instance;
}

[ajcvickers]

@rpedretti ThenInclude should work:
C# var menu = Context.Menus .Include(m => m.MenuDishes).ThenInclude(e => e.Dish) .Where(m => m.Id == id) .FirstOrDefault();
If you are using Visual Studio, then there is a bug that makes intellisense show the wrong thing. See #4117 You may want to provide feedback on the Roslyn bug tracking this: https://github.com/dotnet/roslyn/issues/8237

[rpedretti]

Thank @ajcvickers, things are very smooth now!

[cedriclange]

I'm so stuck with this many to many relationship

[Daniel15]

The docs (https://docs.microsoft.com/en-us/ef/core/modeling/relationships) mention this limitation:

Many-to-many relationships without an entity class to represent the join table are not yet supported.

But don't provide any clues as to when this will be coming. Is there an ETA for this?

[ajcvickers]

@Daniel15 It's currently on our backlog and not scheduled for any of the immediate upcoming releases.

[fedoranimus]

That's disappointing @ajcvickers. It would appear to be an issue for many people (including myself). I know it will drastically simplify my code and make it maintainable.

[KSubedi]

I agree with @fedoranimus, I was hoping this feature would be available with the 1.1 release, I am disappointed to hear that its not even scheduled.

[VSG24]

I can't say that I'm surprised. The whole .NET Core ecosystem is a big mess right now.

[fletchsod-developer]

If you really look at several other bugs, you'll find they're being fixed one by one. We're actually getting there but not fast enough.

[johnkwaters]

I am trying to work with the approach of creating a join entity, and it kind of works... except for this case:

Say you have a many to many from User to Role, so a user can have many roles, a role can be held by many users. I would have User ( Id, Name ), Role (Id, Name) and UserRole (UserId, RoleId).

Inserting a new User is fine, I add a couple of UserRole children, and save.

Now, if I fetch that user to a web app, and in the web app, I add another role, then send it back to my API, when I try to save the User object, I get duplicated roles. The ones that were already there get added again. If I add a unique constraint to (UserId, RoleId), then instead I get a unique constraint violation.

A related problem is if I send the User to a client, it removes a role, and then I save the user with one less role. The dropped role is not actually removed.

So...do I need to go about manipulating EF to understnad these changes to relationships in a better way for the freamework to not create duplicates and understand when 'links' need to be deleted? When I try, I keep ending up with various exceptions to do with the object already being in the context....

What is the correct pattern to use?

[Daniel15]

when I try to save the User object, I get duplicated roles. The ones that were already there get added again.

The way I handle this is by only adding relationships that don't already exist. Given a set of old roles (currently in the database) and new roles (the roles that have been selected), you need to remove roles that are in old roles but not in new roles, and add roles that are in new roles that are not in old roles. Something like this:

public void SetRoles(User user, IEnumerable<int> roleIds)
{
    var newRoles = new HashSet<int>(roleIds);
    var oldRoles = new HashSet<int>(user.UserRoles.Select(x => x.RoleId));

    // Remove roles that aren't set any more
    user.UserRoles = user.UserRoles.FindAll(x => newRoles.Contains(x.RoleId));

    // Add roles that were newly-added
    foreach (var roleId in newRoles.Where(id => !oldRoles.Contains(id)))
    {
        user.UserRoles.Add(new UserRole
        {
            RoleId = roleId,
            User = user,
        });
    }
    Context.SaveChanges();
}

If you have several many-to-many relationships, you could probably pull it out as an extension method to reuse the logic.

Here's some actual code that I'm using on my blog to save a many-to-many relationship between posts and categories: https://github.com/Daniel15/Website/blob/be62cba1f5b9af64e7dd88620433562f03db9e06/Daniel15.Data/Repositories/EntityFramework/BlogRepository.cs#L368-L386

Entity Framework 6 handles this much better, as it does all of this for you. I really hope EF Core gains proper support for many-to-many relationships in the future.

[johnkwaters]

Thanks Daniel, that approach worked. I have one more wrinkle... the NEW version of the object comes in as a DTO which is mapped through AutoMapper to the OLD version from the DB. So your code goes in the mapping like so:

public class MappingProfile : Profile
{
///

/// Constructor
///

public MappingProfile()
{
CreateMap().ReverseMap()
.ForMember( dest => dest.ListItems, opt => opt.Ignore())
.AfterMap(DoProductListMapping);
...
}

    private void DoProductListMapping(ProductDTO src, Product dest)
    {
        var newListItems = new HashSet<int>(src.ListItems.Select( li => li.ListItemId));
        var oldListItems = new HashSet<int>(dest.ListItems.Select(li => li.ListItemId));

        // Remove listitems that are no longer present
        dest.ListItems = dest.ListItems.ToList().FindAll(x => newListItems.Contains(x.ListItemId));

        // Add list items that were newly-added
        foreach (var listItemId in newListItems.Where(id => !oldListItems.Contains(id)))
        {
            dest.ListItems.Add(new ProductListItem
            {
                ListItemId = listItemId,
                Product = dest,
            });
        }
    }

[joehoeller]

.Include(x => x.TableName ) not returning relationships (from principal table to dependent table), or only returning one row of data, FIX HERE:

This was the fix to my issue, add this to Startup.cs
public void ConfigureServices(IServiceCollection services) method:services.AddMvc().AddJsonOptions(options => { options.SerializerSettings.ReferenceLoopHandling = ReferenceLoopHandling.Ignore; });

Related Articles on the above:

https://benohead.com/c-circular-reference-detected-serializing-object

Note: If you want to also be able to have your API ready for TCP/IP or MVC/Razor, do not use that and just use the [JsonIgnore] attribute, which is actually a better practice than the above.

http://www.newtonsoft.com/json/help/html/serializationattributes.htm#JsonIgnoreAttribute

Also, in Startup.cs make sure you have this at the top:

using Microsoft.EntityFrameworkCore; 
using Newtonsoft.Json; 
using Project_Name_Here.Models;

[Vannevelj]

Can you shed some light as to why this isn't more of a priority since it's such a fundamental database aspect? I wanted to try EF again after 2 years of not using it and after even just a day I'm already ditching it because the first two models that I wanted to connect in my app require a bloated solution.

[sthiakos]

While considered an essential, there is a workaround and someone could argue that it's an optimization. Remember that once the relationship has some other attribute (e.g. Association Date) it suddenly becomes and entity.

I have a class folder called ManyToMany that contains these classes that will be eliminated when this features is eventually implemented.

These guys have ton of other difficult and truly essential features to implement (ahem...TPT/TPC ;) along with maintaining quality and performance. @bricelam, @ajcvickers, @divega , @rowanmiller - Thanks for all you do.

[Zonciu]

Is there any way to create a relationship by using object id only? For DDD rules, we should not relate aggregate roots with object reference.

There is an example:

    public class User : AggregateRoot<string>
    {
        public string Id { get; private set; }
        public string UserName { get; private set; }
        public ICollection<string> DepartmentRelationships { get; private set; } // Department id collection
    }

    public class Department : AggregateRoot<string>
    {
        public string Id { get; private set; }
        public string DepartmentName { get; private set; }
        public ICollection<string> UserRelationships { get; private set; } // User id collection
    }

Is it possible to implement, or I have to use another relationship class to relate User and Department?
The relationship class:

    public class UserDepartmentRelationship
    {
        public string UserId { get; set; }
        public User User { get; set; } // Is required ?

        public string DepartmentId { get; set; }
        public Department Department { get; set; } // Is required ?
    }

[ajcvickers]

@Zonciu You can create a relationship with no navigations. For example:

```C#
modelBuilder.Entity()
.HasMany()
.WithOne();

However, if you want to have a many-to-many relationship, then you'll still need the join table. So something like:

```C#
public class User : AggregateRoot<string>
{
    public string Id { get; private set; }
    public string UserName { get; private set; }
}

public class Department : AggregateRoot<string>
{
    public string Id { get; private set; }
    public string DepartmentName { get; private set; }
}

public class UserDepartmentRelationship
{
    public string UserId { get; set; }
    public string DepartmentId { get; set; }
}

With configuration:
```C#
modelBuilder.Entity()
.HasMany()
.WithOne();

modelBuilder.Entity()
.HasMany()
.WithOne();
```

[Zonciu]

@ajcvickers Thank you! This works:

public class User : AggregateRoot<string>
    {
        public string Id { get; private set; }
        public string UserName { get; private set; }

        public ICollection<UserDepartmentRelationship> DepartmentRelationships { get; set; } =
            new List<UserDepartmentRelationship>();
    }

    public class Department : AggregateRoot<string>
    {
        public string Id { get; private set; }
        public string DepartmentName { get; private set; }

        public ICollection<UserDepartmentRelationship> UserRelationships { get; set; } =
            new List<UserDepartmentRelationship>();
    }

    public class UserDepartmentRelationship
    {
        public string UserId { get; set; }
        public string DepartmentId { get; set; }
    }

    public class Context : AbpDbContext
    {
        public Context(DbContextOptions options)
            : base(options)
        { }

        public DbSet<User> User { get; set; }
        public DbSet<Department> Department { get; set; }
        public DbSet<UserDepartmentRelationship> UserDepartmentRelationship { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<User>()
                        .HasMany(u => u.DepartmentRelationships)
                        .WithOne();

            modelBuilder.Entity<User>().HasKey(u => u.Id);

            modelBuilder.Entity<Department>()
                        .HasMany(d => d.UserRelationships)
                        .WithOne();

            modelBuilder.Entity<Department>().HasKey(d => d.Id);

            modelBuilder.Entity<UserDepartmentRelationship>()
                        .HasKey(r => new {r.UserId, r.DepartmentId});
        }
    }

[SirGordon]

How do I vote this up? Roadmap feels like 2.0 will ship still without this much needed feature.

[ctolkien]

@SirGordon this is still on the backlog, it will not be making it into the 2.0 release.

[jasoncavett]

@SirGordon To specifically answer your question, you can vote up this issue via the voting buttons that are on it (see the little +:smiley face: symbol in the upper right of the main post). At the time of writing there are 66 thumbs ups right now.

[mahald]

not making it into 2.0 cry

[bassebaba]

• I'm on EF core 1.1.2.
• Using table structure from this example : https://docs.microsoft.com/en-us/ef/core/modeling/relationships#many-to-many
• Using database first with Scaffold-DbContext

I don't really get how one's supposed to add "Tags" to a "Post"
var tag = new Tag ();
OK... now what? My post doesnt hold any sort of collection of tags... Only PostTags.
Do I manually have to create a PostTag and then add it?

What if I have a collection of Tags?
Back in teh days (normal EF), I had an ICollection on my Post and I could just do:

var tags = ctx.Tags.Where(x => x.TagID > 5).ToList();
post.tags = tags;
ctx.SaveChanges;

How do I do this in EF core?

Sorry for my stupid questions but the lack of many-to-many in EF core just messes with my head.

Edit: And then, how to use it?
Like this? I.e, the way from post is to first select tags from posttag and then select TagName?

            foreach (var p in posts)
            {
               Console.WriteLine($"{p.PostId} : {p.Title}, { string.Join(",", p.PostTag.Select(t => t.Tag).Select(t => t.TagName))}");
            }

[smitpatel]

To add Tags to post.

Single Tag
post.PostTags.Add(new PostTag { Tag = tag });
Multiple tags
post.PostTags.AddRange(tags.Select(t => new PostTag { Tag = t }));

To get tags for a post

var tags = post.PostTags.Select(pt => pt.Tag);

[bassebaba]

@smitpatel Ok thanks. So unless this issue gets implemented, there's no "cleaner" solution?

I.e: at the current state the "code" / "application" needs to "know about" , and navigate thru "PostTags". That's really really cluttered in my opinion.

Also, Scaffold-DbContext creates "Post" as this:

    public partial class Post
    {
        public Post()
        {
            PostTag = new HashSet<PostTag>();
        }

        public int PostId { get; set; }
        public string Title { get; set; }

        public virtual ICollection<PostTag> PostTag { get; set; }
    }

ICollection has no "AddRange"...

I tried stuff like this:

        var post= ctx.Post.First();
            var tags = ctx.Tag;
            post.PostTag = tags.Select(x => new PostTag {Tag = x}).ToList();
            ctx.SaveChanges();

But that gives me errors on insert,I guess EF doesn't like that 'Im replacing the PostTag collection.

This does work:
tags.ForEach(x => post.PostTag.Add(new PostTag {Tag = x}));

But gives errors if I'm trying to add a duplicate tag, so then I guess I always have to clean out duplicates before adding tags.

So either I'm completely stupid, or there are some gotchas with Many-to-Many support as of now.
I guess the best thing for the moment is to place helper-functions on a partial of the Post-class with different operations, e.g "AddTagToPost(Tag t)", "AddRangeOfTagsToPost(...), ReplaceAllTagsWith(..)"

[sky-code]

Ok, so microsoft corporation which is always care about compatibility, can't implement so important for migration feature for 2 years and this feature still on backlog.. what a joke, brand new cloud optimized entity framework in version 2.0 not have basic compatibility with old EF ))
Can anyone from EF team make a checklist of features need to be done for this issue and add this to next milestone than implement this for the next release? Really, I don't get it, so many people need this feature just for try new EF on already existing projects but it still in backlog.
It's definitely have to be done in next release.

[Socolin]

@sky-code https://weblogs.asp.net/ricardoperes/missing-features-in-entity-framework-core-1 like this ?

[sky-code]

Yep, something like this, list itself is good but more important is priority order of tasks from that list.
Modeling must be implemented first, than behaviour features
as example at this moment for porting exists model if you use many-to-many you need to rework entire model and usage of it to new style of many-to-many with join entity, and than rework it back when this feature will be implemented and same with complex types

but behaviour features like lazy loading can have much simpler workaround, adding preloading in some cases, some queries can be re-implemented temporary and than turned back

My main point is - that modeling has to be done as the first step that there was something to work with at least

[cedriclange]

my aspnet core project become like a sort of unfinished one because some
features cant be done the way we used to, and i think about a workaround
for me ...i feel like giving up because dont eanna come back, when those
features will be implemented...crazy..

On Jun 20, 2017 20:09, "Igor" [e-mail] wrote:

Yep, something like this, list itself is good but more important is
priority order of tasks from that list.
Modeling must be implemented first, than behaviour features
as example at this moment for porting exists model if you use many-to-many
you need to rework entire model and usage of it to new style of
many-to-many with join entity, and than rework it back when this feature
will be implemented and same with complex types

but behaviour features like lazy loading can have much simpler workaround,
adding preloading in some cases, some queries can be re-implemented
temporary and than turned back

My main point is - that modeling has to be done as the first step that
there was something to work with at least

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/aspnet/EntityFramework/issues/1368#issuecomment-309841409,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AVf50pPR5MjE68rkz8dBKUb-wSr9DSPSks5sGArEgaJpZM4DPrA7
.

[sky-code]

I am thinking about rewrite entire project to django only because old asp.net mvc is dead but new asp.net core is not usable and will be same in next several years?
I already have existing project but I got stuck with legacy asp.net for unknown amount of time and I can't use new features but need them now and old asp.net doesn't have it.
I got in to a dead zone.
Only one option remains, rewrite all many-to-many relationship with join entity class and rewrite all complex types to properties with backend field and later re-write all this stuff back to normal.

[skendrot]

As much as it makes your life easier to have M-M support, take this time to start using an ORM like Dapper. Performance is so much better.

[slamotte]

Why not write an extension to EF Core to add this functionality? I don't think idle threats to move to another platform will sway anyone.

Myself, I just accept the limitations of the platform and code on. Nothing is perfect, and no one ever said that EF Core was backwards compatible with legacy code.

Don't get me wrong, this really bothers me too. But not enough to make me switch platforms.

[popcatalin81]

While I do use EF Core on my newest projects, with great benefits despite some functional limitations,, this is still the main issue preventing me to migrate an older large project to EF core, which I would really like to do.

[sethcall]

@skendrot Do you use a particular SQL Builder with Dapper?

[skendrot]

@sethcall I don't. I just fumble through and search online until I find the SQL that works for what I need.

[sven-n]

I wrote about a possible workaround in my blog post and I think it might be a good solution for some guys here.
To sum it up: My application just uses a basic data model (domain model). To use it with the entity framework I create (with the help of T4 templates) inherited classes which include all the join entities and other aspects regarding entity framework core. All this stuff is hidden from the actual application code.

[joehoeller]

To make a relationship (and for added flexibility) make the FK nullable (_int? means nullable_) . Just create your data models like this, and let EFCore do the rest. _Make sure you see my comment above about using the JSON serialization with Reference Loop Handling_. You can use .Include() and not .WithMany(). This is a SQL DB performance enhancement as well.

PARENT TABLE:

public class Parent { public Parent() { Dependents = new List<Dependent>(); } [DatabaseGenerated(DatabaseGeneratedOption.Identity)] [Key] public int Id { get; set; } public string ParentName { get; set; } public virtual List<Dependent> Dependents { get; set; } }

DEPENDENT TABLE:

public class Dependent
{
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
[Key]

    public int Id { get; set; }
    public string DependentName { get; set; }
    public int? ParentsId { get; set; }
    [ForeignKey("ParentsId")]

    public Parent Parents { get; set; }

}

}

[alojoshka]

Is there any approximate time schedule for implementing pure many-to-many support in EF Core (without join table as entity)? we are waiting for this feature to start porting all our projects to Ef Core.

[alojoshka]

I think many-to-many feature was always one of the weak points even of EF6. In NHibernate the implementation was much better - it just worked simply and stable without the need of difficult and critical updates of both connected lists (adding, deleting and modifying connected lists on any side caused needed operations in join table without the need to modify both collections before saving the context). Hopefully the Ef Core will have improved many-to-many scenario.

[mhosman]

+1, please implement Many To Many via shadow state entities!!!!

[alojoshka]

please implement Many To Many via shadow state entities!!!!

for us it means "forget about Ef Core for now". This solution adds a lot of shim to every m-m entity, and it is not implementable for business applications with hundreds of entities. Unfortunately.

[popcatalin81]

@rowanmiller @bricelam @ajcvickers Now that 2.0 is out, do you have any updates for us regarding this item? A roadmap update would be really helpful.

[fletchsod-developer]

If I were you, I wouldn't keep waiting for this feature (along with 1-to-many, 1-to-1, etc.) to be fixed and move on to thinking outside of the box. Because you will end up waiting 10 years later and still no changes.

We end up creating Sparse Matrix to store fields & values (like pebble pieces) as C# class properties. That is our database tables and we used C# linq objects to write script to manipulate the data ourselves. Since then we had made progress on our own without depending on incomplete EF Core package.

That is how we think outside of the box. You need to do the same as well because it will be a long time wait.

[alojoshka]

It sounds like writing own ORM. What is the task of Ef Core then, if it supports a lot of advanced features on many platforms, but doesn't support out of the box the most basic things - reading and saving data?
Many-to-many scenario is not just an abstraction, it's a model that describes business logic, and I can't understand the logic of Ef Core developer's team, when in 2017 I end up with writing my own sql queries or a tricky mess of code and helpers just to support the simplest every-day business scenario.

[fletchsod-developer]

Exactly! We're far ahead on the new "Cloud-based Web Application" now than we were 3 years ago. We now no longer have to deal with frequent JOIN bugs. We noticed EF Core's sql query & returned result aren't the same for every same call in JOIN. So we moved business logic outside of EF Core & instead passed in seperate data back to EF Core tables for CRUD changes individually. We will move the business logic back into EF Core when it works better & support features we need. Frustrating though. We also are having to deal with Angular 4 & TypeScript mess too.

[alojoshka]

It would be fare then to announce it publicly that the Ef Core will be cloud-based only and will never support many-to-many pattern in a classic way, and remove this topic from the list of issues, so developer can decide whether to change the entire philosophy of the database development, or to choose a more appropriate ORM and platform for their future projects. But this topic is still in "hight priority features" for future releases, like a teaser, making us to wait for years now, and you clearly say that we don't need this feature at all and it will not be implemented at all. Very frustrating.

[mhosman]

Something important... Many to many relationships works on EF Core. The only different thing is that you need to create a Class for the join table. This discussion is about removing the need of creating that class.

[alojoshka]

the problem is that this join entity always should be mentioned not only while saving the context, but for fetching data and building projections, and it is extremely difficult to migrate existing business logic to Ef Core, making it implementable only for the new applications, written from scratch. As it was mentioned many time in the discussion above, the lack of the possibility to migrate old applications just because of the delay in implementing this feature make a lot of companies to restrain from starting to use Ef Core generally. Starting to use not tested ORM for new projects is too risky while there is no practical possibility to port the existing applications to Ef Core and to test it in practical existing scenarios.

[KhalloufHassan]

@alojosgka
Did they really say they won't do it?
I was so hyped for asp core for a week and I watched all mva courses about it! I even convinced my manager to start our new project using it only to find out today this isn't supported and spatial data isn't supported either

I'm not sure we can start the project this way, how hard is it to use ef6 on asp core and move to ef core when the features are ready?

I know this will limit us to iis and windows only but it's not like starting on the old mvc will give us this feature anyways!

Or should I just start the project using mvc 5 amd forget about core, they said it's production ready, I didn't even start working and I found 2 deal braekers :(

[bassebaba]

I'm with @alojoshka here. I use the database first approach since I'm on legacy systems with existing/old databases.
Many-to-many gets really messy and I actually wish I would have explored other possibilities than ef core, but when I found out about this it was too late.

@KhalloufHassan please be cautious. "Release" and "production ready" does not mean the same thing for Microsoft as for the rest of us. Migrating a ~40 project solution has been the biggest pain I have experienced, ever in coding.
We have a class-file with "workarounds" for all things .net core that "doesnt work" in order to share solutions in the team, and that class is now over 5000 lines long... It must have been hunderds of hours of work to find and workaround all those problems....

I have no hope what so ever that this issue will be fixed, and my number one priority will be to try to replace EF core, but i havent had the time to research if there are any viable options for .net core.

[MikeFoden]

It's now open source, so feel free to contribute to help 'em out guys!

[popcatalin81]

@MikeFoden this particular feature is unfortunately out of reach of individual contributors as it touches almost all subsystems in non trivial ways: metadata, entity tracking, query translation, reverse engineering and more.

Which means the only chance to see it implemented is if the EF team decides to do it.

[leus]

What @popcatalin81 says, and further more, even if one individual decides to bite the bullet and implement it, chances are it won't be merged because of its scope. (I believe this actually happened.)

[alojoshka]

actually, to solve the problem in a positive way, the only thing is needed and highly awaited from the Ef Core team is to set the approximate date of the release which will implement this feature. Of course each developer team and business has it's own priorities and has the right to decide whether to wait or to look for and alternative solution. I am really tired to plan our next bunch of projects to move to Ef Core in some months, because m-m feature is in the priority list for Ef Core team, and after years not even planned. As I've understood now, this delay has some philosophical reasons as well, not only the lack of time of the developers (which we all suffer from time to time and can easily forgive to others). Now in October we planned the shift our main solution to Ef Core. It has some b2b and b2c hotel booking systems, air company booking and site etc. - the whole solution is almost 10GB(checked right now).
The shift was planned 1.5 years before! After long and deep analysis, the only feature which is the absolute stopper is this m-m - our DAL is quite plain, based on the pure POCO entities, but is too huge to change m-m relations to sth like "m-m with join entity". What should I write to our CEO? That in 1.5 years of keeping this feature in hight priority list it is not even planned for next months and it's developers try to convince me that I don't need it at all? Really, together with @bassebaba I put my next biggest priority to find the alternate product, which has its development team able to set its priorities according to the business needs and not only its imaginary icon of developer's needs.

[gavrilyuc]

What is the approximate date? I want to know, whether to wait or write your crutch.

[ntrpnr]

We need this.

[ajcvickers]

EF Team Triage: We really appreciate the recent feedback on this issue. Feedback from developers is extremely important in determining how resources across the team are assigned. However, please keep in mind that that feedback is only one of several inputs into the planning process and that--like in all software projects--resources are limited and every decision to implement a specific feature usually involves postponing others.

To shed a little more light onto our planning process we have created some documentation on the common considerations we take when deciding what to work on next.

We read and consider all feedback (and try to make the right choices based on it) no matter how it is delivered; that is, whether it is provided politely and with respect or not. Yelling at us at best makes you feel good and us feel bad. It doesn’t achieve anything concrete. We are doing our best to prioritize features and implement them in the best way we can. We really appreciate your feedback and it makes a big difference on shaping the product. We personally appreciate it more when feedback is delivered in a respectful way, but please don’t stop providing the feedback.

[pwen090]

@ajcvickers Thank you so much for the work you and others on the team do. I think simply some of us are just wondering, without anger or judgement, if there is any new guidance on this post 2.0 release? I think the last guidance from you was "It's currently on our backlog and not scheduled for any of the immediate upcoming releases."? Totally cool if that guidance still stands, just wondering! Either way thank you for everything.

[manigandham]

+1 for this.

We have a complex domain model which we are waiting to use EF Core with. Unfortunately the amount of extra work and complexity of join classes, along with the lack of lazy loading, has kept this from being viable for us.

[ajcvickers]

@pwen090 It's still on the backlog which means that it unfortunately doesn't fit for 2.1. We will consider again when planning for the release after that.

[BFPLima]

I recognize the MS effort and team work, but without this feature and lazy loading is very frustrating to me.

[cakirpro]

+1 for many-to-many relationship support without including an entity class for the join table.
+1 for lazy loading.
we are eagerly waiting for these features.
Thank you in advance for your effort.

[fangspeen]

I agree whole heartedly:

+1 for many-to-many relationship support without including an entity class for the join table.
+1 for lazy loading.
we are eagerly waiting for these features.
Thank you in advance for your effort.

[mxmissile]

https://lostechies.com/jimmybogard/2014/03/12/avoid-many-to-many-mappings-in-orms/

[slamotte]

That's an excellent read, @mxmissile, thanks for sharing.

The comment "A junction entity is a normal entity waiting to happen" really resonates with me as something I find important more often than not is recording _when_ the association was created, or _who_ created it, etc. Without a join table there's no way to store that. And in reality there are often other attributes that need to be associated with the join, not just time/user-stamp metadata.

Not that this precludes the desire to have join-less many to many, but it makes you wonder if maybe explicit join tables should be considered.

[ajcvickers]

@mxmissile @slamotte The way we plan to implement many-to-many in EF Core should mitigate some of these issues. Essentially, the entity type for the join table will still exist in the model, it will just be created by convention and in "shadow state" so that there does not need to be a CLR type for it. There would then be skip-level navigation properties that jump over the join table entity from one many side to the other.

But because it is still just an entity type it means that:

• Other properties can be added to it, even while keeping it in shadow state
• It can easily be promoted to an entity type with a CLR type backing it without fundamentally changing the model
• Even after promotion to a "real" entity type, the skip-level navigation properties will still work, so parts of the code that don't need to know about the join table still don't have to know.

So the idea is that people can start with simple many-to-many relationships handled by EF, but then can grow-up to explicit mapping of the join table with additional payload, etc. when needed.

[cakirpro]

@mxmissile Thank you for the link. It is interesting and some arguments are fair enough. However when you consider a model where you have a relatively many number of entities and complex relationships (like an entity having m-m relationship with multiple entities), it becomes very complex to model "avoiding many to many relationships" and having to create extra entities.

Therefore I like the approach @ajcvickers explained because you need both approaches. While sometimes it is enough to let EF handle the mapping, on the other hand you sometimes need extra properties (like sorting) and would be nice to have the option to assign to entities.

[bassebaba]

@mxmissile you are very welcome to come and:

1. Change all my legacy databases
2. Fix all other software that already uses those legacy databases

Do you guys know how many times/projects during my years with EF have used code first? 0.
Database first? 100%.
Code first is a luxury for "privileged" developers. All coding I have to do is against already existing databases.

Many-to-many is out there. Everywhere. In legacy systems. No number of blog posts will change that.
Heck, if I got to choose, I'd probably kick out mssql altogether and go for NoSQL or something else cool that the kids use nowdays :P

This is why I'm so grumpy. I'm stuck and I really need:

1. A good m-2-m implementation
2. Lazy loading
3. A flawless reverse-engineer (database first) tool.

@ajcvickers okay, but how will this work in my scenario, database first, legacy systems?

[mxmissile]

@bassebaba honestly I feel you. I am working with a DB built in 1997, would I use EF with it? Never! NHibernate does everything (and more) I need when working with a legacy DB. Greenfield though? EF Core all the way!

I would not think @jbogard's article I posted to be "bible". It only offers some suggested points to think about when dealing with many-to-many scenarios.

[ajcvickers]

FYI, I just published some blog posts that show how to use and hide the join table when using many-to-many relationships in EF Core 2.0. The approach as a couple of significant limitations, but it may be useful to people until this feature is implemented. See at https://blog.oneunicorn.com/

[HappyNomad]

Once this feature and #8881 are both implemented then, given a many-to-many relationship between Product and Org, will the following work?

modelBuilder.Entity<Product>().HasQueryFilter( p =>
    EF.Property<IEnumerable<Org>>( p, "PurchasedBy" ).Any( o => o.ID == TenantID )
);

I'll need to access a many-to-many shadow property like this in my scenario. I'm guessing that the "skip-level navigation properties" that @bricelam mentioned will allow me to navigate straight to Product, _skipping_ over the shadow join entity.

[ajcvickers]

@HappyNomad That seems correct with regard to skip-level navigation properties. @anpete would be better able to answer the filter question.

[anpete]

Seems reasonable 😁

[mhosman]

There is some time estimation to have this working?

[khionu]

Read up, there's none, sadly. This thread is filled with discussion about why this is a backlog feature, despite it seeming to be a needed key feature.

[jbogard]

@mxmissile nothing I write should be a bible for anything.

I do know I've regretted every time I've used a "many-to-many" hiding feature in ORMs, from NHibernate to EF6. But I wouldn't say every time this feature has been used by an EF6 user has been a regret. Just the times I've used it on the systems I've built.

[mxmissile]

I know for sure. But the pros do outweigh the cons in this case. IMO of
course.

On Fri, Oct 20, 2017 at 12:54 PM Jimmy Bogard [e-mail]
wrote:

@mxmissile https://github.com/mxmissile nothing I write should be a
bible for anything.

I do know I've regretted every time I've used a "many-to-many" hiding
feature in ORMs, from NHibernate to EF6. But I wouldn't say every time this
feature has been used by an EF6 user has been a regret. Just the times I've
used it on the systems I've built.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/aspnet/EntityFrameworkCore/issues/1368#issuecomment-338293380,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AAOWiMJzUFQJcVVeifX9xqMlQqXIWjkRks5suOxrgaJpZM4DPrA7
.

[joehoeller]

This guy has a post on how to maintain relationships in many to manys with rest routes in EF core 2.0, but I havent been able to make this work in a real app with async controllers handling rest routes. Any guidance is appreciated: https://blog.oneunicorn.com/2017/09/25/many-to-many-relationships-in-ef-core-2-0-part-1-the-basics/

[HappyNomad]

Thanks @ajcvickers and @anpete for your replies to my question above at https://github.com/aspnet/EntityFrameworkCore/issues/1368#issuecomment-338265306. As a follow-up, will the following also work? It involves an additional many-to-many that's between Product and AuxiliaryProduct.

modelBuilder.Entity<AuxiliaryProduct>().HasQueryFilter( a =>
    EF.Property<IEnumerable<Product>>( a, "Products" )
        .Any( p => EF.Property<IEnumerable<Org>>( p, "PurchasedBy" ).Any( o => o.ID == TenantID ) )
);

And how about https://github.com/aspnet/EntityFrameworkCore/issues/3864#issuecomment-342752182?

[ajcvickers]

@HappyNomad Looks reasonable to me, but I may be missing something.

[dougajmcdonald]

Ouch, just came across this issue and now have to re-write much of our data access code to handle the new type of joining entity on either side of the relationship. Significant issue :(

Asking for an explicit joining entity is fine, but having to name it add an ICollection<EntityAEntityB> on both sides is a killer

[gregbowyer84]

oooh! deal breaker... any timeline for the implementation?

[khionu]

@gregbowyer84 read the whole thread, please.

[HrDahl]

Until this enhancement is implemented we are all forced to use a join entity class which is frustrating and a tedious task. I will defently look forward to the day they prioritize this feature. But based on the creation date of this thread, I am not sure we'll ever get it.

[wandelson]

Pleaseeeeeeeeeeeeee ...

[jaredcnance]

Can we please lock this issue. I think it is well established ☝️ that the community wants this feature. Further comments to that effect only serve to create noise for users who are subscribed to this issue and are waiting for a resolution.

[jfevia]

This link does not provide an acceptable solution. It's just a workaround for a key feature in ORM's. EF6 supports this out of the box so I don't understand why EF Core doesn't.

When one provides a better solution, all previous features are included and you build on top of those.

These features are high priority but we think EF Core would be a compelling release for the vast majority of applications without them
Source: Roadmap

Your "thinking" is wrong. This is a setback.

[sky-code]

this thread started in 2015, funny thing that ms needs 3 years for implementing this feature which exists in previous version of EF (of course if ms implement this feature in 2018)

[mxmissile]

Instead of all the gnashing of teeth, maybe submit a PR?

[ptMuta]

@Travis Shush. That would require work and understanding of the underlying
mechanics from them. They're already using most of their capacity crying
like little babies.

On Thu, Dec 7, 2017, 16:21 Travis [e-mail] wrote:

Instead of all the gnashing of teeth, maybe submit a PR?

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
https://github.com/aspnet/EntityFrameworkCore/issues/1368#issuecomment-349980526,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AFLY27ZSnqzvjqMQ5AEerivaCHW0HQXEks5s9_RzgaJpZM4DPrA7
.

[jfevia]

Yeah, I'll submit a PR, so the project is then neglected and replaced by whatever fancy new name MS come up with. Now, where have I seen this before...

No worries though, I'll be back to EF6 and come back to EF Core when this project is taken seriously for productivity. Maybe in another 3 years.

Good luck :)

[khionu]

I'm bumping the call for this thread to be locked until the EF team actually gets around to addressing this issue.

To summarize this thread for everyone who is not reading the entirety (omitting the salt):

• Adding Many2Many would require a massive PR changing fundamentals of the library
  
  
  
  • Said changes need to be done with extensive knowledge of the inner workings. Ergo, this is almost guaranteed to be an in-team task
  
  
• The EF team definitely plans on doing this, eventually. No ETA
• The current workaround (which is agreed to have it's benefits in a schema, but messiness in the ORM usage) is to have a Join Table Entity.

It would have been nice for this to go unsaid, but this is a public thread of professional context. Making a comment just to display the fact that you're unhappy with the status of this issue is redundant and a waste of your productivity. The EF team is well aware of this issue, and the comments over the last three years make it clear that they need to do it. The ball is in their court.

[ajcvickers]

Today we are taking two actions based on the extent and nature of the discussion here:

• Issue #10508 has been created to track actual work on many-to-many relationships. The new issue is locked so that it can be subscribed to for updates on the implementation without noise.
• This older issue has been re-purposed to be discussion about many-to-many relationships in EF Core. It will not (for the time being) be locked because it is important to us that we don’t shut down channels of communication with the community.

With regard to feedback, I think it is worth reiterating some comments made a few months ago. We read and consider all feedback (and try to make the right choices based on it) no matter how it is delivered; that is, whether it is provided politely and with respect or not. Yelling at us or others with different opinions at best makes you feel good and us/them feel bad. It doesn’t achieve anything concrete. We are doing our best to prioritize features and implement them in the best way we can. We really appreciate your feedback and it makes a big difference on shaping the product. We personally appreciate it more when feedback is delivered in a respectful way, but please don’t stop providing constructive feedback.

[serafimprozorov]

Hi guys,

I don't know what do you think about many-to-many support, but I think that it is epic fail to have no many-to-many support in 2018. Also it is strange that you can't implement it during THREE years. I have read a lot of internet resources and I still can't understand two things: will this feature be implemented someday and if the answer is positive when will it be implemented. Should I use EF Core and wait implementation of this feature or I have to use another ORM 'cause you have no plans or interest in implementing it?

[jbogard]

How is the lack of this feature affecting your ability to deliver software
to your end users or customer? Genuinely curious since I intentionally
avoid this feature in other ORMs for explicit join classes.

On Fri, Jan 26, 2018 at 6:40 AM Serafim Prozorov [e-mail]
wrote:

Hi guys,

I don't know what do you think about many-to-many support, but I think
that it is epic fail to have no many-to-many support in 2018. Also it is
strange that you can't implement it during THREE years. I have read a lot
of internet resources and I still can't understand two things: will this
feature be implemented someday and if the answer is positive when will it
be implemented. Should I use EF Core and wait implementation of this
feature or I have to use another ORM 'cause you have no plans or interest
in implementing it?

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/aspnet/EntityFrameworkCore/issues/1368#issuecomment-360774563,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AAGYMtfdIBpGTb3t-rrquFMuWzRkaf54ks5tOcfJgaJpZM4DPrA7
.

[serafimprozorov]

@jbogard It is not affecting me or my team now, we are just selecting the ORM framework for new version of our software. As for supporting this feature in ORMs, NHibernate has normal many-to-many support, as far as I know Linq2DB also has it. And suddenly... the old Entity Framework (6-) also has this feature. I don't understand why m-to-m without additional entity suddenly became a bad idea. So if the vendor's new products have less functionality and stability than it's older libraries and frameworks, if it can't develop new features and fix lacks, if it can't to keep it's own promises to community if it breaks up the library public API, if it can't implement basic features in the normal way (for example -- group by) I prefer to use other library from other vendor. For example if I have the project depends on the EF I have to rewrite my data model if I want to use EF Core, I can't just change the library and reimplement factories. All these things make me sad.

[bassebaba]

@jbogard As I have pointed out before, lots of us are stuck with old legacy databases with existing data where M2M is already present, therefore we have no other choice but to keep using M2M.

[jbogard]

Right, so you can model that today with a join class, which is my default
in any ORM.

On Fri, Jan 26, 2018 at 7:36 AM bassebaba [e-mail] wrote:

@jbogard https://github.com/jbogard As I have pointed out, lots of us
are stuck with old legacy databases with existing data where M2M is already
present, therefore we have no other choice but to keep using M2M.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/aspnet/EntityFrameworkCore/issues/1368#issuecomment-360786201,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AAGYMj6Z6CgnvbDd2L1VcgGw21lEttVkks5tOdT4gaJpZM4DPrA7
.

[danroot]

@jbogard Lack of this feature has affected three projects of mine in the past few months. In each case, we wanted tagging. Instead of having just MainModel and Tag classes, we have to have MainModel, MainModelTag, and Tag classes. Anywhere that uses Tags then also needs to know about MainModelTag. In some cases, the Tag class needs to be shared with other models, so that's +1 class for each scenario, and +N mapping logic anywhere Tags are added/removed. It also affects JSON serialization, so we've had to do ViewModel mapping in places it might not otherwise be needed. I am working on some extension methods that hide some of this, but it _is_ extra code and logic I would not need otherwise. As far as real business impact, just this week, a lack of automatic M2M in EF Core added about ~8 hours and ~150LOC in one project. So I wouldn't say it's stopping me from adopting EF Core completely, but it is a hindrance and does have an impact for us.

[bfsmithATL]

I would love to have this feature, it has affected a few design aspects on a project I'm working on. I've had to include five different models (& respective tables) to represent the join entities. Not the end of the world though. I'd like to see more simplicity here, but I've been able to easily work around the many:many shortfall in EF Core. There are plenty of blog articles and illustrations on how to achieve this. Using Automapper and DTOs I'm easily able to hide the "artifacts" of these join entities from models that are in transit to & from front-end clients. I also learned (later on) there is now a feature in EF Core you can use to hide properties of EF-tracked models so you can suppress these artifacts in this way without using Automapper and DTOs.

Now, does this really stink from an architectural and new-app-greenfield-dev perspective? Yes, certainly. But the gains are elsewhere. The benefits gained by sticking with EF Core (and .NET Core) is ensuring future ease of portability of the application to any hosting platform. This is huge. Usage of EF Core also ensures that the community stays strong and enough development resources stay allocated to keep it alive. You must always remember the nature of open source development. It's not "free." Everything costs money somewhere along in the line in the dev lifecycle, in the form of cash or time. The EF Core team has direction and priorities and those all must be balanced and evaluated. There is a reason this feature has not been implemented yet. I'd love to know the details as to why, just out of curiosity. We can easily petition the team in more effective ways. Nothing is stopping us community members from pulling together a gofundme campaign in the spirit of this feature and gifting a donation to the team to fund additional dev resources to work on this feature, if that is in fact the inhibitor to getting this feature. :-) Any takers? :-D

[popcatalin81]

I've worked on an EF6 project where the amount of many-to-many relations compared to all others was insanely high.

Examples of M:N Relations, NOT from the real Project, just the idea: Product<->Categories, Product<->Tags, Product<->Features, Tag<->Clasiffications, Category<->Clasiffications, Product<->Classifications, Classification<->Pricings, Category<->Pricings, Product<->Pricings ... (and it keept going for 100+ of these).

The queries were highly complex involving lot's of intersections and unions, with dozens to hundreds of lines. I don't even want to imagine how the queries would look like with 100+more join tables in the model. I haven't worked on that project for years now, but lack of many-to-many would have basically meant a product rewrite, no other way to migrate from EF6 to EF Core.

For most projects lack of many-to-many is nothing more than an annoyance, for others, it's a must-have feature. I wouldn't tackle a similar project today without using an ORM with many-to-many support.

[neridonk]

to sum everything up it costs more time due the increase of complexitity and code coverage etc.
around 1/8 +.
I appericiate all your work so far and I know in america is a inflation incoming , but can you save our time pls a little bit.

[mxmissile]

america is a inflation incoming

Now we are using politics to get this through? lol

[EmreAbi]

Allah belanizi versin MS.

[Mezzenine]

Working with many-to-many relations in Asp.Net Core 2 with entity framework core and odata v4 support is not really straightforward. Currently, you have to expose the CLR type of the join table to access the related data. In my current project, there are lots of them.
In my opinion, this should be one of the top features in the ef core project. It will support one of the most common usecase.

Example:

```c#
public class Computer
{
public Guid ComputerID { get; set; }
public string DisplayName { get; set; }
public virtual ICollection DevicesComputers { get; set; } = new HashSet();
public virtual ICollection Devices { get; set; } = new HashSet(); //<--This will cause an sql error, because it adds a ComputerID column to the device table
}

public class Device
{

public Guid DeviceID { get; set; }
public string HardwareID { get; set; }
public string DisplayName { get; set; }
public virtual ICollection<DevicesComputers> DevicesComputers { get; set; } = new HashSet<DevicesComputers>();
public virtual ICollection<Computer> Computers { get; set; } //<-- this cause an sql error too. The same reason.

}
public class DevicesComputers
{
public Guid ComputerID { get; set; }
public virtual Computer Computer { get; set; }
public Guid DeviceID { get; set; }
public virtual Device Device { get; set; }
}
public class ComputersController : ODataController
{
private ComputerRepository _computerRepository;
public ComputersController(ComputerRepository repository) => _computerRepository = repository;

[EnableQuery]
public IActionResult Get()
{
    return Ok(_computerRepository.Computers);
}
/*
The code below is not working, because of lack supporting many-to-many relations.
 */
[ODataRoute("Computers({computerId})/Devices")] **//<-- if I remove the Devices property from the computer class, to avoid the sql error, this becomes a invalid odata route.**
public IActionResult GetDevices(System.Guid computerId)
{
    var comp = _computerRepository.Computers.Single(c => c.ComputerID == computerId);
    if (comp == null)
    {
        return NotFound;
    }
    return Ok(comp.Devices);
}

/*
The code below is working, by exposing the clr type of the join table. 

The consumer of the api have to use http://localhost/api/Computers(1)/DevicesComputer?$expand=Device to access the devices of a computer 
instead of http://localhost/api/Computers(1)/Devices. Which is more understandable, in my opinion.
 */
[ODataRoute("Computers({computerId})/DevicesComputers")]
public IActionResult GetDevices(System.Guid computerId)
{

    var comp = _computerRepository.Computers.Include(c => c.DevicesComputers).ThenInclude(dc => dc.Device)
        .Single(c => c.ComputerID == computerId);

    if (comp == null)
    {
        return NotFound;
    }

    return Ok(comp.DevicesComputers);
}

}
```

[alojoshka]

Fortunately the NHibernate, which has brilliant many-to-many support (much better than EF6), got now Net .Core support, async queries etc., As I understand now, it was a great idea to move to NHibernate + Fluent NHibernate + SysCache2 some months ago. It's really pity after 7 years of everyday using of Entity Framefork for huge projects (starting from Linq2Sql), but it was really the solution for projects, which can't wait for years. Even a small EntityWorker.Core has now many-to-many support, but of course the NH is much more mature.

[jeffguillaume]

Holy crow, it's been over 3 years and no movement on this??

Who can give an authoritative answer re: the _current status_ of this issue?

Is there opposition? Roadblocks? What?

1. Will this EVER be implemented?
2. If so, when?

I have been a fan of EF since the beginning and witnessed it grow more advanced and mature over the years. EF Core, at least in this case, feels like a huge step backwards.

--OR--

Should I model my data differently? How else can you model a many-to-many relationship? In my situation, I have Articles and Topics which need to be linked together. An Article can have many Topics, and a Topic can have many Articles in it... Am I missing something?

[jbogard]

Well of course a feature not present in the rewrite will seem like a step
backwards. But at least in this feature’s case, there are some rather
simple workarounds. Just create an entity for the many to many table. I’ve
found that’s a better way to model in the long run anyway.

On Mon, Apr 30, 2018 at 1:48 AM Jeff Guillaume [e-mail]
wrote:

Holy crow, it's been over 3 years and no movement on this??

Who can give an authoritative answer re: the current status of this
issue?

Is there opposition? Roadblocks? What?

1. Will this EVER be implemented?
2. If so, when?

I have been a fan of EF since the beginning and witnessed it grow more
advanced and mature over the years. EF Core, at least in this regard, feels
like a huge step backwards.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/aspnet/EntityFrameworkCore/issues/1368#issuecomment-385323321,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AAGYMg_auYsOgS4YMUZUO7L6hmLHHMNnks5ttrOogaJpZM4DPrA7
.

[hirre]

No @jbogard, it’s hard to create dynamic REST APIs with e.g. OData - ASP.NET Core - EF that conceptually make sense from the user perpective. You will have to create a lot of custom logic as workarounds to get it right, just not worth it... Lack of many-to-many relationships that hide the mapping table is a showstopper imho, especially if you want to do dynamic/flexible REST APIs...

[Kralizek]

I'm sorry @jbogard but the workaround is at the end just a (terrible) workaround that asks for worse workarounds.

"An entity shouldn't exists if it hasn't a name"

That's DDD's way of explaining the idea of "raison d' être".

Allowing for a class that represents a table because of the tool's fallacy, forces us to limit those classes in the storage layer (something like a low-level domain).

The domain will have then its own set of "clean" entities, possibly mapped to the EF ones via AutoMapper or similar.

This works ok, and somehow forces you to think better in terms of your domain, but not all applications need to have two domain tiers.

And EFC definitely shouldnt be the judge of it.

[ajcvickers]

@jeffguillaume See these comments in the thread: https://github.com/aspnet/EntityFrameworkCore/issues/1368#issuecomment-326054060, https://github.com/aspnet/EntityFrameworkCore/issues/1368#issuecomment-328990491, https://github.com/aspnet/EntityFrameworkCore/issues/1368#issuecomment-331945903, https://github.com/aspnet/EntityFrameworkCore/issues/1368#issuecomment-350121796

[cristiangiagante]

I hear some rumors about this will take in place after EF Core 2.1 release.

I will really appreciate any who can recommend me a book where I can found how to implement many-to-many relationships with a nice and transparent navigation using dto objects and mapping.

[tural-esger]

When are you planning to solve this problem? Please do it as soon as possible.

[embryologist]

+1, when the feature is coming out. Will culminate the beauty of EF core

[bugproof]

This is really important feature. I hope this will be implemented after 2.1

[filipbekic01]

pls

[tural-esger]

pls

[amrnaveen]

Waiting!

[FrankDupree]

I don't think I want to wait around for this feature :D. in the mean time can someone please be so kind to point me in the right direction.

How do I model the following, A user has many followers and follows many users. The same user has many blocked users. (Twitter kinda feature)

public class ApplicationUser : IdentityUser
    {
        public virtual ICollection<UserToUser> Following { get; set; }
        public virtual ICollection<UserToUser> Followers { get; set; }
        public virtual ICollection<UserToUser> BlockedUsers { get; set; }

    }

    public class UserToUser
    {
        public ApplicationUser User { get; set; }
        public string UserId { get; set; }
        public ApplicationUser Follower { get; set; }
        public string FollowerId { get; set; }

    }

My implementation so far.

 public void Configure(EntityTypeBuilder<UserToUser> builder)
        {
            builder.HasKey(k => new { k.UserId, k.FollowerId });

            builder
                .HasOne(l => l.User)
                .WithMany(a => a.Followers)
                .HasForeignKey(l => l.UserId);

            builder
                .HasOne(l => l.Follower)
                .WithMany(a => a.Following)
                .HasForeignKey(l => l.FollowerId);
        }

[jbogard]

Just map the many to many tables directly as first class citizens of your
model. That’s what we always do, it works just fine.

On Tue, May 29, 2018 at 5:17 AM Frank Akogun [e-mail]
wrote:

I don't think I want to wait around for this feature :D. in the mean time
can someone please be so kind to point me in the right direction.

How do I model the following, A user has many followers and follows many
users. The same user has many blocked users. (Twitter kinda feature)

public class ApplicationUser : IdentityUser
{
public virtual ICollection Following { get; set; }
public virtual ICollection Followers { get; set; }
public virtual ICollection BlockedUsers { get; set; }

}

public class UserToUser
{
    public ApplicationUser User { get; set; }
    public string UserId { get; set; }
    public ApplicationUser Follower { get; set; }
    public string FollowerId { get; set; }

}

My implementation so far.

public void Configure(EntityTypeBuilder builder)
{
builder.HasKey(k => new { k.UserId, k.FollowerId });

        builder
            .HasOne(l => l.User)
            .WithMany(a => a.Followers)
            .HasForeignKey(l => l.UserId);

        builder
            .HasOne(l => l.Follower)
            .WithMany(a => a.Following)
            .HasForeignKey(l => l.FollowerId);
    }

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/aspnet/EntityFrameworkCore/issues/1368#issuecomment-392726470,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AAGYMopwFhWI8wKJsPYTap745qSifM5Rks5t3SAfgaJpZM4DPrA7
.

[cristiangiagante]

I think that approach is not the best one if you want to work mapping your data to DTOs

[jbogard]

@cristiangiagante explicitly mapping? We've done it that way for like....7 years now? Even with DTOs, even with DDD-style-aggregates, it really is fine.

[FrankDupree]

@cristiangiagante and @jbogard I'm being advised to create another class on StackOverFlow. I'm kinda confused now. Was tempted to post more codes here, but I think StackOverFlow fits the bill. Can you guys help wrap this up.

[raffaeler]

Is there a UserVoice link for this? It is really preventing the migration from EF6 to EFCore.
Touching the model is out of discussion for big projects already deployed and massively used from customers.

[dannevesdantas]

That's bad. We are in mid 2018 and must create a class for EVERY SINGLE many-to many relationship. It generates a lot of unnecessary code, makes the maintenance harder. What happened to the Convention over Configuration concept??? Things are supposed to improve, not going backwards :( It was beautiful on EF6 :) Everybody is abandoning EF and diving into Dapper, Nhibernate and other ORMs :( I think it should be the 1st priority for 3.0

[Tyology]

I would also like to see this feature implemented in the near future. It is becoming a major headache with all the many to many relationships we have in our projects to manage these join classes. I understand that the workaround works (writing raw sql would "work" too) but we want to use EF Core for convenience and speed to build production apps.

[scionwest]

Everybody is abandoning EF and diving into Dapper

I've used Dapper for years and to be honest, the amount of SQL I save having to write, debug and maintain by going EF Core has me happily building many-to-many linking classes. They take 30 seconds to create and I'm off using LINQ for the query again. My data layer is so much cleaner without using Dapper (and I _really_ love Dapper) now.

I get that it's a pain, but really, with the macros in VS to quickly create properties, it takes me less than a minute even on my really complex many-to-many scenarios. Easier to maintain because I can use VS refactor tools vs dealing with embedded SQL not being compiled/tested until run time. Or even more annoying, writing the query in SSMS, testing, then moving into VS. Way more convoluted workflow.

[maxim-shaw]

@scionwest Off topic, though many-to-many with EF doesn't look efficient.
e.g.:

IEnumerable<Application> applications = _dbContext.Applications
                .Where(x => x.ApplicationRegions.Any(r => r.RegionId == regionId));

For this EF builds quite inefficient query with select in the where clause. Correct me if I'm wrong.

[kevdever]

I'm torn about this topic. On the one hand, the more an ORM does for us, the further removed we become from the database structure, which has pitfalls re optimization (storage, query, etc) or even just knowing what's possible at a low level. With respect to EF standard, I didn't like how it handled Many-to-Many; it wasn't really extensible (e.g., you couldn't add metadata to the join table without going against the grain). Thus, I actually like keeping MM manual: it's more work, but everything is kept explicit, and your models more closely relate to the structure of the database.

On the other hand, manual MM tends to be tedious and repetitive, and queries involve more steps, making a ripe target for abstraction. All that said, abstracting MM properly in a manner that leaves it extensible has a risk of creating an API that's more confusing for the dev than just going at it manually.

[msx752]

so now, what i need to do instad of using many to many relationship on .net core ?
should i stop to use .net core,

[maxim-shaw]

@msx752 you need to use linking entity as you would use with EF6 if your linking entity has any extra columns other than foreign keys.

[Cowlephant]

Yep, create that join table entity. Been working just fine for me.

// PlatformUserRole Join Table
            modelBuilder.Entity<PlatformUserRole>()
                .ToTable("Identity.UserRoles")
                .HasKey(p => new { p.PlatformUserId, p.PlatformRoleId });

            modelBuilder.Entity<PlatformUserRole>()
                .HasOne(pu => pu.PlatformRole)
                .WithMany(r => r.PlatformUserRoles)
                .HasForeignKey(pu => pu.PlatformRoleId);

            modelBuilder.Entity<PlatformUserRole>()
                .HasOne(pu => pu.PlatformUser)
                .WithMany(u => u.PlatformUserRoles)
                .HasForeignKey(pu => pu.PlatformUserId);

[akram1905]

WO.oW, more than 3½ years and still in backlog!

[filipbekic01]

Come on .NET, you can do it.

[bfsmithATL]

LOL, we're keeping this feature thread nice 'n warm. C'mon big #1368 ! :-D

[ytzhakov]

This issue has been keeping me from moving to .NET core for the past year now.
Pretty shameful it's still an issue.

[scionwest]

There’s a work around so you can do many-to-many. It works great, just a little convoluted. I’d rather the team work on features that are missing with no work around, over features that are missing but can still be achieved using a work-around.

[msx752]

same code different at .net core EF nearly 13sec slower than .net 4.5 EF on my project, @Cowlephant your method is not efficient, :/

[jeffguillaume]

I've come around on this issue. It's still a major PITA compared to non-Core, but as @scionwest pointed out, there are workarounds. I, too, came in here guns-a-blazing when I found out this functionality was missing, but after a lot of reading and internal fighting, I've decided to let it rest for now.

[zete]

Suffering same issue here.
Let's hope EF Team fix this it soon.

[orassr]

Is there any way to separate the configurations to a different file outside the DbContext?

[scionwest]

@orassr Yes; not sure what that has to do with this conversation though. Sounds like you need to do a bit of googling.

The IEntityTypeConfiguration<TEntity> interface will get you what you want.

public class UserConfiguration : IEntityTypeConfiguration<User>
{
    public void Configure(EntityTypeBuilder<User> builder)
    {
        builder.Property(entity => entity.FirstName).HasMaxLength(50);
    }
}

public class BusinessContext : DbContext
{
    public BusinessContext(DbContextOptions options) : base(options) {}

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.ApplyConfiguration(new UserConfiguration());
    }
}

[ghost]

EF Core Team please fix this!

[anatalin]

Waiting for many-to-many in EF Core! Come on, guys, you're cool, you can do it!

[thombrink]

Maybe someone can do something with my solution: https://github.com/aspnet/EntityFrameworkCore/issues/11892

If someone is interested I will update the sample with my current implementation (and how to get it working). The sample only works with an in memory database, my current implementation also works with sql databases (and is also simplified).

[vsopko]

@thombrink
Your implementation (and this also) wouldn't work with queries like this:

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    private ICollection<PostTag> PostTags { get; } = new List<PostTag>();

    [NotMapped]
    public IEnumerable<Tag> Tags => PostTags.Select(e => e.Tag);
}

public class Tag
{
    public int TagId { get; set; }
    public string Text { get; set; }
    private ICollection<PostTag> PostTags { get; } = new List<PostTag>();

    [NotMapped]
    public IEnumerable<Post> Posts => PostTags.Select(e => e.Post);
}

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

var query = _context.Post.Select(x => new Post
{
    Title = x.Title,
    Tags = x.Tags.Select(y => new Tag
    {
        Text = y.Text
    })
});

If you map Post.Tags as navigation to Tag entity it would load mapped navigation instead of joined entity, and if you make it unmapped it wouldn't load anything because PostTags not exists in query (look at Ignored includes)

[eduherminio]

@thombrink Isn't ThenInclude() 2.1 feature enough for your purpose?

[raffaeler]

@eduherminio I can say for at least two customers, the answer is no because the model is public and cannot be changed anymore.
I developed a solution to the problem for a customer but I can't publish the code without an explicit permission. The problem with this solution is that is difficult to integrate into existing code and even if the problem is apparently solved, it cannot be applied to the current codebase in the short term.

The lesson learned is that migration to EFCore can be extremely hard and will prevent a lot of apps to be migrated.

[thombrink]

@vsopko

In which case would you use the example below?

var query = _context.Post.Select(x => new Post
{
    Title = x.Title,
    Tags = x.Tags.Select(y => new Tag
    {
        Text = y.Text
    })
});

The example below would create a new IEnumerable< string > which, in my opinion, would be the thing you would want.

var post = context.Posts.First();
var postTitles = post.Tags.Select(x => x.Title);

[thombrink]

@eduherminio

In short, that's exactly what my new Include extension method does.
Instead of writing post.Include(x => x.PostTags).ThenInclude(x => x.Tag) I can now use post.Include(x => x.Tags) which is much cleaner in my opinion.

[vsopko]

@thombrink

In which case would you use the example below?

In order to get a list of entities with many-to-many children in two sql queries with only the necessary partial data. Is it possible with your implementation? Have you any repo for testing if it's working?

[thombrink]

@vsopko

Currently it needs two 'full' queries. I'm working on a more efficient one.
In order to get your code working I had to add AsEnumerable, which is the reason for the inefficient queries.

I looks like this right now:

var posts = context.Posts.Include(x => x.Tags).AsEnumerable().Select(x => new DTO.Post
{
    Title = x.Title,
    Tags = x.Tags.Select(y => new DTO.Tag
    {
        Level = y.Level
    })
});

This generates the following queries:

SELECT [p].[Id], [p].[Title]
FROM [Posts] AS [p]
ORDER BY [p].[Id]


SELECT [p.Tags].[PostId], [p.Tags].[TagId], [p.Tag].[Id], [p.Tag].[Level]
FROM [PostTags] AS [p.Tags]
INNER JOIN [Tags] AS [p.Tag] ON [p.Tags].[TagId] = [p.Tag].[Id]
INNER JOIN (
    SELECT [p0].[Id]
    FROM [Posts] AS [p0]
) AS [t] ON [p.Tags].[PostId] = [t].[Id]
ORDER BY [t].[Id]

The code is currently hosted on VSTS/Azure DevOps, I could make it public or move it to github.

[vsopko]

@thombrink
Well, you just load all from DB and process in memory, imo it's better to use existing ef many-to-many mappings instead of such overload

The code is currently hosted on VSTS/Azure DevOps, I could make it public or move it to github.

It would be great to continue on github, to not clog this branch.

[thombrink]

@vsopko Seems like a good idea to me.

I have pushed my code to https://github.com/thombrink/EntityFrameworkCore
The code is (at this time) not meant for production environments because not all methods are implemented and/or tested!

Anyone else who is interested, just create an issue or pr :)

[neridonk]

can we fund this wish? Our economy is about to get a big shock, time is running help

[ginomessmer]

+1

[knyzorg]

Can we stop the +1? They don't add anything to the conversation.

[maxisam]

@knyzorg I can't believe Github haven't developed a way to prevent people to comment stuff like that. Stackoverflow already ban this kinda comment.

[akram1905]

@knyzorg, To generalize:

• +1 doesn't add anything.
• Assuming +N does not add anything => +(N+1) = +N +1 => +1 doesn't add anything and also +N doesn't add anything, Then +(N+1) doesn't add anything

Proof by induction!

KeepItAlive

Sorry
Time to take Ritalin.

[slamotte]

-1 😁

On Mon., Oct. 29, 2018, 6:01 p.m. akram1905, [e-mail]
wrote:

@knyzorg https://github.com/knyzorg, To generalize:

• +1 doesn't add anything.
• Assuming +N does not add anything => +(N+1) = +N +1 => +1 doesn't
  add anything and also +N doesn't add anything, Then +(N+1) doesn't add
  anything

Proof by induction!

KeepItAlive

Sorry
Time to take Ritalin.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/aspnet/EntityFrameworkCore/issues/1368#issuecomment-434112218,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AAPnOEPLckRgJBJCnwOorGSHBmHzJZLcks5up4jRgaJpZM4DPrA7
.

[chaim1221]

Join tables! Join tables EVERYWHERE!

[Schuttrim]

AHHHHH AHHHHHH!
Here's Joinny!!!
cuts
Tables!

[Kralizek]

Let's try not having this issue closed for spam please!

[jacobbutton]

@Kralizek agreed, but I can understand developers frustrations of not having proper many-to-many relationships after nearly 4 years and counting with no discernible progress towards it.

[nmocruz]

@jacobbutton agreed, To me was killing reason to not move to ef core, I dont want o reactor all me code to make it work as It is now, and refactoring back in the future. It should be there from the beginning.
Some thing on the core framework was go back and make people wait ages for old stuff.

[dannevesdantas]

I will love to be able to map many-to-many without a class for join table, like EF6! I'm looking forward to it! But as a developer I also understand that building an ORM and its features for sure isn't an easy and simple task, one of the most complex tasks a programmer can perform probably. EF Core wasn’t designed to map many-to-many relationships without a class for join table, so I believe probably it requires changes to already done code, refactoring, architecture review, testing, optimization, all the stuff we already know… and for sure it requires a lot of time due to the complexity.

[chaim1221]

To be fair, this is the way you do it in SQL Server (or any other SQL engine, for that matter) –whether through CTE, or as a View, or just the regular old join table. It's really just getting rid of some stuff under the hood that was hiding this fact from us in previous versions of Entity Framework. My comment was not meant to be "spam," it was meant to point out that join tables "are a thing" and that that's what we are apparently using now instead of in-memory relationships that only EF knows about.

To be fair again, you could probably [NotMapped] your way out of the situation. @bricelam 's solution works just fine for most cases; if you need more, here's a deep dive:

https://blog.oneunicorn.com/2017/09/25/many-to-many-relationships-in-ef-core-2-0-part-1-the-basics/
https://blog.oneunicorn.com/2017/09/25/many-to-many-relationships-in-ef-core-2-0-part-2-hiding-as-ienumerable/
https://blog.oneunicorn.com/2017/09/25/many-to-many-relationships-in-ef-core-2-0-part-3-hiding-as-icollection/

[raffaeler]

@chaim1221 this can work if you are creating a new app.
If instead you are migrating an existing one where the model is public and cannot be changed, you are out of business.

Before or later this feature will need to be implemented. Given the length of this thread and the comments I am getting in conferences as a speaker I can say that the priority should be higher.

[chaim1221]

I hope I am not "out of business" because that is exactly what I am doing. I may have to go back and make the join tables as a separate migration in my MVC 4.5.1 project before I can apply the new schema, but I don't think it should choke too much after that?

Now you've given me pause. I'll post my results.

[ajcvickers]

Just to chime in here on a couple of things. First, we have had a plan for many-to-many from the beginning--see this comment: https://github.com/aspnet/EntityFrameworkCore/issues/1368#issuecomment-442548883. So hopefully we won't need to re-architecture much. However, many-to-many is a high-level behavior that builds on lower-level blocks, and it is these lower level blocks that we have been making progress towards.

Second, we generally consider this and TPT to be the highest priority big general-purpose features that are not yet to implemented. That being said, there are things like a robust and rich query pipeline where we still need to spend significant resources, there are some breaking changes we need to make in 3.0 since its a major version bump, and there are also strategic plays like the Cosmos provider which are a priority.

[nmocruz]

There's any estimate for this? before end 2Q 2019 ??

[vibeeshan025]

Will it get released with EF Core 3.0

[VinnieThePooh]

January 2019 is outside. Any estimates for this feature?
It would be nice to configure seamlessly (or almost) many-to-many for the same table.

[bricelam]

Support for property bag entities (listed on the 3.0 roadmap) is a stepping stone toward many-to-many support. Property bag entities will replace what EF6 called an independent associations in the state manager.

[popcatalin81]

property bag entities (listed on the 3.0 roadmap) is a stepping stone toward many-to-many support

@bricelam Are you pulling our leg? You can easily add the many to many entities as shadow state, without property bags. Shadow state works with virtual getters and setters does not need actual properties on a CLR class, so you already have your bags ...

[bfsmithATL]

So, this paragraph is in the latest edition of MSDN Magazine (see below.) In the context of this very old thread, is this the way the only way we can get many-many relationships without a join class? Worse, does this mean we should not bother with EFCore at all, since the fully featured & very mature EF6 is being ported to run on .NET Core?

[Cito]

This is also online here: What's Coming in .NET Core 3.0

[bricelam]

@popcatalin81 I too fail to see the difference between shadow entities and property-bag entities, but I’ve been told they’re different and better. 🙃

[ajcvickers]

@bfsmithATL If EF6 has the features and characteristics that you want, then waiting for it is a reasonable option. But keep in mind that EF6 will not get any significant new features.

[ajcvickers]

@bricelam @popcatalin81 EF Core currently supports shadow properties, but they must be on an entity type represented by a unique CLR type. So each join table could be represented by a CLR type (e.g. ProductFeatures, ProductCategories, and so on) which then had shadow properties. This is really no different from the current pattern for many-to-many since it still requires a CLR type for each join table.

What we originally planned here was to have an entity type not backed by _any_ CLR type. This is the shadow entity types feature, which is not currently implemented (except in a very limited form for the model snapshot.)

However, really what we need is an entity type that is not backed by any _unique_ CLR type. That is, if we can re-use the same CLR type for all join tables, then that would work and be easier to implement and manage. An obvious candidate for this is Dictionary<string, object>, but this really reduces down to any CLR type with a string to object indexer property. This is a general feature that we refer to as property bag entity types.

Those observing closely might also notice that weak entity types, which are currently only used for owned entity types with multiple owners, is essentially "an entity type that is not backed by any _unique_ CLR type." So property bag entity types are a matter of enabling indexer properties (which is not hard) and an expansion of weak types to cover more general cases (which is quite hard).

After this, the other big missing piece for many-to-many is skip-level navigation properties. That is, the ability to have a navigation property pointing from one entity type to another even when they are not directly related, but instead are only related through some other middle entity--in this case the join table mapping.

[raffaeler]

@ajcvickers I am interested in all the details about the way you will implement this feature.

Could you please expand this point please?

After this, the other big missing piece for many-to-many is skip-level navigation properties. That is, the ability to have a navigation property pointing from one entity type to another even when they are not directly related, but instead are only related through some other middle entity--in this case the join table mapping.

[bfsmithATL]

@ajcvickers Thx for the info, I sincerely appreciate it. One question for you (and the community), in your statement:

But keep in mind that EF6 will not get any significant new features.

Do you mean that the ported version EF6 will no longer receive new updates/features (e.g. the port won't be maintained in parallel to EF6 moving forward), or that EF6 is going to be sunsetted in favor of future development of EF Core?

[ajcvickers]

@raffaeler For example, taking the example at the top of this issue:

```C#
class Product
{
public int Id { get; set; }
public ICollection Categorizations { get; set; }
}

class Categorization
{
public int ProductId { get; set; }
public Product Product { get; set; }

public int CategoryId { get; set; }
public Category Category { get; set; }

}

class Category
{
public int Id { get; set; }
public ICollection Categorizations { get; set; }
}

That's what works now. However, to avoid this dependency on the join table, we need something like:
```C#
class Product
{
    public int Id { get; set; }
    public ICollection<Category> Categories { get; set; }
}

class Categorization
{
    public int ProductId { get; set; }
    public int CategoryId { get; set; }
}

class Category
{
    public int Id { get; set; }
    public ICollection<Product> Products { get; set; }
}

That is, the Categories navigation property to refers directly to the Category entity (and vice versa), even though there is no direct relationship between the two. In other words, it is "skipping over" the join table entity.

How this is implemented is not decided. Possibly by re-writing to a chain of shadow navigation properties defined by convention on the underlying entities.

[ajcvickers]

@bfsmithATL Yes. That is already the case, and has been for the last 4 or 5 years.

[ajcvickers]

@bfsmithATL Compare EFCore commits to EF6 commits

[ajcvickers]

@bfsmithATL Sorry for the spam, but I realized I only answered part of your question. The .NET Core EF6 will continue in parallel to .NET Framework support. It will be the same NuGet package(s). See https://github.com/aspnet/EntityFramework6/issues/271

[bfsmithATL]

@ajcvickers No problem. I see your point, much more activity in the EF Core repo than EF6, but is that a function of EF6 being more mature (less activity), whereas EF Core is up & coming? I'm not trying to split hairs here, but really just want to be sure I pick the library that will have the most longevity. So with that in mind, I'll stay with EF Core and keep gritting my teeth with waiting on the true many-many support :-)

[raffaeler]

@ajcvickers I am aware of how they are currently implemented as I already developed a 'workaround' consisting in a custom collection redirecting the many to many through a non-public intermediate entity, and an expression visitor rewriting the member access + call (include, theninclude) to redirect the queries.
Reason for this: can't change the public model
Reason for not being happy: it is very invasive and is hard to put in production (too long to explain).

I am asking because I would like to know if there can be any other way to workaround the custom collection. Again the need is to avoid modifying the current public model which doesn't have the intermediate entity. Time is a huge constraint and I need to run.

[ajcvickers]

@bfsmithATL It's not due to that. EF6 is not receiving any significant investment. EF Core currently has four engineers spending the majority of their time on it. In general, EF6 doesn't have any dedicated engineering resources. One EF Core engineer is currently spending time away from EF Core to port EF6 to .NET Core. Once that is done, they will be back on EF Core.

[ajcvickers]

@raffaeler The skip-level navigation properties will not look any different from other navigation properties to the application. They do not need a custom implementation of the collection. The difference is in how EF interacts with them.

[raffaeler]

@ajcvickers afaik there are no skip-level navigation properties now. Is there any workaround for my custom collection that I can implement in 2.1? (2.1 is important because it is an LTS)

[ajcvickers]

@raffaeler Everything I know about workarounds is in this thread, or in my blog posts referenced from this thread.

[DM2489]

I'm really confused by this. With mapping the joining table, don't we end up with a list of relationships that then contain a list of the mapped object's we're after?

In essence, an extra layer is added...

[raffaeler]

@DM2489 If you can change the model by adding the intermediate entity and "force" your users to express the queries in terms of the intermeditate entities, the current implementation works.
In all the other cases you just have a broken toy.

[yepeekai]

Out of curiosity, is it looking good for this to be on 3.0?

[marijnz0r]

@ajcvickers could we have an update on this issue?

[ajcvickers]

@marijnz0r There is no change in the status. This issue is in the Backlog milestone. This means that it is not going to happen for the 3.0 release. We will re-assess the backlog following the 3.0 release and consider this item at that time. However, keep in mind that there are many other high priority features with which it will be competing for resources.

[nagstaku]

@ajcvickers darn, that's too bad. This was a much loved feature from EF -- I look forward to its return.

[dannevesdantas]

EF Core currently has four engineers spending the majority of their time on it.

Is there any chance to more engineers join the team? EF Core, as any other full-featured ORM, is a very complex software. I have no experience with it, but I believe just four engineers is a very small team for the size and complexity of an ORM, even if the team is formed by super experts and high skilled professionals.

[pantonis]

Is there any place that we can vote and ask Microsoft to allocate more engineers for the EF Core team. Having a project so big with just four people is impossible to develop new features. EF Core lacks a lot of basic functionality. It is impossible to work on such an incomplete ORM. From what I can understand, Microsoft is not going to invest in EF Core anymore. If they did they would allocate more resources.

[bassebaba]

Is there any place that we can vote and ask Microsoft to allocate more engineers for the EF Core team. Having a project so big with just four people is impossible to develop new features. EF Core lacks a lot of basic functionality. It is impossible to work on such an incomplete ORM. From what I can understand, Microsoft is not going to invest in EF Core anymore. If they did they would allocate more resources.

I would also like some clarification on this point. During my years of .Net development, Entity Framework has been one of the most central / critical / important areas. It's all about the data. So I'm really confused about the seemingly low allocation of resources to EF-core. Don't get me wrong, I really appreciate the work these 4 individuals does, I just don't understand why EF-core ain't one of the most prioritized things in the whole .net-core sphere.... What could be more important than how to save and retrieve data? ;D

It also seems that EF6 development more or less have stopped? And I guess there will be no more ".net Framework / classic"-only EF-realeases? EF7 as I understand it, got renamed to EF-core...
So EF-core is what we got ahead of us?

I just need help to understand how to continue to develop new / maintain old projects which rely heavily on EF.

And to continue upon Pantonis question: is MS own point that SQL/EF is old and on its way out the door? Is CosmosDB / nosql the way forward? I think it's a justifiable question seeing how little resources EF-core gets...

[dannevesdantas]

I believe we got to the point: Not enough (human) resources for a full-featured ORM of this size and complexity. The ms EF team is great, for sure!!! There is no doubt about that, but they are just four :( As EF is now an open source project, I believe ms is expecting the community to contribute on it, instead of allocating more of their engineers to the project, but looks EF is not receiving enough contributions and it still lacks on some features.

So, what we could do to help on it? Any ideas? I guess maybe ms stigma caused by IE and windows update jokes couldn't help attracting new contributors from the world outside .NET ;( I personally don’t have experience building ORMs ;( and already contribute on vs code localization when I have some free time :( And what about if ms and community join forces? Is a too much crazy idea if ms tries to invite new contributors using their asp.net twitter page? And what about both: ms tries to bring at least one more engineer, in addition to trying to invite more contributors? Sorry, just some crazy ideas…

[AlphaCreativeDev]

Good points all around. We are working on a new system and started with .NET Framework and EF6. We are refactoring it now and don't know if we should go with .NET Core 2.0 and EF Core or wait until .NET Core 3.0 and the EF6 port.

[pranjalvatsa]

hi All,

Working with EF Core for the 1st time. Just encountered this issue with many to many mapping.
We have this relationship for the Below two models, Business and Client.

public class Business
{
public int Id { get; set; }
[Required]
public string Name { get; set; }
public virtual List BusinessesesClients { get; set; }
public double OverallValidatorScore { get; set; }
}

public class Client
{
public int Id { get; set; }
[Required]
public string Name { get; set; }
[Required]
public string Email { get; set; }

    public ICollection<BusinessClientRelation> BusinessesesClients { get; set; }
}

3rd Model to handle the relationship:
public class BusinessClientRelation
{
[Key]
public int BusinessId { get; set; }
public Business Business { get; set; }
[Key]
public int ClientId { get; set; }
public Client Client { get; set; }
}

We have also used fluend API in the DB context class:

modelBuilder.Entity()
.HasKey(bbt => new { bbt.BusinessId, bbt.ClientId });
modelBuilder.Entity()
.HasOne(bbt => bbt.Business)
.WithMany(b => b.BusinessesesClients)
.HasForeignKey(bbt => bbt.BusinessId);

        modelBuilder.Entity<BusinessClientRelation>()
           .HasOne(bbt => bbt.Client)
           .WithMany(b => b.BusinessesesClients)
           .HasForeignKey(bbt => bbt.ClientId);

public DbSet> BusinessesClients { get; set; }

Now i need to write a query to get all businesses along with their clients. I can get the businesses and the client object inside each business but not sure how to extract the Client info from the object

Interface method:
public List GetAll()
{
return _context.Businesses
.Include(bc => bc.BusinessesesClients)
.ThenInclude(c => c.Client)
.ToList();
}

Controller class:
public IActionResult Index()
{
var businessModel = _businesses.GetAll();

        var listingModel = businessModel
            .Select(result => new BusinessIndexListingModel
            {
                Id = result.Id,
                Name = result.Name,
                LogoUrl = result.LogoUrl,
                WebsiteUrl=result.Website,
                MinPrice=result.MinPrice,
                MaxPrice=result.MaxPrice, 
                ServicesOffered = result.ServicesOffered
            });

        var model = new BusinessIndexModel()
        {
            Businesses = listingModel
        };
        return View(model);
    }

Kindly help me out with the correct query to get all businesses along with their corresponding Clients.

[shivam183]

@pranjalvatsa You are facing issues because in your Business class instead of public virtual List BusinessesesClients { get; set; } it should be public virtual IEnumerable<BusinessesesClientRelation> BusinessesesClients { get; set; }. Right now you are just adding a list named BusinessesesClient in ur db which cannot connect with ur joining table, u need to make a collection of type BusinessesesClientRelation in order ur business to know which table to use

[Serjster]

Don't use IEnumerable for properties to be leaded by EF, always use ICollection instead, so

public virtual ICollection<BusinessesesClientRelation> BusinessesesClients { get; set; }

[btecu]

@Serjster what is the issue with using IEnumerable instead?

[Serjster]

EF does not load those properties in various scenarios... always use ICollection with EF loaded properties

[ajcvickers]

@Serjster If you are having issues using navigation properties that are exposed as IEnumerable<T> then can you please file a new issue and include a small, runnable project/solution or complete code listing that demonstrates the behavior you are seeing.

[Serjster]

I don't have the time to do as you request, but you may learn more about this issue from this StackOverflow post: https://stackoverflow.com/a/32997694/550975

I will add that you down voting my response without being aware of the issue is not cool.

[ajcvickers]

@Serjster That post is about classic EF, not EF Core. EF Core most certainly supports IEnumerable navigation properties (I implemented it!) so if it's not working, then that's a bug for which the appropriate thing to do is to file an issue so we can fix it.

[pranjalvatsa]

BusinessesesClientRelation

Yes, it was already that.. i finally got the query to work. Just had to add the below line of code:

var clients = business.BusinessesesClients.Select(p => p.Client);

[616b2f]

@ajcvickers thank you for clarifying it. I was also unsure whether to use IEnumerable or ICollection. Is there any benefit using the one or another?

[gojanpaolo]

@616b2f with regards to EF Core, there should be no difference between IEnumerable or ICollection. It all depends on your use case. Use IEnumerable if you just want to iterate (read) the collection. Use ICollection if you want to be able to add/remove (read/write) items.

[Kralizek]

Is it possible to use IReadOnlyList instead of IEnumerable?

[gojanpaolo]

@Kralizek Yes

[JoshMcCullough]

Why would you want to use a list instead of an enumerable if you're not making changes? Enumerable would (I have to assume) use a data reader to read a single record whereas list would be filled up in memory with all results.

[Serjster]

Let's move this discussion back to its original purpose of many-to-many relationships... discussions of best uses of IEnumerable, ICollection, etc. should be done elsewhere. I incorrectly brought up the IEnumerable vs ICollection issue that was only an issue in previous versions of EF.

[jzabroski]

Additional feature not yet mentioned

• [ ] 1. Support many-to-many where referenced entity is an enum (and therefore a special case of unidirectional as well, since you cannot put an ICollection property inside an enum)
• [ ] Reach goals:
  
  
  
  • [ ] 2. Table-per-hierarchy where a leaf class in the hierachy has a many-to-many relationship and the rest do not (this is effectively a regression test against the EF Core Metadata API and EntityTypeBuilder class's API)
  
  

Example 1

Derived from @bricelam 's example:

class Product
{
    public int Id { get; set; }
    public ICollection<Category> Categorizations { get; set; }
}

enum Category
{
    Books = 1,
    Music = 2
}

Example 2

TODO Provide example demonstrating usefulness.

[smitpatel]

@jzabroski - I fail to see how your example is variation of many-to-many. Enum are mapped (generally) to database column. To me your example seems like a collection scalar. See https://github.com/aspnet/EntityFrameworkCore/issues/4179

[jzabroski]

@smitpatel It's literally the same example Brice gave, but instead of materializing Category as a class entity, it is materialized as a enum. If I understand #4719 , I suppose my request is a composition of #1368 and #4179. In this request (#1368), the intermediary table does not require CLR class for a join table. In #4179, the navigation end does not require a CLR class. In my request, I want neither the intermediary table nor the navigation end to require a CLR class. I just want an enum.

Does this make sense? Note, your comments about #4179 are very helpful, because it makes me realize the complexity of implementing this correctly everywhere, including in the Query Translator: there is likely some non-trivial test cases around type inference rules in many-to-many queries on such enums.

[smitpatel]

EntityTypes are collection of properties and navigations which maps to a table in database. (or equivalent in non-relational). They can be tracked and modified and saved. On the contrary, enums are value types single value which can be stored in column. Even if you decide to make a table for enum, it will have a single value in the row which is the enum value. If you want tracking kind of functionality then you are basically having an entityType with just enum as single property in it.

Effectively collection of entityType is navigation. Collection of scalar is not a navigation. It is just a scalar. It could be mapped as direct array if provider support (like npgsql cc: @roji), or serialized into json or some other format. Or mapped to a different table but materialized into one by EF behind the scene as mentioned in #4179.

Further, many-to-many arises in real life because EntityA can have collection of EntityB & vice-versa. When you look at collection of scalar, EntityA can have collection of int but does int has collection of EntityA? Obvious, answer is no. As part of being one framework, they may have some overlap but implementation wise, both are quite different features.

[jzabroski]

tl;dr: ok
Long reply:
When you put it that way, it makes a lot of sense why you wouldn't support such a feature. However, I was thinking of it the navigation Product.Categories as a bijective map of two sets (enum values and a values for a given EntityType), not as a matter of entity ownership.

So, while I can see your perspective and follow it to its logical conclusion as to why this is not supported, I still think I was right to request it. In that sense:

Further, many-to-many arises in real life because EntityA can have collection of EntityB & vice-versa. When you look at collection of scalar, EntityA can have collection of int but does int has collection of EntityA? Obvious, answer is no.

The answer isn't always obvious - if you're thinking of things as a bijective map, then the phrases value type and entity type have little consequence. They're merely syntactic sugar that imply certain conventions.

[smitpatel]

If you are thinking about bijective map then basically what you are asking is to create an entityType enclosing a valueType as only property. And inject that wrapper entityType in place of collection of scalar. I did not comment about anything would not be supported. (What is supported right now is known). Merely pointing out that the syntactic sugar you want, wrapper entityType created for collection of scalar has no commonality with many-to-many.

[cristiangiagante]

I wish this feature will be able before Microsoft Deprecate .Net5 🌵 🌵 🌵 🌵 🌵 🌵 🌵 , I know that engineers working in ef core are doing his best, but really... this is a must have... Microsoft hurry up before this feature request converts to a big meme's place joking it of this.

[ThomasWendrock]

Fully agree with christiangiante. The workarounds for this missing feature in combination with Odata Web Api are a mess.

[asadikhan]

So, are many to many relationships being supported in EF Core 3.0 without needing the join table or no?

[prince272]

What is preventing this feature since "7 Jan 2015".

[ZBAGI]

For those who still looking for it. Here is easiest solution i could make. - It still uses Join entity but solution takes away quite a bit when dealing with it. Essentially it is "setup once and forget" solution.

Usage example:

var firstUser = new User("Name", "Surname", "[email protected]");
var secondUser = new User("Name", "Surname", "[email protected]");
var someRole = new Role("Root");
firstUser.Roles.Add(someRole);
secondUser.Roles.Add(someRole);

Anyway... I would like to see build-in solution for such a core functionality.

[ThomasWendrock]

For those who still looking for it. Here is easiest solution i could make. - It still uses Join entity but solution takes away quite a bit when dealing with it. Essentially it is "setup once and forget" solution.

Usage example:

var firstUser = new User("Name", "Surname", "[email protected]");
var secondUser = new User("Name", "Surname", "[email protected]");
var someRole = new Role("Root");
firstUser.Roles.Add(someRole);
secondUser.Roles.Add(someRole);

Anyway... I would like to see build-in solution for such a core functionality.

Hi ZBAGI, this is a very interesting solution. We've tried a similiar thing. Do you have some experience made with odata for these relationships? We have serious problems to get those many to many relations through the serializer, when using $select and/or $expand. Somewhere deep in the EF Core the (not-mapped) facade is set to an empty collection and will never be published back to the client.
If you have any hint, it would be fantastic. Best regards, Thomas

[ZBAGI]

Yes, @ThomasWendrock i have used OData with Many-To-Many

If you layer OData directly onto Many-To-Many you must also include joint entity and its values to populate facade. For example: Roles <-> User needs $expand AccountRoleJoin and then Role.

Easiest solution i could find is to implement DTO's with AutoMapper which will help you directly map UserDto.Roles property without exposing joint entity in your EDM model. On top of that you receive all that benefits that comes with DTO pattern.

Here is how to create EdmModel for DTO:

var builder = new ODataConventionModelBuilder();
var userType= builder.AddEntityType(UserDTO);
userType.Name = nameof(User); // I like to hide the fact it is DTO, at the end every entity in EDM should be DTO so adding sufix into every entry is pointless.
builder.AddEntitySet("Users", userType);

Here is how AutoMapper config should looks like:

new MapperConfiguration(cfg =>
{
    cfg.CreateMap<User, UserDto>()
        .ForMember(dto => dto.Roles,
            opt => opt.MapFrom(a => a.UserRoleJoints.Select(j => j.Role).AsQueryable()));
});

And finally the controller:

[EnableQuery]
public IEnumerable<UserDto> Get()
{
    DbSet<User> users = Database.Users;
    return Mapper.ProjectTo<UserDto>(users);
}

[ThomasWendrock]

Thanks @ZBAGI, I'll give this well explained approach a try in our generic odata controller...

[TehWardy]

@ZBAGI My existing OData V4 model sat on top of EF6 doesn't have the join entities so I can expand without the extra steps.

My experience has been that working with OData on top of EF is that the whole stack gets exponentially slower with each entity type you have to expand through once out of the DB.

I further back any option that makes migration from EF6 to EF Core as simple as possible as I have literally hundreds of OData endpoints and thousands of clients that would be majorly put out by a change that results in the exposure and having to query through "extra" entity types that weren't there before.

That said ...
I don't mind the entities being in the EF model if there is some way to ensure they aren't in the OData model but that I feel would present a complex business logic scenario involving some interesting mapping issues.

We tend to "as much as possible" avoid the need for DTO's and encourage the use of the entities directly where the bulk of our business logic is actually put in to the entities so migiration to EF Core is a major pain i'm suffering right now.

[prince272]

Will many to many be supported when .Net Core 3 is released.

[lloy0076]

Rather than get excited about all the intricacies, is it really too much to ask that for a BASIC many-to-many there is:

1. A model book
2. A join table book_author:
   
   
   
   • book_id
   
   
   • author_id
   
   
3. Another model author

...where book, author are examples and book_author is a fairly standard naming scheme.

I feel like the solution of "make an entity model with all the various mappings" substitutes:

INNER JOIN book ON book.id = author.id

...for something even _more complicated_ to understand.

If the conventions remained as simple -- for this simple use case -- then the EF model is almost certainly able to be generated in code (using a template or by driving a grammar) which implies the framework _could_ do it fairly easily.

[nagstaku]

Of all the things that have been happening with EF Core, this has been the one thing I have been waiting for before migrating any of my asp.net 4.6 projects over. I know the priority list balances resources required for a feature and if there are workarounds, but the workaround for this is just silly. I very much look forward to when this is adopted!!

[Angele]

4 Years... soon 5, and Many 2 Many is not properly implemented yet. Shame.

[nmocruz]

@angele indeed, how many people like me waiting this to migrate to core, or even start with core due lack o many to many mapping.
Not seeing people doing workarounds or changing the entity model removing all many-to-many relationships.

[danieltulp]

for everyone saying they are not migrating because of this:
let me say that .net core is way better in development than .net framework
seriously it is like night and day
so do not let this one thing (that I think should be a part of it) hold you back, you are only keeping yourself from moving forward for a thing you can work around

[ThomasWendrock]

@danieltulp: can you please tell me the correct workaround with many-To-Many-Relations in combination of an OData layer? Without problems when using expands and filters with Any or Concat?
But I agree. that the feature has not stopped us from going on with ef core.

[jzabroski]

for everyone saying they are not migrating because of this:
let me say that .net core is way better in development than .net framework
seriously it is like night and day
so do not let this one thing (that I think should be a part of it) hold you back, you are only keeping yourself from moving forward for a thing you can work around

Can you please not make assumptions about what our needs are?

We are communicating to Microsoft what our needs are.

I have been very vocal that the current fluent interface is a disaster, and the whole use-case driven approach is a nightmare because I can't predict what use cases are supported or not without spending months learning the tool. I looked very bad in front of a client when a project was delayed one week because the Discriminator logic didn't work in my use case. And I was in the office until 10pm every night fixing the issue to save face and my reputation. Why? Because the approach EF Core has taken is wrong. I can't recommend it because I had a very bad experience that cost me a lot of reputation points. As a consultant, relationships are everything. A good or bad anecdote can make or break your practice.

tl;dr:

True: .NET Core is superior to .NET Framework.

False: EF Core is superior to EF6.

[mahald]

With .Net Core 3 (comming out soon) you can use EF 6.3 on .Net Core.
https://devblogs.microsoft.com/dotnet/announcing-entity-framework-6-3-preview-with-net-core-support/

This comes handy for upgrades from .Net for Windows.

But for me the question arises, will people use EF6.3 over EF Core due to the many-to-many Feature in EF 6 ?

[jzabroski]

But for me the question arises, will people use EF6.3 over EF Core due to the many-to-many Feature in EF 6 ?

I talk to other lead developers/consultants, and know one guy with a large team in Poland who is clamoring to use EF6.3 instead of EF Core.

By the way, I do think EF Core has done a lot of innovative things and I hope the project continues. With enough time, it will become the gold standard. But there are some fundamental problems that just take lots and lots of man-hours to fix. EF6 has warts (it's slow, it's query generator is questionable), but its functionally complete with easy to Google workarounds for any issue you can encounter.

I'm pushing for EF Core to add the features I need because I would long term rather use EF Core than EF6. It has some innovations (extensible query expressions, shadow properties) that I can deliver features with that I cannot deliver with EF6. My features I need are: better fluent mapping, especially many-to-many and TPH discriminator.

[pikausp]

From looking at the notes and release schedule being in sync with .NET Core, is it safe to assume this won't be implemented before Nov 2020 for sure?

[TehWardy]

Whilst many to many relationships are supported (ish), it's not in the way we might expect, and the net result is when you want "like for like" functionality on your EF6 structure because frameworks like OData sat on top of it expect things to work that way and you don't want a huge client code refactor.

Here's the problem I ran in to ...

https://github.com/aspnet/EntityFrameworkCore/issues/17642#issuecomment-529558408

... frankly after seeing no progress on this for 5 years i'm really starting to fail to see what makes .Net core in any way "better" than .Net 4.6

From actual testing with stacks that run exactly the same functionality side by side on the same server I have not found any of the promises made by Microsoft regarding .Net Core to be true ...

1. It's not faster
2. It's not cleaner or better structured
3. Features have been removed (e.g. config transforms, that produce a single config for the server that the build is going to) so it's not like for like in fact the Core version now has to actively hack round half backed framework features / re-implement removed ones.
4. Asking for help keeping existing functionality / app behaviour consistent is met with "it's not our problem" style responses and tickets getting closed because we should just update to the new "preview" or "RC" build which is ultimately more broken.
5. After 5 years waiting on simple things like this and the Azure Functions SDK supporting the latest version of Newtonsoft.Json FFS M$ is still dragging it;s heels forcing us to depend on old dependencies for compatibility reasons.

Everything is about Azure these days ...
I asked for a workflow engine and got told I needed to basically build one by "wrapping up several orchestration engines API's" that are not fit for purpose after several months of discussion with various technical experts inside various M$ teams.

It's also gotten to the point where the tooling is getting worse ...
VS is getting slower and having to account for more and more versions of frameworks that don't agree with each other.
I often find myself typing something and waiting for a few seconds just to allow the intelli-sense process to catch up.

I could go on all day about this stuff but the fact is M$ doesn't seem to know what the hell they are doing any more implementing a framework that's barely out of beta and is already discussed as being replaced ... one vision, one framework or why bother shipping it at all? right?

[danieltulp]

@jzabroski

I never said EF core was superior to EF6 and I didn't make any assumptions, I merely pointed out that my opinion is that the drawbacks of the current EF core implementations are not a reason for me, not to go for .NET Core

[danieltulp]

@danieltulp: can you please tell me the correct workaround with many-To-Many-Relations in combination of an OData layer? Without problems when using expands and filters with Any or Concat?
But I agree. that the feature has not stopped us from going on with ef core.

I am not familiar with an OData layerl, but I think a good solution has already been mentioned in this thread.
I always create my own Web API with a business logic layer.
My many-to-many approach is as the mentioned work around:

public class Parent
{
    public int ParentId { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
    public ICollection<ParentByChild> ParentsByChildren { get; set; }
}

public class Child
{
    public int ChildId { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
    public ICollection<ParentByChild> ParentsByChildren { get; set; }
}

public class ParentByChild
{
    public int ChildId { get; set; }
    public int ParentId { get; set; }
}

You can than use Include and ThenInclude in Linq queries to let Entity Framework create the joins for you.

[TehWardy]

@danieltulp That's not a solution to my problem, and is only one type of many to many join: imagine an entity that looks like this ...

public class Thing 
{
     public int Id { get; set; }
     public virtual ICollection<Thing> RelatedThings { get; set; }
}

... with EF Core in the state it's currently in this is not possible to construct even if i add a join table entity as I will always end up with 2 collections which results in confusing usage scenarios for the api layer consumer or the need to add in hacks further down the stack to compensate for the missing functionality on our part as consumers of the framework.

As stated in a linked comment above (https://github.com/aspnet/EntityFrameworkCore/issues/17642#issuecomment-529558408) ... the EF team do not yet support this scenario.

In short ... if you have "Parent child relationships" it works, but if you have "these are related but neither is a parent or child relationships" then it doesn't.

Putting this in to perspective (a real world example) ...

• If I have a Folder, the files in it are considered children ... this works.
• If inside that folder I want to say "these two files are related to each other", I can't.

This is a fundamental piece of missing technology that was previously in EF6 and has not yet been implemented in EF Core despite a 5 year wait from us all.

If you consider what the implementation of this would require in the underlying EF Core implementation, all those extra "many to many join entities" we had to create and expose fundamentally changing our OData endpoints behaviour will ultimately be thrown out when this feature eventually gets implemented putting the final API layer back in the state of a .Net 4 implementation today.

For those of us like myself implementing API's that are consumed by hundreds of companies who each have solutions being built on top of the API I build this is a real problem that presents a serious risk to our business' bottom line.

[jzabroski]

@TehWardy Hi Paul, I am running Visual Studio 2017 and 2019, and have zero performance issues. Why don't you email me via my profile and we can discuss offline what performance problems you have. The long and short of it is: Visual Studio now does a ton of stuff in background threads, which requires a lot of cores and a lot of memory. The other thing is I don't do programming work on a laptop. I've found power saving modes on laptops interfere greatly with tasks like compiling, plus most laptops stop at 16GB memory. Here are my work specs: https://www.userbenchmark.com/UserRun/20192284 My home specs are insane - I have the latest Nvidia top of the line graphics card, Intel 9900-K, Intel Optane SSD w/ 1gb / second read bursts, etc. I literally do "business at the speed of thought" with these specs. Such a rig only cost me $2,500.

[Angele]

This is not a forum thread to discuss .net core vs .net. Also not to complain about your personal life, or to boast about which big company you have contact to or work for. (Sorry to hear you have stress and long working hours tough) Also not about developer workstations!

Fact is this missing feature leads to bad code from bad developers, lots of stories like the one from jzbroski and makes this package overall less good.
Despite there are workarounds it's an important feature!
And the fact that it's brother is capable of doing it right, might even get developers to move away from core in early stages.

[TehWardy]

@jzabroski Yeh VS runs fine on a $2,000 workstation, try it on a $400 workstation, that said I agree with Angele that this is not the place to discuss VS performance, it was simply another piece of the frustration of dealing with .Net Core.

@Angele

This is not a forum thread to discuss .net core vs .net. Also not to complain about your personal life, or to boast about which big company you have contact to or work for. (Sorry to hear you have stress and long working hours tough) Also not about developer workstations!

Stating the fact of the business impact to us is none of that.

Fact is this missing feature leads to bad code from bad developers,

And bad code from good developers to work around the issues / missing features which is harder to unpick in the longer term.

Despite there are workarounds it's an important feature!

There isn't a work around for the relationship i have described as linked above, someone from the EF team in Redmond clearly stated that. That's the point!
Unless you know something Redmond doesn't?

[pantonis]

I was under the impression that this feature will be part of ef core 3. Based on this.
Is this coming in EF core 3 or not?

[roji]

@pantonis no, this feature is not included in EF Core 3.0.0. Issue #10508 tracks actual implementation of the feature, please follow that to see milestone changes.

[GorbatukArtem]

@pantonis no, this feature is not included in EF Core 3.0.0. Issue #10508 tracks actual implementation of the feature, please follow that to see milestone changes.

it's sad. I was waiting for the change. it's needed like air.

[Serjster]

It's really not that needed! It is a very simple work around using 2 instead of 1 query calls. It's a convenience, sure, but certainly not needed.

[raffaeler]

@Serjster please avoid absolute statements. While there are cases where you have a couple of workarounds, there are others where one really need it.
I created my own workaround rewriting expressions but it is definitely a stopper for those who wants to migrate from the old to the new codebase without making revolutions in code.

[Serjster]

@raffaeler That is silly. It is no "revolutions in code" to change from a many to many done via EF and where it is done manually via multiple calls.

[raffaeler]

@Serjster again, it depends, don't make absolute statements.
If your code is already in place and thousands of clients should be modified, or a code generator already emits the code using the many to many strategy, it is silly not to change EF itself.
BTW I work with projects having both those real use cases.

[Serjster]

The shapes of your models do NOT need to change... just make a second call at the EF level to load the many to many and none of your thousands of clients even need to know that something changed!

[raffaeler]

It is needed because they come with plugins where there is business logic too. Don't make absolute statement please.

[Serjster]

@raffaeler I disagree with you and stand by my absolute statement.

[AlexVPerl]

@Serjster I think you're over simplifying things. If you work with small code base & simple data model yeah this workaround is no biggie. But for anyone maintaining a real enterprise level system where data models tend to very complex, this is indeed a deal breaker. The cost of refactoring effort alone is enough for PMs to keep existing EF implementation and hold off on upgrading to EF Core until it matures.

[Serjster]

I work in enterprise systems that have well over 150 services with each having their own databases, very complex models, etc. and it took a couple devs only a few days to refactor the code to not relying on EF many-to-many.

[AlexVPerl]

I somehow find that hard to believe :P But I guess you're one of those devs that's set in their ways based on their narrow experience and nothing will change your mind. I bet you're a real treat to work with too. There are different codebases and different use cases. Making overgeneralized statements like that shows your level of experience. This thread is full of devs saying this is a priority for their projects. If this wasn't a big deal for you, then fine - but don't be dismissive of others.

[Serjster]

You can call my over 15 years as lack of experience but in that limited time I've come to see that devs tend to exaggerate their pain. I'm sure there are horrible code bases that allow database access from all over their app, but that calls for a refactor! If all of your database calls are calling from repositories or other centralized locations as they should be, then this really isn't a problem. And going ad-hominem about how much of a treat I am to work with especially based on pure assumption shows your level of ignorance.

[TehWardy]

@Serjster I, like you, have 20 years in the industry and have to disagree.
It depends on the relationship, some have work arounds, others don't and by that I mean "it's not that EF won't handle any potential mapping scenario if you're able to talk to EF directly it's that Entities or LINQ expression trees from the frameworks that sit on top of EF will break" based on integration scenarios beyond EF.

I literally quoted someone from M$ stating as much directly (see above).

In the real world EF is not a stand alone solution, it's a dependency for complex stacks of frameworks (which you will obviously know after 15 years), pairing it with something like OData or GraphQL limits how far you can go with it to continue having the stack still function correctly before your cross framework functionality hit limits.

If you only look at EF as a stand alone feature it's a perfectly reasonable claim you're making ... the problem is EF is basically never used in such a scenario except for the most basic edge cases and the bulk of enterprise solutions i've seen / designed simply can't be reduced entirely to the current feature set for good reasons without there being issues.

It took me 3 months on lengthy conf calls and over emails to get through the Azure services team that a collection of Orchestration engines doesn't make a workflow engine, this is the foresight that M$ teams often fall short of.

What's the point in an ORM unless it's going to be consumed by something else?
You can argue all you like that the ORM "does a thing" but if the framework on top of it can't consume that thing, it's essentially a useless point, and that's (to my mind anyway) the issue here.

M$ has historically been the global champion of software compatibility, it would be nice to see that continue in to the future.

[lloy0076]

As I've said before, I suspect that the most basic "many-to-many" classes could likely be generated by a template engine -- which begs the question of "well, why don't I write" such a thing. This almost certainly won't solve complex cases but if the computer can generate it reliably in code (either literally or in memory/some temp. place on the fly) then I have less code to figure out and the code becomes simpler.

$it->isRelatedToThat() is far less complex than "Let's write a separate class and wire it up". At the very least it's many less keystrokes to make a typo ⌨️

[jzabroski]

You can call my over 15 years as lack of experience but in that limited time I've come to see that devs tend to exaggerate their pain. I'm sure there are horrible code bases that allow database access from all over their app, but that calls for a refactor!

1. Developers do not always have full control of project scope, including time to refactor.
2. Developers do not exaggerate their pain, they accentuate that they're already working long hours from time-to-time, and they love it when vendor software "just works", so that the projects that are supposed to be easy, are actually easy.
3. It's hard to understand how something that was working in EF6 cannot work in EFCore.
4. None of your advice applies in the context of "Rapid Application Development" using OData, as described by @Mezzenine on April 11, 2018 (over a year ago). In RAD, the goal is to minimize development cost as much as possible and take sensible shortcuts. Every additional "accidental complexity" step in a RAD project is strictly unnecessary cost.
5. We're perplexed why someone comes to a Discussion to discuss why it's not important, without suggesting what is important to them. I literally went through your GitHub activity and saw no activity for EFCore or EFClassic anywhere.
6. We're perplexed why the "adequate workaround" solution is to introduce non-determinism in the middle tier by bringing back two data sets to the middle tier and then joining the results. That is not the solution, at all. The solution has been mentioned many times already in this thread [[1](https://github.com/aspnet/EntityFrameworkCore/issues/1368#issuecomment-222118379)] [[2](https://github.com/aspnet/EntityFrameworkCore/issues/1368#issuecomment-277309185)], including by EFCore developers. However, it's not straight-forward algorithm in all cases, such as when you add pagination [3]. When your websites start returning 429 errors due to non-determinism, you can trace it back to bad decisions like these. I've fixed these problems for many companies.
7. EFCore developer @ajcvickers has published four blog posts [[4](https://blog.oneunicorn.com/2017/09/25/many-to-many-relationships-in-ef-core-2-0-part-1-the-basics/)] [[5](https://blog.oneunicorn.com/2017/09/25/many-to-many-relationships-in-ef-core-2-0-part-3-hiding-as-icollection/)] [[6](https://blog.oneunicorn.com/2017/09/25/many-to-many-relationships-in-ef-core-2-0-part-2-hiding-as-ienumerable/)] [[7](https://blog.oneunicorn.com/2017/09/25/many-to-many-relationships-in-ef-core-2-0-part-4-a-more-general-abstraction/)] on his blog about workarounds for this problem until this issue is fixed.

[rblackman]

no, this feature is not included in EF Core 3.0.0. Issue #10508 tracks actual implementation of the feature, please follow that to see milestone changes.

@roji That issue hasn't been updated in nearly a year. For those of us who are waiting on this to move to EF Core, it would be nice to at least get an idea of when this is planned to be implemented and if it's going to be implemented at all.

[MaStr11]

Yesterday at dotnet conf the ef core team showed a "What's next?" slide: youtube (minute 20)

According to that, the feature is planned after the release of ef core 3.1:

ef core 3.1 is planned for the end of the year.

[DM2489]

@MaStr11 Yeah, the problem is that it's had the consider-for-next-release tag applied and removed a number of times over the last few years.

https://github.com/aspnet/EntityFrameworkCore/issues/10508

Just because it's on the vNext discussion board does not mean that it will be implemented for the next version after 3.1.

[divega]

@DM2489 is correct. In the live stream I clearly said that everything under that EF Core "vNext" list is under discussion.

We are basically taking all the pain points that we know customers want us to address and we are starting to discuss which ones we are actually going to tackle, as we usually do on every release. We are certain that we aren't going to be able to address all of them at once given our team's resources.

Also, I don't think the recent back and forth between those who think many-to-many is blocking and those who don't is adding any new information at this point. This thread is more than 300 messages long and the same perspectives have been brought up before. In fact the demeaning tone of some of the comments yield negative value for everyone.

The current state of affairs is that the EF team is familiar with the pros and cons and has decided a long time ago that EF Core should implement many-to-many support. The only question is when this will happen, and hopefully we will be able to communicate soon if it is happening on "vNext" or not.

[nmocruz]

@divega we need also to understand that people had many-to-many in most of all popular ORMs 10 year ago. The expectation were to have that on dotnet core 1, was not even on 2, and again is not coming on 3, and all that is given to us is a space to discuss. So the mod is needed to express sentiments about this never being delivered, and some uncertainty if it will be on the next version or not caused by what is happening.

[Prasanna-Sridharan]

Not adding any useful idea to issue but just an observation :
EF Core has made one-to-one many relationships easier than EF6 : EF6 demanded primary key be used as FK as well in the dependent entity (at least in Data annotations, not sure about FluentAPI) but EF Core works like a charm allowing separate FK property in dependent (unique index in covers).
but many-to-many relations have been made difficult than EF6 (requiring extra class).
Its not like EF Core is completely inferior but has few extra and miss few good features than EF6.

Hopeful that EF Core will have all good EF6 features soon,(plus its own DB, platform agnostic design features) positioning itself as a better ORM :-)

[jzabroski]

We are basically taking all the pain points that we know customers want us to address and we are starting to discuss which ones we are actually going to tackle, as we usually do on every release. We are certain that we aren't going to be able to address all of them at once given our team's resources.

@divega I want to express something, but am not sure quite how to state it. As an engineer, humility and assertiveness are two sides of the same coin. My general desire is to understand some of the decisions and why they aren't "1 story point" and might even be "high risk items" that get worked on without making it into mainline, but also understand blog posts like Announcing EF Core 3.0 and EF 6.3 General Availability. My worry as an engineer is that ORMs are quite complicated, it's hard to understand why so much effort was put into "LINQ Overhaul" and how it "enables translating more query patterns into SQL". As a suggestion, it would be really cool if you included some numbers to back up that assertion, such as, "We added 50 new unit tests to the new query generator, and in particular, these are queries that simply broke the old query generator in surprising ways."

It would also be really cool to hear "Lessons Learned building an ORM" at some point. What mistakes did you make, what did you realize about customers needs, how did you fix them, etc. I think Andrew has communicated some of these to me (and also on his blog), but there's no coherent view of it "everywhere".

Note: I do notice and appreciate your thoughtfulness in many areas.

[smitpatel]

@jzabroski - Not trying to answer larger question but being part of query overhaul, I can give some data
This link is rough subset of all the query issues which were fixed by new query pipeline simply by new design. Those issues are in 3.0 milestone since we verified they were fixed in 3.0 without making any code change. (Regression tests for those issues went in 3.1 branch).
Some big features which worked out in new pipeline

• Removal of client eval
• Collapsing N + 1 evaluation to single query. (https://github.com/aspnet/EntityFrameworkCore/issues/10001, https://github.com/aspnet/EntityFrameworkCore/issues/11186, https://github.com/aspnet/EntityFrameworkCore/pull/17077)
• Unique-fying sync/async pipeline upto the point of enumeration. No more deadlocks in EF Query.
• Recognizing value converters first class in query pipeline. #12045
• Inferring type mapping more robustly. #13192
• Removal of identity resolution so no more memory peak.
• Removal of external libraries so easier for provider writers and contributors to work with query.
• Performance improvement by reading values from DbDataReader directly.

Most of things which got fixed in 3.0 in query were actually impossible to fix in 2.2 codebase due to design limitations. Further we have put a concrete base going forward to make it easier to expand translations.

I understand it is hard to see value of new implementation unless you are affected by aforementioned points. e.g. If you are writing good queries which never client eval'ed before, or not using value conversions. Things are more-or-less same for you. It could be case for many of good developers. Many of recently filed issues I am seeing in 3.0 right now, are mainly earlier implicit client eval is broken for customers. It is painful but that was our intention to guide customers in right direction rather than hiding issues in linq queries.

Further, if you think about features like data seeding or column order they do not have any relation to query pipeline. But features like many to many & TPT has huge overlap with query. Query pipeline needs to expand collection navigation into join when many to many is used with implicit join table. Having a good design in query makes it easier for us to implement this feature rather than trying to fit into older design.

I hope this post helps you appreciate a little what we did and why we did it.

[Atulin]

So, while we're waiting for EF Core to reach feature parity with decade-old PHP ORMs someone wrote as their first project in highschool, does anyone have some other ORM in mind that could spare the headaches and having to manually write join classes?

[DM2489]

So, while we're waiting for EF Core to reach feature parity with decade-old PHP ORMs someone wrote as their first project in highschool, does anyone have some other ORM in mind that could spare the headaches and having to manually write join classes?

If the task is as easy as you imply, perhaps it's something you could look into doing yourself?

Hell, this project is open source. What's stopping you from implementing this yourself and submitting a PR?

[ptMuta]

@DM2489 now now, don't assume everyone is capable of writing a "first highschool project"

[Atulin]

I'm not saying it's easy, and I'm certainly not implying that I would be able to do it alone. I'm not a team of highly-skilled well-paid veteran Microsoft engineers after all.

What I'm saying, is that I've seen a good few "hey guys, made an ORM in PHP for fun" projects that had support for N-N relationships. There's also the fact, that EF6 had this support, so it clearly is doable in .NET.

I'd like to think, that when creating an ORM for relational databases, relations would be put front and center. As in, start making the ORM by making sure it supports all relation types, then make sure it supports all join types, progress from there. I can't fathom why EFC was made with "let's support all relationships except N-N. Man, fuck N-N. N-N killed my hamster when I was 5" mindset.

What's even more baffling, is that this issue – a request for one of the most basic functionalities of any proper ORM – is in a limbo for half a decade or so. And judging by the progress – or lack thereof – of the issue that's supposed to serve as a progress tracker, it won't be added until 2025.

[DM2489]

I'm not saying it's easy, and I'm certainly not implying that I would be able to do it alone. I'm not a team of highly-skilled well-paid veteran Microsoft engineers after all.

So, perhaps don't be so crass with your presumptions. IIRC this team consists of a handful of developers, who are also responsible for the EF6 project too.

What I'm saying, is that I've seen a good few "hey guys, made an ORM in PHP for fun" projects that had support for N-N relationships. There's also the fact, that EF6 had this support, so it clearly is doable in .NET.

Making your own ORM to your own spec and to meet your own requirements is one thing. Developing an enterprise level product is entirely different. There will be thousands of use cases, customers, and project utilising EFCore, which in turn requires loads of unit tests to be written and thousands of bugs/issues to go through and fix. All the while considering the performance implications of any changes and how it might affect/break things for an existing user base.

You're essentially comparing two things of entirely different scales, scopes and expectations, and equating them to the same thing.

No-one is saying many-to-many isn't doable. It just hasn't been done yet, because it hasn't been prioritised by the team.

What's even more baffling, is that this issue – a request for one of the most basic functionalities of any proper ORM – is in a limbo for half a decade or so. And judging by the progress – or lack thereof – of the issue that's supposed to serve as a progress tracker, it won't be added until 2025.

As @divega stated above, they are a small team. Just because no progress has beem made on this specific issue does not mean that the package has not moved on in 5 years. @smitpatel highlighted some of the changes made on 3.0 and reasons for why they were made.

I'm sure that everyone on this thread would love to see a full implementation of many-to-many relationships, but ranting an raving about it here isn't going to get it done any quicker.

[yepeekai]

I think it is important to close the features gap between EF6 and EF core. Those features missing are often a show stopper for projects to be migrated from EF6 to EF core. I don't think this is much an issue for new project but for existing projects that want to stay up to date, it doesn't make sense to put a lot of time to change the code base as a workaround for a missing feature and then cleaning up that code base again in a year or two once that feature is implemented. In that regard, I think this is an important feature... I don't know all other differences from EF6 to EF core, but in our case, from day 1, there were 2 importants features missing (group by and many to many). Group by has been addressed a while ago, but many to many has been ignored for too long in my opinion... It is an unecessary PITA to manage intersection tables when doing optimized linq queries. And again, it is a show stopper considering that this effort is temporary and for those choosing to go forward will have to make additional effort to go back to what it was...

[yepeekai]

Is is nice to have information about what the query pipeline overhaul improved. But mainly, it doesn't add much for those that did not use clientside execution. 3.0 seems to be a lot about refactoring and some breaking changes but not much about new features which is sad... There were also a long part of 2.x that was about adding unit tests, which is good but again doesn't add much features. I think that when a lot of time is required for things that don't add features, it would be nice to temporarily add engineers on the team to keep the project going forward while refactoring/unittests are being made.

[jeffguillaume]

No-one is saying many-to-many isn't doable. It just hasn't been done yet, because it hasn't been prioritised by the team.

OK, so the question is: WHY THE F$#K NOT?? This thread should be all the proof needed to prioritize.

I've personally been subscribed to and following this thread for many years. It's downright absurd N-N relationships, one of the bedrocks and most commonly used basics of relational databases, is STILL not implemented.

/gripe for this year. See you again next year.

[ericsink]

(To be clear, I don't speak for anybody on the EF Core team.)

I see disappointment and frustration here. Those are understandable.

But I would ask everyone here: please try to express those emotions without being unkind.

Thanks!

[jeffguillaume]

I see disappointment and frustration here. Those are understandable.

The time for respectful reflection is done. We deserve a solid answer on why this has been dragging for LITERALLY HALF A DECADE...

And now I'm seeing whispers of 2025? Please... this is all just a joke to them if that's the case. They clearly have no intention of doing this, so why should we need to be overly respectful at this point?

Edit: The only reasonable thing for them to do now is ADMIT they are NOT going to implement this. And _if that's untrue_, they need to provide an actual timeline and implementation promise.

[ericsink]

@jeffguillaume FWIW, I was speaking of being kind or unkind, not being "respectful", which seems a bit different to me, but anyway...

We deserve a solid answer on why this has been dragging for LITERALLY HALF A DECADE...

There exists a separate discussion on what consumers of an open source project "deserve", but your remark is not unkind, so I'm fine with it.

this is all just a joke to them if that's the case. They clearly have no intention of doing this

OTOH, this one strikes me as somewhat unkind, in a subcategory I call "assuming the worst of people".

so why should we need to be overly respectful at this point?

If by chance this question was not rhetorical, and if you had said "kind" instead of "overly respectful", I would have answered it like this:

1. Being kind is always appropriate.
2. Being unkind won't actually help you get what you want anyway.

Bottom line: If someone is upset about this situation, that's valid. I'm just calling for folks to use care in how they express those feelings.

[jeffguillaume]

@ericsink Appreciate your calm demeanor.

My emotions on this have traveled a straight path from disappointment, to exasperation, to disillusionment and finally, incredulousness — we're going on 5 years with this unresolved issue and still no clear path to implementation.

Someone on the Microsoft team said previously here they _know_ how ornery this issue makes people, and yet they continue to ignore widespread demand for implementation. In this regard, they are not respecting the developers who have expressed a great need for this very basic ORM feature. I guess that's why I brought up respect, as opposed to kindness. It's also why I am now sick and tired of the inaction on this issue, and have begun expressing it more flagrantly.

[Atulin]

I think that while the first 1-2 years of waiting on basic functionality would warrant a civil discussion, half a decade makes anger understandable. Just my opinion, though, perhaps there are people out there who are fine waiting even 10 years for support of all relation types.

[ericsink]

@Atulin Anger and civility are NOT incompatible.

[Atulin]

You're right, let me reiterate then.

I think that while the first 1-2 years of waiting on basic functionality would warrant a civil discussion, half a decade makes some incivility understandable. Just my opinion, though, perhaps there are people out there who are fine waiting even 10 years for support of all relation types.

[trekco]

so..eh...when will it be done? asking for a friend...

[nmocruz]

seems like this team is not using agile principles. many-to-many is being seen as optional or not important to deliver for 5 year, besides the users asking for this in a bold way the answer is "we need to discuss, then maybe we put this on roadmap". If dotnet was doing this 10 year ago, and java delivering as dotnet did, I was not working with microsoft today.

[Atulin]

Man, I wish there was a .NET port of Doctrine right now, not only does it have support for N-N in the first place, it has bidirectional N-N and unidirectional N-N... Which is another thing I could very much use right now.

[dragnilar]

I'd really like to see this added as many others have stated, but the join tables have kind of become a way of life for me, so it's not a huge problem. I'd still love to see them go away though.

@nmocruz Not sure what methodology they are using, but I can't help but feel that they were operating under some kind of crunch mode with .Net Core 3.0. The release date seemed a little too soon to me. I really hope that wasn't the case.

But either way, I still love Dot Net and 🦄s.

@Atulin It's been soooo long since I have used PHP, and I don't have any plans to go back, but I definitely wouldn't mind seeing that as well.

[Atulin]

Yeah, Doctrine is simply amazing. Going from Doctrine to EF Core feels like such a massive downgrade

[lloy0076]

https://gunnarpeipman.com/aspnet-core-nhibernate/ - it's been a while since I've used Hibernate (I'm also a Java developer) but it seems that NHibernate is actually usable on EF 2.X and 3.0.

[gusgorman]

There exists a separate discussion on what consumers of an open source project "deserve",

If this was an open source project being run by a group of hobbyists devoting their free time to create it then this would be a valid point. But it isn't. Its a Microsoft technology that many companies use in good faith assuming at the very least fundamental functionality will exist. If this was version 0.3 then it wouldn't be a problem.

[jeffguillaume]

If this was an open source project being run by a group of hobbyists devoting their free time to create it then this would be a valid point. But it isn't. Its a Microsoft technology that many companies use in good faith assuming at the very least fundamental functionality will exist. If this was version 0.3 then it wouldn't be a problem.

THIS!!! is why I am justified being upset at this point, and continue to call out the bullsh!t in this thread.

I am done being polite here. My previous post stands: Either the Microsoft team needs to tell us when exactly this will be implemented, or admit that it's not important enough (to them) to fix after YEARS OF CUSTOMER DEMAND.

[nmocruz]

@jeffguillaume all that microsoft team admitted was lack of resource to implement this and that strategy to address is keep us screaming and crying for not seeing this in the roadmap (or being pull out all the time to the future), and then blame us for spam this thread with useless messages they are ignoring or at least answering.
I'm not inside of why this was implemented on version 1.0, maybe there's other important bugs and features to give low priority to this, but lack of resource is not good answer to me, because no one was expecting to have EF to work with a CosmoDb before many-to-many mapping. This is all schedule decisions, people are not happy, and to me is not hard to understand why.

[dragnilar]

@nmocruz Something you made me ponder.... I do not have a crystal ball (or at least not the most accurate), but I am wondering if another reason why EF 6 was brought to Core was to also start exploring the possibility of some sort of merge with Core for .NET 5 since the two now have a significant number of features that the other would benefit from having... including the feature that this very thread is about.

[popcatalin81]

but I am wondering if another reason why EF 6 was brought to Core was to also start exploring the possibility of some sort of merge with Core for .NET 5 since the two now have a significant number of features that the other would benefit from having

There's no way that can be achieved.

The code bases are so different that EF Core and EF 6 cannot be merged. They both have different expression trees to use for Linq, wildly different medatata, EF6 has EMDX with a complex mapping model involving different mapping languages: CSDL, SSDL MSL. and widelly different driver models and architecture, where EF core is based on Dependency Injection and EF6 is not.

What they share is a similar surface API for the EF6 DbContext api and EF Core. But in the case of EF6 the API is a mere wrapper on top of the old Entity Model API which is quite different from EF Core API.

[dragnilar]

but I am wondering if another reason why EF 6 was brought to Core was to also start exploring the possibility of some sort of merge with Core for .NET 5 since the two now have a significant number of features that the other would benefit from having

There's no way that can be achieved.

The code bases are so different that EF Core and EF 6 cannot be merged. They both have different expression trees to use for Linq, wildly different medatata, EF6 has EMDX with a complex mapping model involving different mapping languages: CSDL, SSDL MSL. and widelly different driver models and architecture, where EF core is based on Dependency Injection and EF6 is not.

What they share is a similar surface API for the EF6 DbContext api and EF Core. But in the case of EF6 the API is a mere wrapper on top of the old Entity Model API which is quite different from EF Core API.

@popcatalin81 Interesting. I was already aware of the fact that EFC is a rewrite and thus the two are pretty much different beasts, although the wrapper part was an interesting factoid I was only partially aware of, thanks for pointing that out 👍. And in all seriousness, I hope it isn't something that they are exploring, due to the fact that as you just went to the trouble explaining, wouldn't be a good idea.

But I think this hints at the overall point that the recent posts on this thread are alluding: the way this feature is not being addressed or commented on, leaves us to only speculation and/or getting grumpy. Don't take this as me deriding you @bricelam @divega , I still ❤️ you guys.

[nmocruz]

@dragnilar seems more that was to let people migrate to .net core from projects where EF6 cold be a pain, fear to inject bugs by touching hundreds of files, and inventing ways to make old code work with workarounds in the schema. This will maybe let people to be on dotnet 5 without change alot, but for people starting a project designing new model in dotnew core, will be ef core and ef6 to choose, knowing limitation of first and the future of the second.

[dragnilar]

@dragnilar seems more that was to let people migrate to .net core from projects where EF6 cold be a pain, fear to inject bugs by touching hundreds of files, and inventing ways to make old code work with workarounds in the schema. This will maybe let people to be on dotnet 5 without change alot, but for people starting a project designing new model in dotnew core, will be ef core and ef6 to choose, knowing limitation of first and the future of the second.

Yep, I remember reading about that. Anyways, my comment was just half speculation about behind the scenes/undisclosed work, sorry for the confusion.

Edit: Still though, if you think about it, having EF6 continuing to be supported does sort of "kick the can down the road"... sooner or later they will have to decide whether or not to continue to support 2 different versions of EF. I could see that having impact on getting major features like M2M implemented, since the team is spread even thinner. But hopefully EF6 being on continued support isn't that huge of a resource drain. Anyways... I digress, I didn't mean to derail the discussion.

[millikens]

This is a very missed featue. Sorry, real world databases have M2M relationships. They should be easy to deal with and properly supported. Period. I am shocked that this did not make EF Core 3. I just started a new .Net core 3 project and was fully expecting this to be implemented, and, well no documentation, no mention of it and here we are still begging. Please Microsoft, for the sake of us evangelizing your tech make this a THE TOP PRIORITY. Take people off of the ASP.Net team or whatever you need to do to get this done.

.Net is used primarely by businesses, and primarely for data managment of one form or another. Lets get on it!

[ajcvickers]

I was away on vacation when this thread blew up again a few weeks ago. Let me address a few points now that I’m back.

First, nobody on the EF team is happy that many-to-many has not yet been implemented. Let me try to explain some of the background that got us here, not as an excuse, but just to allow everyone to understand the context.

Many-to-many and feature planning

Many-to-many is a high-level feature that builds on much of the underlying architecture and infrastructure of EF Core. In the early days of EF Core these fundamentals were not in place. Also, EF Core early-on was focused on new applications where mapping the join table, while not ideal, is somewhat reasonable. Compared to other features that were missing in the early days (e.g. inheritance mapping, rich query translation, etc.), many-to-many was relatively easy to work around for new applications.

In addition to this, the planning process is not as simple as “do the one with the most votes”. For example, the Cosmos provider was implemented because Cosmos is strategically important to Microsoft. Value converters were implemented because they form a foundational building block for many scenarios. And so on.

That being said, in retrospect it might have been better to implement many-to-many before some of the features that we did implement. But then again it’s very hard to know how that would have turned out. Regardless, as always, we learn from this and attempt to do better going forward.

The EF team

Microsoft is investing in EF Core, but the number of developers allocated to EF Core and EF6 has been dramatically reduced since we started working on EF Core. This reflects changing strategic priorities within Microsoft; investment in EF Core means less investment in other more strategically important areas. We currently have one manager and five developers for all design, implementation, testing, documentation, and most customer interactions, on both EF Core and EF6. This is sufficient investment to keep EF Core moving forward, but not sufficient to implement everything needed for a feature-rich OR/M (at production quality, with testing and documentation) in a timely manner.

As I have said before, the EF team has and will continue to communicate customer feedback up the management chain. It is then a strategic decision whether to increase resources working on EF, as opposed to using those resources for other projects that may potentially have bigger immediate ROI or strategic impact. Currently the management feedback we have received on this is that the current level of resourcing is sufficient, and the relatively slow progress on EF Core that results is acceptable from a strategic perspective.

On the EF team, we will continue to advocate for more resources on EF, and we will continue to do our best to move EF Core forward to the best of our ability.

EF6

EF6 is being brought to .NET Core so that existing applications that use EF6 can be ported to .NET Core without also requiring a port to EF Core. It’s not our intention to encourage new projects to use EF6. That being said, we want you to use whatever is the most appropriate technology to achieve your goals. That might be Dapper, nHibernate, EF6, or anything else. The EF6 codebase is not going to significantly evolve going forward, but it supports many-to-many and if it works well for you, then by all means use it.

EF6 and EF Core will not be merged. As @popcatalin81 explained, the architecture and design principles are fundamentally different.

Are we going to do many-to-many for EF Core “5.0”

(That is the, next major version after 3.1, which currently looks like being branded as EF Core 5.0.)

Unfortunately, this is not a simple “yes”. The main questions are:

• Can we implement both TPT and many-to-many for 5.0? (This would be a stretch, but may be doable.)
• If not, should TPT be done first because it is a low-level feature that may need a provider-breaking release? (Compatibility becomes more and more important with each release.)
• What other strategic investments need to be made that don’t come directly from customer demand. (For example, one EF engineer is likely to spend significant time on performance work at the ADO.NET level.)

We will be working through these questions in the coming weeks.

Yelling at the EF Team doesn’t help

The EF team is dedicated and passionate about making EF Core as good as it can be. We listen to, appreciate, and respect the feedback we get on this and many other issues. Please remember that we are all trying to do the best we can within the constraints of our environments. (By “all”, I mean the EF team, the EF community, and developers using EF in their code.) Attacks directed at the people on the EF team are not in any way helpful in moving anything forward. Please ensure that responses are kept civil and respectful as is covered in our code of conduct.

Speaking personally, I try hard not to take attacks personally, but they can be very disheartening, especially knowing how hard I and the rest of the EF team work. And how much we care. It does make me seriously consider working on something else, although I am resisting that for now. Hopefully its fairly obvious that if the people passionate about EF Core leave the team, then EF Core won’t get better because of it. (It was very sad to see @divega go after 12 years working on EF.)

Thanks,
Arthur

[dragnilar]

@ajcvickers Thanks for taking the time to address this thread, I can see why things are the way they are; I was under the impression that the team was small, so I'm not surprised that is one of the reasons why things may be the way they are.

And yes, I will have to agree, that for where the apps I am working on right now stand (new apps), it doesn't hurt them that much to deal with join tables. Still, I miss the feature but I'm not going to be upset if it takes longer to implement this feature.

And don't take the silly speculation I had about EF6 and EF Core being as anything intended as deriding of you guys. As I implied previously, I greatly respect the work you guys do and appreciate every day being able to use EF with .NET. I wish you guys had more manpower and hope things get better sooner than later. But again, thanks for posting this, and thanks again also for everything you and everyone else does to make the 🦄 work.

Also, I'm sorry to hear about @divega leaving, that has to be a big blow. I didn't hear about that until now. 😢

[ajcvickers]

@dragnilar Thank you for your kind words. We appreciate it very much.

[Kralizek]

Thanks @ajcvickers for the explanation.

Obviously it's sad to read that a team behind such an important product is going through staffing issues.

On the other hand, it would be interesting if your team would really adopt the "development in the open" philosophy and direct the open source effort with a series of work items that could get us there.

[tomasfabian]

I agree with @Kralizek. Rich Microsoft does not have enough resources? The world is full of hard working and smart people, Willling/loving to code. But our destiny is to wait for AspNetCore.OData, EF Core M2M and so on.

[colltoaction]

I've closely followed this thread for years and stopped myself from stepping in countless times. I want to speak up, but I really don't want to stir up more trouble with another opinion.

Instead, I want to propose a way forward. If a reasonable bounty was set up, with contributions from either the users or Microsoft (or both), I would like to tackle this feature. I've never done bountied work before, but I have seen that in other open source collectives.

I think I would be a good candidate because:

• I'm an ex EF Core intern, and I've worked with some of the current team members
• I've experience in implementing an actual feature in this project (Data Seed)
• I've more than 8 years of development experience, with a focus on C# and Java
• I've experience in working in the open
• I've more than 150 merged PRs in https://github.com/rsksmart/rskj in the last two years to back me

Let me know what you think!

Cheers,
Martín.

[ajcvickers]

@Kralizek @tomasfabian We discussed this in the team yesterday. I am already planning to work on a significant amount of documentation that should make it easier for external developers to contribute. Beyond this, we discussed coming up with more detailed documentation on what is needed to implement many-to-many specifically, such that a group of people from the community would have more chance of successfully implementing it.

I didn't include this in my post for two reasons. First, we don't have such guidance written yet and I don't want to promise something that may not happen. Second, whether or not this makes sense depends on what we decide to do for 5.0, and as I mentioned above, the planning for that is going to happen in the coming weeks.

[yepeekai]

Thanks a lot for the explanation. Sorry to hear about divega :(

The way I see it, we are in competition with TPT... I just learned what TPT is and I read a part of the thread. The main problem I see with TPT is that it is "relatively, one of the most expensive and involved features we could take on" so considering you are a small team... I think TPT could block Many to Many and probably many more smaller features...

I am unfortunately not sure it is super easy to open the development of EF core to outside developers...

As for EF6 vs EF core. In my opinion EF core is incredibly better. We have a huge schema and EF core starts way faster and queryexpression preparation is way faster too. So keep on the good work.

[rblackman]

@ajcvickers Thank you for the transparency. It helps a lot to hear about the deliberations and know that the feature isn't just being ignored.

As I have said before, the EF team has and will continue to communicate customer feedback up the management chain. It is then a strategic decision whether to increase resources working on EF, as opposed to using those resources for other projects that may potentially have bigger immediate ROI or strategic impact.

Is there something more productive that all of us could be doing to help convince the higher-ups at Microsoft to give the EF team sufficient resources?

[ajcvickers]

@yepeekai Thanks. To be clear, EF Core is open source, just like EF6 before it, and we have taken many hundreds of community PRs over the years. However, it is has proven hard for external people to implement big or complicated features, hence the need for more documentation.

Many-to-many is not a fundamentally difficult feature to implement, but there are several aspects to it, and it does require work across the whole codebase, which in turn requires a pretty good understanding of how things work internally.

[ajcvickers]

@rblackman I don't know of anything specific you can do, but please continue to provide feedback on whatever channels you have available to you.

[millikens]

@ajcvickers, in going back and reading my comment I realize that it may have been a little stark, and I apologize for that if it was. I think we all appreciate what you guys have done with EF Core so far or we would not be here hitting the pavement! I think when people are being critical, they are looking at Microsoft, the large highly capitalized company, not the team of 5 highly talented developers. I am sure many of us would like to contribute to this feature, but I am also aware that it needs to follow the patterns that you have established, and techniques that you are comfortable with. I know that designing and thinking through a feature is two thirds of the work as well!

I do wish management would re-allocate good people to your team, as I think what you are working on is fundamental to the success of .Net as a whole.

Again, we really do appreciate your work and we use it every day, be it as a consumer using an e-commerce site or a developer building one!

Thank you... and keep us posted on the prioritization of this issue and let us know what we can contribute to help.

[bassebaba]

Microsoft is investing in EF Core, but the number of developers allocated to EF Core and EF6 has been dramatically reduced since we started working on EF Core. This reflects changing strategic priorities within Microsoft; investment in EF Core means less investment in other more strategically important areas

@ajcvickers

Just help me understand.
Storing data is the single most important feature that 98% of my/our applications do.
EF / EF-core is _the most_ important feature in the whole .Net sphere for me/us.
Our #1 bottleneck / source of problems in our apps is always data-storage.

I cannot even begin to understand how it's even possible that EF is not a "top priority".
I would expect the EF-core team to have tens, if not hundreds of members.

So, what to do?
Use another ORM?
Give up SQL and go all in on NOSQL, and hope that MS priorities that tooling more?

I'm not angry, just lost. I need to choose a database strategy that will work today, and tomorrow, for enterprise class applications.

[dragnilar]

@bassebaba I share your concern since from my POV, data is and will continue to be an increasingly important asset for businesses, large and small. In many ways, it makes EF practically the lifeblood of almost any program using .NET. I do hope things get better for @ajcvickers and everyone else though. I feel that their work is just as critical to .NET as what @danroth27, @SteveSandersonMS, @LyalinDotCom, @MadsTorgersen and so many others do to continue to make .NET awesome.

[Serjster]

We can try this:
Go to your Visual Studio, Help, Send Feedback, Suggest a Feature... in the window that pops up, search for "Increase budget for Entity Framework Core dev team", then click on Features and vote for the top result...

[pantonis]

Already commented on thread opened Sergey Sajan Increase budget for Entity Framework core dev team. Please all lets support this thread and hope they hear our voices

[TehWardy]

@ajcvickers is Microsoft open to accepting community PR's in to the EF Core repository?
Until now I wasn't aware this was even an option for corporate control reasons but if it is I might consider this a pet project to get involved with ... hell it wouldn't hurt if I submitted a PR that solved this right?

I'm only asking because if there's literally no way in hell that will ever get accepted then I obviously won't bother otherwise I think I might be able to at least find some time towards getting a few bits in place towards this goal even if I can't complete it.

Disclaimer ...
I obviously share the frustration here, but am as an external third party highly invested in seeing EF Core succeed and willing to do all that I can to help make it succeed despite Microsoft's internal budgetary / resourcing limitations as a gesture of good will.

With this post comes no promises I will actually deliver anything at this time.

[lloy0076]

Were I to design an SQL course using a respectable SQL server (such as Microsoft SQL Server or these days, MySQL), I'd not miss out many-to-many relationships. They're just too common...even in a TODO application with multiple users or chat application they're applicable.

I get that there are difficulties under the surface to make other things work but a simple:

MyModel->belongsToMany(ofThem) and themModel->belongsToMany(ofMyModels), I still cannot see what is so difficult about making those bindings (you could, as a horrid hack, intercept the actual AST and just build some defaults - the example code to make N-N work is almost completely templateable to the extent where even the C preprocessor itself could probably manage it...).

I'm not yelling.

I'm just wondering how one of the most basic relationships that would be taught to any competent junior school developer is just ... missing even in the most basic format.

[TehWardy]

Honestly @lloy0076 from what little perusing i have actually done of the EF Core framework codebase I can safely say there's way more to it than that.

In order to make that sort of magic happen there's a ton of supporting infrastructure that needs to be put in place first.
As has been touched upon i'm sure in many other places and likely somewhere in this thread too, it's not a simple matter of adding a single method in to an existing model builder class but more a task understanding what impact that actually has when the relationship is crawled to the model, the query parser, and any abstractions (e.g. the many database providers that are supported, and any polymorphism internally in the framework itself) that may have taken place in order to make the call possible in the first place.

I doubt very much it's trivial but that said I agree it's a "basic taken for granted scenario" that should be supported by an ORM on a relational database.

[roji]

For the benefit of some newcomers who may not be aware, it's worth restating that many-to-many relationships are fully supported by EF Core. This issue is about supporting many-to-many without a CLR class to represent the join table, which is a more convenient way to do things but in most cases should not be blocking.

Just to make sure we're all talking about the same thing.

[pantonis]

@roji I don't know if you ever worked in large enterprise systems but the workaround creates a total mess both for development and for maintenance. Stating that there is a workaround I think creates wrong assumptions. For systems with bugs there are workarounds but they are fixed sooner or later and the bugs are not there infinitely. Just to let you know I have a system with dozens of M2M and believe me it is a struggle to work with them. Unecessary includes all over the place etc...

[roji]

@pantonis I understand and agree - and am not trying to downplay the importance of the issue. Some comments simply seemed to indicate that some users think EF Core doesn't support many-to-many at all, which isn't the case, and would be a much more serious issue.

[Daniel15]

Some comments simply seemed to indicate that some users think EF Core doesn't support many-to-many at all

How could any ORM not support it though? The workaround is just using two one-to-many relationships.

[lloy0076]

There is more to it than that if you want advanced features to "work out of the box"; however, the workaround is as simple as:

1. Generate the separate classes - which is template-able;
2. Add the relevant bit in the context - which is template-able.

Advanced features may or may not work. If they don't work with the current"solution" they won't work with what I am suggesting.

I am suggesting that the _work around itself_ is easy enough to code - either on the fly or something like:

% dotnet create_many_many_pivot_for ClassOne ClassTwo

That literally spits out the code using some conventions (which we can fight about - should the pivot names be foo_id or fooId and so forth).

I'm not suggesting my suggestion would be a complete solution but it could help reduce the number of includes, boiler plate _or_ at least generate the boiler plate...

Honestly @lloy0076 from what little perusing i have actually done of the EF Core framework codebase I can safely say there's way more to it than that.

[ajcvickers]

Many thanks for the kind works from many of you. It is much appreciated.

@lloy0076 Generating the boilerplate code isn't really the issue being discussed here; yes this is not hard--reverse engineering from an existing database already does this. The point of this issue, as @roji said, is many-to-many _without using a join table in your code_.

I wrote a four-part blog series some time ago about workarounds to many-to-many and the challenges involved. I haven't looked at it for a while, but I think it is still relevant.

[pantonis]

@ajcvickers. The big problem here is not the many to many complexity issue, but the fact that Microsoft "abandoned" EF Core by minimizing the staff working on it. I know a team of 6 cannot make miracles and It would be good to know the intentions on EF Core as lots of money were invested by my company and I suppose from other enterprises as well to build systems using EF Core

[Atulin]

@lloy0076 There's more to the workaround than just generating a join class. If that was it, it'd be all well and good, really. But the issue is, you can't just

class Article {
  public List<Tag> Tags { get; set; }
}

and access it via

var articles = await _ctx.Articles
    .Include(a => a.Tags)
    .ToListAsync();
// ...
var tag = articles[0].Tags[0].Name;

but you need either

class Article {
  public ICollection<ArticleTags>ArticleTags { get; set; }
}

accessed via

var articles = await _ctx.Articles
    .Include(a => a.ArticleTags)
    .ThenInclude(at => at.Tag)
    .ToListAsync();
// ...
var tag = articles[0].ArticleTags[0].Tag.Name;

or

class Article {
  public ICollection<ArticleTags>ArticleTags { get; set; }
  public IEnumerable<Tag>Tags => ArticleTags.Select(at => at.Tag).ToList();
}

accessed via

var articles = await _ctx.Articles
    .Include(a => a.ArticleTags)
    .ThenInclude(at => at.Tag)
    .ToListAsync();
// ...
var tag = articles[0].Tags[0].Name;

which is not only unnecessary boilerplate and/or unnecessary use of LINQ, but it also breaks the natural semantics of "an article has tags" making it "an article has articletags, and each articletag has a tag". It's also something that cannot be generated by dotnet fix-my-shit.

There's also the potential for circular references, since ArticleTag contains both a reference to an article and a reference to a tag.

[jzabroski]

It's also something that cannot be generated by dotnet fix-my-shit.

I am not trying to be funny when I say I can actually generate that boilerplate for you with an expression tree rewriting machine.

[Atulin]

Interesting. I guess generating

class Article {
  public ICollection<ArticleTags>ArticleTags { get; set; }
  public IEnumerable<Tag>Tags => ArticleTags.Select(at => at.Tag).ToList();
}

when generating entities wouldn't be an outlandish idea, but can

var articles = await _ctx.Articles
    .Include(a => a.ArticleTags)
    .ThenInclude(at => at.Tag)
    .ToListAsync();

be generated as well, anywhere in the code? Not like it's a lot to write, but still.

[lloy0076]

It's also something that cannot be generated by dotnet fix-my-shit.

I am not trying to be funny when I say I can actually generate that boilerplate for you with an expression tree rewriting machine.

Aye, I was being a tad grumpy when I was saying that even the elder C preprocessor could probably generate the code but it's not as though C# isn't able to generate code (otherwise how the world does it even manage to compile to the intermediary format...)

[TehWardy]

@Atulin you're getting way ahead of yourself, as I said above it matters what the underlying implementation is doing first before we can talk what API calls exposes.

@roji most M2M types are supported yes.
Earlier in this thread I highlighted and even linked to a currently unsolvable case where entities can be related but neither is the parent or child they are simply related to one another.

@lloy0076 I don't think generating the classes that represent the underlying structure is the issue here, I think it goes deeper and requires more complexity due to the nature of the EF provider model that results it having to do different things in different situations based on the db type it's actually sitting on.

Translating an expression tree in to a SQL query for polymorphic or dynamic data types is quite a complex task and to have it work in a way that's both fast and supports all the potential scenarios sat on top of the model means you're limited in the number solutions you can actually pick from.

[lloy0076]

It's not the exact issue - but for a non-polymorphic, bog standard N-N, the code is completely able to be generated and in the world that I live in, the more that the computers can do, the more I can do things that humans do better.

I shouldn't have to stare at the docs page to figure out how to implement what is probably the third relationship taught in a basic SQL course (I'd put 1-N, 1-1 first).

That said, as I've also mentioned above, NHibernate suits my purpose so I'm not sure why I'm invested so much in this discussion. My actual point is:

• Make the simple things simple (*);
• Make the difficult things possible;
• Make the almost impossible things, almost possible.

(*) I argue that the standard N-N, no polymorphism is simple. If a tool makes a simple thing complex, it's hard to trust that it will make complex things possible.

All I'm trying to point out is that the boilerplate code on the EF Core documentation site is completely computer generatable - and it will have the same issues generated _or not_. So why not generate the simplest case so people can concentrate on the more difficult cases?

[Let me take a guess: lack of resources, a barrier to open source contributions etc etc]

@lloy0076 I don't think generating the classes that represent the underlying structure is the issue here, I think it goes deeper and requires more complexity due to the nature of the EF provider model that results it having to do different things in different situations based on the db type it's actually sitting on.

[TehWardy]

@lloy0076 With all due respect here I think you're over simplifying the problem because "it's been solved in other frameworks", that's the wrong way to look at the problem at hand.

Whatever the solution it needs to be in-keeping with the rest of the framework around it and allow for the complexity that the full breadth of EF Core allows for (context is important).

NHibernate is not a fair comparison, compare it to EF6 for a fairer comparison, it comes with the same age baggage and effort poured in, EF Core is a complete rethink from the ground up and has a fraction of the man hours put in so is immature by comparison.

[millikens]

@lloy0076, I appreciate your efforts to help come up with a solution, but auto generating boilerplate code is not exactly the solution some of us are looking for. The issue is not creating creating a simple class for joins and configuring the context appropriately. This is done once and takes 10 or 15 minutes if you have experience. The real issue is the added complexity of the data model and all of extra code you need to include in your business logic/repo classes as a result.

For this reason I/we are wanting a well thought out solution that helps us reduce the complexity (read bloat) of our domain logic.

In my case the current project I am starting has quite a few of these relations and like you I am going to opt for nHobernate most likely. I must say though that EF Core is much easier to configure. I used EF Core on my last project and it was great, easy to configure, etc. I will definitely use EF Core in the future and if timing permits perhaps switch over with this project.

I usually confine my data access to a repository layer so it will be easier to switch at some point if necessary.

[AlseinX]

So, will many-to-many relationship come with 3.1 LTS?
It would be a significant fixing to make EF Core usable for production in fact.

[AndriySvyryd]

@AlseinX 3.1 is stabilizing release, no new features will be added. Watch #10508 for updates from the EF team.

[NetMage]

@AndriySvyryd Doesn't seem like the EF Team is updating much.

[AndriySvyryd]

@NetMage That feature is still in backlog. Currently we are working on https://github.com/dotnet/efcore/issues/19003

You can see all updates from the team on https://github.com/dotnet/efcore/issues/19549

[NotloeHsung]

I have just created a many to many relationship with EF core 3.1.2. An index is created, but only for one of the PK columns in the mapping table.
Shouldn't an index be created for the other column, or an index for both, as with the PK?
Via modelbuilder i used only HasKey with the 2 columns to declare the PK. No difference, when declare the relationship (HasOne WithMany...) additional by fluent api.
example from: https://www.learnentityframeworkcore.com/configuration/many-to-many-relationship-configuration

[Serjster]

You could always do this by manually declaring the intermediate table's class, then having weird navigation stuff, with having to do stuff like this: thing.IntermediateTable.ActualThingThatShouldBeThereInsteadOfIntermediateTable
which typically looks like this:
student.StudentClass.Classes ... that StudentClass should exist in SQL but not in code.

[NotloeHsung]

i'm familiar with that from ef6, but without any fluent api declaration in ef core, specified by List<student> property in "class" and List<class> in student it generates a one to many relationship, no join table.
https://docs.microsoft.com/en-us/ef/core/modeling/relationships?tabs=fluent-api%2Cfluent-api-simple-key%2Csimple-key#other-relationship-patterns still not supported without the join table. ok, I just wanted to ask about the index that is not created across both columns, when using the join table.

[AndriySvyryd]

@NotloeHsung An index is not created when the column is already covered by another index or in this case a composite PK. However it only covers one of the columns even though it's defined on both, so an additional index is created for the other one, see Indexing Documentation

[bmartin-outmatch]

I would present a perspective here. EF Core does not support many-to-many relationships. Relations are supported in EF by navigational properties. EF Core does not have a means of defining many-to-many navigational properties. Adding a CLR intermediate class that represents the associative and defining two one-many-properties is a work around, but it's not supporting many-to-many relationships. It's an excuse to assign a lower priority to it.
The aim of an ORM is to map your object oriented structure to a relational database structure. You would typically not define an intermediate class. The fact that you have to in order to load your data means that your database definition is dictating your object model. This is bad. Please quit saying that EF Core supports many-to-many relationships. It does not. A workaround is a means to fill the gap for a missing feature. It is NOT the missing feature. It's 2020: this has been going on for more than 5 years and this "feature" is a fundamental component of an ORM. Let's call it what it is.
I love EF, by the way. I'm frustrated that this hasn't gotten the attention it deserves.

[vslee]

@bmartin-outmatch please read the rest to this thread. You'll see that they are actively working on many-to-many

[Atulin]

you'll see that they are actively working on many-to-many

Yes, very actively... Last update to #10508 was 24th of June 2019, and it was about a contributor removing their assignment.

[vslee]

Check out #19003 and #19549

[ajcvickers]

@Atulin A quick scan through recent PRs shows https://github.com/dotnet/efcore/pull/19660 from Jan 30, which is directly about many-to-many. Other work is also currently ongoing. It might be helpful to follow the EF weekly updates for status or look at the EF Core 5 plan.

[Atulin]

Then perhaps it would be proper to either edit this issue to reflect where one can find info about N-N progress, or to keep updating the issue I referenced. That would avoid any future confusion.

[Perustaja]

If you look at the weekly updates, PRs, and that plan it seems they are taking this topic seriously. I think the problem is more on Microsoft's end for their lack of support for EF Core in terms of manpower. There is a lack of proprietary documentation on actually _working_ with the join tables currently. 5.0 looks good, Microsoft just needs to prioritize EF Core more.

[TehWardy]

I don't think they should bother now ... to many people have already done the hard work to handle this for them. There's way more important stuff like inserts, grouping, FK handling, ect that need a ton of core fixes to make it work with OData again properly and other EF non core scenarios that used to work.

[leonardoporro]

If you want a model that is really database agnostic (both SQL and NoSQL), this feature needs to be implemented.
Table (Clr class) joining other tables (Clr classes) is a pure SQL concept and is not natural to in-memory models.
The joining table (Clr class) looks weird, pollutes the model and the queries, and forces to add a mapping layer on top of EF, when EF is a mapping layer itself.
I should be able to persist my model to a SQL, NoSQL, Text file or whatever database only by changing/configuring the ORM.
Migrating an EF model (code-first, of course) to EF Core is really painful because of this joining table.
IMHO, this feature is really important to make EF a true ORM. OData and other features mentioned before are secondary (maybe I want GraphQL?), Also, EF6 (and NHibernate too) supports it very well, its a really basic and useful feature that was supported 5 years ago and now it's gone.
Sorry for my English, and thanks for reading my complain.

[TehWardy]

I agree ... I often mention OData in ORM conversations not because it's a particular technology stack but more because OData is one of those things that for ORM's pushes the feature set forcing you to use everything in a relational data model.

Regardless of weather we have proxies / abstractions around weather or not a join table exists in the code model / db the system will still function, but basic stuff like inserts not correctly managing keys and grouping not working are far more important IMHO.

My point was simply, people are now over this hurdle and more core features are in a worse shape and until Microsoft takes this framework seriously I think the EF team should be working on the most core features not this optional one that already has documented and implemented work arounds that people are only going to have to undo again when this lands.

[Perustaja]

I somewhat agree and somewhat disagree. There are still some issues preventing EF Core from behaving like a true ORM. The reason people use it is because they nailed a lot of the QOL aspects, however there are still some standard-issue ORM capabilities lacking. It's a bit odd that they have some features that seem kind of "final-polishing" that make you all happy, then you look up some basic functionality you would expect and see yet another multi-year issue. Core issues like this need to be addressed first (as they are).

[przemo098]

How long do we have to wait for this ?!
It's 5 years...

[AlseinX]

If you want a model that is really database agnostic (both SQL and NoSQL), this feature needs to be implemented.
Table (Clr class) joining other tables (Clr classes) is a pure SQL concept and is not natural to in-memory models.
The joining table (Clr class) looks weird, pollutes the model and the queries, and forces to add a mapping layer on top of EF, when EF is a mapping layer itself.
I should be able to persist my model to a SQL, NoSQL, Text file or whatever database only by changing/configuring the ORM.
Migrating an EF model (code-first, of course) to EF Core is really painful because of this joining table.
IMHO, this feature is really important to make EF a true ORM. OData and other features mentioned before are secondary (maybe I want GraphQL?), Also, EF6 (and NHibernate too) supports it very well, its a really basic and useful feature that was supported 5 years ago and now it's gone.
Sorry for my English, and thanks for reading my complain.

That must by why EF6 is migrated to .NET Core, for there must be an usable official supported ORM framework in the world of .NET Core.

[pikausp]

Since it's marked here as on the backlog, I'm assuming 6.0 is the earliest we might see this feature?

[dandrejvv]

@pikausp, its comparing EntityFramework 6.0/6.4 (old .NET) with EF Core (latest version found: 5.0).

[pikausp]

@dandrejvv Yes, and for EF core it states on the backlog as opposed to other aspects being planned for 5.0.

[Aleksej-Shherbak]

I came in .NET from PHP and Laravel world. Of course, Laravel's ORM (Eloquent ORM) supports this feature by default. Can you imagine, how I was frustrated when I saw what is happening with many-to-many in EF? So serious platform - .NET Core - direct Java and Spring competitor (maybe even killer in the nearest future, who knows ...) and don't have so simple feature for main ORM. I don't understand why( I love C#. Microsoft, please, I beg you! Make your platform (.NET Core and infrastructure around) the best in the world! And ORM too! I'm really waiting this feature!

[alainkaiser]

This is still not supported by ef core? Damn! 👎

[Atulin]

@Aleksej-Shherbak I came from Symfony, and Doctrine ORM. Honestly, EF Core isn't half of what Doctrine is. I get caught on "wait, this should've been much easier" way too often for my liking.

[Aleksej-Shherbak]

@Atulin strangest and unfriendly thing from my point of view is not supporting of many-to-many. I found this hack https://medium.com/@ti.ka/many-to-many-relationship-done-right-in-the-entity-framework-multi-clients-users-130ac185f667 It can save my client code (I'm talking about the code, where I work with db context) from ugly mapping with pivot C# class. But, of course, it not protects me from creating of this class.

[Xawx]

@Aleksej-Shherbak

How is this a hack? That's how it is defined in an actual database - join table.

[Kroshtandt]

++1 waiting for many-to-many integration without declaring intermediate cross table

[Aleksej-Shherbak]

@Xawx the "hack" is

    [NotMapped]
    public IEnumerable<Client> Clients
    {
        get => UserClients.Select(r => r.Client);
        set => UserClients = value.Select(v => new UserClient()
        {
            ClientId = v.Id
        }).ToList();
    }

in the model. With the help of this I can write something like

user.Clients = clients;

where clients is collection (List for example). Like I have many to many. Did you read the article from my previous message?

[TehWardy]

@Aleksej-Shherbak will that work with something like OData though that generates a SQL executed expression tree on the database structure?

[Aleksej-Shherbak]

@TehWardy I'm not sure. I think it works only for very simple cases. It is useful if you want just hide mapping with pivot class. UserClient in this case.

[TehWardy]

That's why I need this issue fixed, for now i've just exposed the join tables as entities to the API so that it can correctly construct the right expression trees based on the actual DB structure.

That's given me an idea.

[JustoSenka]

Please fix this now! I spent hours, assumed this fundamental functionality just works out of the box.. Apparently not. Join tables are ugly and unwanted.

Read some older comments, this issue was a "high priority" feature request. It's been 5 years..

[ctolkien]

For people still following along - this is scheduled for the 5.0 release (due Septemberish Novemberish).

https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-5.0/plan#many-to-many-navigation-properties-aka-skip-navigations

[ajcvickers]

@ctolkien EF Core 5.0 will release in November at the same time as .NET 5.

[RyanJMcGowan]

As of (legacy?) Entity Framework 6.3, you can go back to the mature Entity Framework API inside a Core project, with complete mappings that we're all used to! I hadn't been watching the legacy EF development, and thought it was dead. This might be the best "workaround" if migrating back to EF6 isn't too difficult, until EF Core is really done. There's still more EF6 in the wild than EF Core, so it's going to be supported for a long time to come. Food for thought for anyone following this issue.

It sounds like the November release is still requiring manual modeling the relationship table, and the feature coming down the pipe is just supporting queries without having to reference the join table. This is really disappointing if true. I hope I'm reading the announcement wrong.
Entity Framework is an Object _Relational_ Mapper, after all. This is why people use it. Also, we were told it would be in 3.0, so it wouldn't be wise to plan around the announcements. Don't count your chickens until they're mapped many-to-many! Until then, maybe take a look at EF 6.3.

[alojoshka]

It depends of the team) Great job the Typescript, can't program vanilla js
any more, great job VsCode- industry standard everywhere, and total failure
of EF Core, as well as many Core versions of (why should I use this Core
...1...2....3...)

On Sun, May 24, 2020 at 7:07 AM RyanJMcGowan [e-mail]
wrote:

As of (legacy?) Entity Framework 6.3, you can go back to the mature Entity
Framework API inside a Core project, with complete mappings that we're all
used to! I hadn't been watching the legacy EF development, and thought it
was dead. This might be the best "workaround" if migrating back to EF6
isn't too difficult, until EF Core is really done. There's still more EF6
in the wild than EF Core, so it's going to be supported for a long time to
come. Food for thought for anyone following this issue.

It sounds like the November release is still requiring manual modeling the
relationship table, and the feature coming down the pipe is just supporting
queries without having to reference the join table. This is really
disappointing if true. I hope I'm reading the announcement wrong.
Entity Framework is an Object Relational Mapper, after all. This is why
people use it. Also, we were told it would be in 3.0, so it wouldn't be
wise to plan around the announcements. Don't count your chickens until
they're mapped many-to-many! Until then, maybe take a look at EF 6.3.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/dotnet/efcore/issues/1368#issuecomment-633175940, or
unsubscribe
https://github.com/notifications/unsubscribe-auth/AFOSOYODDY4R5AQMG66HRULRTCMQDANCNFSM4AZ6WA5Q
.

[tomasfabian]

I've been waiting for this patiently for a long time ManyToMany. Is this for real? Thank you EF Core Team!

[pantonis]

Any update on this? In which .NET 5 preview is gonna be available?

[AndriySvyryd]

We are working on several pieces of this. The whole feature won't be available at least until preview9.

[sean-thorburn]

I'm coming from EF 6 to EF Core. Realising that this feature still not implemented is disturbing.

[OneGenkiGirl73]

Same here, I'm coming from EF6 to EF Core also -- Could someone put an example up on GitHub and link to it if they've been able to work it out ?

[Atulin]

The current way to "work it out" is explicit join entities, or adding the N-N functionality yourself.

[HunteRoi]

This topic is still discussed after 5 years. We don't want more discussion, we want to know when it is going to be implemented. Anyone who could answer this?

[roji]

@HunteRoi this is being released with EF Core 5.0 (e.g. https://github.com/dotnet/efcore/issues/1368#issuecomment-650515304).

[gojanpaolo]

@HunteRoi

Issues #10508 and #19003 have been created to track actual work on many-to-many relationships

Those issues are tagged for 5.0.0 which is planned to be released on November 2020

[haydenmc]

We are working on several pieces of this. The whole feature won't be available at least until preview9.

The latest .NET 5 Preview blog post indicates that after preview 8, there will be two release candidates before release.

Should we expect this feature to be available in one of these release candidates?

[AndriySvyryd]

@haydenmc preview8 will contain most of the functionality and the next release should further improve the experience.

[nmocruz]

good to see that, looking forward to see this in the next release :)
https://github.com/dotnet/efcore/issues/19549

[leus]

Yay, finally! https://github.com/dotnet/efcore/issues/19549#issuecomment-663237077

[ajcvickers]

To add to what @leus said, 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.

[leus]

I like that, at the context level, you don't even need to create two entity sets, but one (note that there is no DbSet<Tag> definition here.) Is that correct?

public class BlogContext : DbContext
{
    public DbSet<Post> Posts { get; set; }
}

[ajcvickers]

@leus Yes; Tag is discovered as an entity type through it's relation to Post.

[TehWardy]

When I migrate my existing codebase that doesn't have this to .Net 5 please tell me it'll still work?
If I have to rebuild all my contexts again I'm going to go crazy.

[Ciantic]

First of all, creating the connecting tables automatically is big 👍 however creating "top-level" tables automatically might be a bit confusing.

I like that, at the context level, you don't even need to create two entity sets, but one (note that there is no DbSet definition here.) Is that correct?

public class BlogContext : DbContext
{
    public DbSet<Post> Posts { get; set; }
}

So to recap, this still creates three database tables: Post, Tag, PostTag. I get the PostTag is magically created table if Post and Tag is connected, but having Tag table appear out of nowhere might be unwanted.

Imagine having big database, now you can't figure roughly what tables it creates from a DbContext.

[bachratyg]

First of all, creating the connecting tables automatically is big 👍 however creating "top-level" tables automatically might be a bit confusing.

This is the behavior we have been used to since the very early days of EF. DbSets are only roots to the entity graph and all entities that can be discovered through navigations are added. You can choose then to define query roots only or all tables in your db at will. I usually start with high level concepts and add new DbSets when necessary e.g. for perf requirements.

Imagine having big database, now you can't figure roughly what tables it creates from a DbContext.

Migrations or context.Database.GenerateCreateScript() will tell you exactly what would be created.

[ajcvickers]

@TehWardy It should be non-breaking to existing code.

[TehWardy]

@TehWardy It should be non-breaking to existing code.

Thank you tank you thank you!
One less pain to suffer, much appreciated.

[iwis]

Thank you tank you thank you!
One less pain to suffer, much appreciated.

What other .NET elements cause pain? Knowing this would be useful to anyone who is wondering whether to choose C# or another programming language. And Microsoft could declare whether it wants to fix them or not. I am asking this because Microsoft is known to cause pain on purpose, for example to anyone who wants to use a database other than SQL Server. Many people are surely wondering if it is still practiced.

[Atulin]

@iwis Far as I can tell, .NET doesn't have many pains. Mostly just EF Core being underdeveloped (N-N being added will alleviate that a lot) and a lack of any official cross-platform UI framework.

[Aleksej-Shherbak]

About pain with EF. Is it possible to stop keeping ApplicationDbContextModelSnapshot (database snapshot)? For me, it's a source of pain. Because, it can make conflicts. For example, programmer A works with EF and Models, programmer B works with EF and Models. Both programmers make migrations and update local database. And it can lead to conflicts during the merge in a VC system. Because the same file was changed (I mean database snapshot).

I don't know, but from my point of view, would be convenient and more robust the following behavior. I have changed my Model (added one field), then I make new one migration. EF looks on the Model class and on the database directly. It can see the new one field. And EF makes me a new migration with the field. So, all my Models are database snapshot, not the special file (usually it calls like 'ApplicationDbContextModelSnapshot'). Is it possible?

[jzabroski]

First off, .NET is a really amazing product and the main reason I can see not to choose it is ramp up time to become an expert and super super productive. Ive got 12 years under my belt and have curated a ton of knowledge, so building applications for .NET has become much easier for me over time. If you're young and reading this, pick something you enjoy and, as you learn, try to keep a portfolio so you can benchmark yourself and get better and better. I started doing this ~4 years ago and I've grown a lot as a result. By contrast, you can get a lot more success a lot more faster using Python and machine learning... but your skillset will be narrower.

I think the main pain points in .NET are:

1. EFCore code-first fluent API has subtle breaking behavior release to release due to regression tests only covering what people report using. (This may finally be resolved now if that API is relatively feature complete, but this was the number one reason I stopped using EFCore and instead just use EF6.)
2. The back-and-forth handling of whether to split queries and what behavior to choose when the query is not translatable.
3. Using proxies incredibly slow down programs

I am optimistic that the EF team is going to create a great product. I also can't wait to try EFCore 5.0. But I use EF6 and see myself using EF6 for a very long time. EFCore simply seems to want to be the ultimate data access tool to all kinds of databases, and I don't need it to be that. I just need it to model SQL Server. EF6 does that.

[TehWardy]

EF Core code-first fluent API has subtle breaking behaviour release to release

It's not just the fluent API ... it's the entire API, a big frustration for sure, but the EF team do document the changes which helps.
The price of progress I guess!

The back-and-forth handling of whether to split queries and what behaviour to choose when the query is not translatable.

Yeh this lost me too ... I have a funny feeling this is solved now at least.

Using proxies incredibly slow down programs

There's a reflection cost for generating and consuming proxies ... I just turned them off completely and turned off lazy loading too.
doing this globally means that my code is a ton cleaner and forces a developer to ask for what they want the first time instead of getting in to 0(n) scenarios.

EFCore simply seems to want to be the ultimate data access tool to all kinds of databases, and I don't need it to be that. I just need it to model SQL Server. EF6 does that.

The original concept for EF was basically that, but it fell short, hopefully the EF Core rebuild will ultimately lead to that.
some of us would like to use more than one DB technology as some technologies are better at different tasks.

@iwis

What other .NET elements cause pain?

• Each new version of EF brings breaking changes some result in loss of functionality (the current version for example lost group by support) as well as this ticket.
  
  
  
  • Saving changes to a graph (lots of related entities in one hit) has resulted in a problem I can't clearly explain to the EF team and so it can't be solved.
  
  
  • Key management is confusing as all hell.
    
    
    
    
    
    • The exceptions I get for this are usually "the framework screwed up, fix it"
    
    
    
    • Guids are inconsistent, and even seeded ints sometimes result in "temporary key" issues for really odd reasons.
    
    
    
  
  
  • the tools we used to get for free now either have to be installed separately in to VS or in to the project itself (i don't like adding deps in my project on the tools used to build it)
  
  
  • Still can't use aggregation functions in OData because of a limitation in EF Core which didn't exist in EF6
  
  

Non EF stuff ...

• Azure Functions seems to be incompatible with everything.
• There's no consistency in the features supported by the OData team and the EF team leaving us to figure out the gaps.
• EF6 despite being a horrible mess of query generation is feature rich and just behaves properly all the time compared to EF core which seems to do odd things in complex scenarios.
• with aspnet core CORS seems to break every time google updates chrome. "on some calls"
• The gaps between teams mean sometimes issues are hard to pin down to the right team.
• Microsofts team structure and resourcing changes basically every year depending on it's objectives so even if the team behind EF are awesome they seemingly sometimes get no support themselves or limited resource to actually solve stuff.

Then when you get to deployments and have to deal with Azure ...

• The Azure Portal UI doesn't play nice with Azure DevOps and is a crater of dispair unless you have time to go digging.
• The UI for configuring pipelines is inconsistent (e.g. some relative paths some absolute)

However
There's no way in hell i'm switching to Node because JS has no business being on a server or Java because well have you seen java? or PHP because despite it's popularity the only way to make PHP good is to do a Facebook and rebuild it completely.

The .Net stack, whilst it has it's issues is still the best out there, and the bulk of my complaints boil down to complex business logic situations and EF Core being an immature product (which essentially means, it's issues will go away over time).

Knowing what I know now since migrating to .Net Core I think I would have advised people to use .Net 4 until 5 get's released. That said with the lead times and getting up to speed a decent sized project started today would be about ready for a QA cycle around the time .Net 5 gets released and EF is supposed to be close to feature complete with it at that time (as I understand things).

The bottom line
I can't complain too much, the teams are awesome and really try and I have had a career on this stack for 20 years which I still don't regret. Microsoft just need better inter-team QA process.

[Aleksej-Shherbak]

One question about new Many-to-Many. Can I set name for the pivot table manually?

[ajcvickers]

@Aleksej-Shherbak Yes, the table mapping can be specified just as for any other table.

[simon-curtis]

In case anyone missed it Entity Framework Community Standup - Many-to-many in EF Core 5.0

[Saibamen]

In case anyone missed it Entity Framework Community Standup - Many-to-many in EF Core 5.0

Or directly into demo part (10:28): https://www.youtube.com/watch?v=W1sxepfIMRM&t=628s

And I also suggest to set play speed at 1.25x

[Atulin]

The ASP.NET repo is probably a better place to ask this question, but is it possible that ASP internals will start using N-N as well? For example for user roles in Identity and such?

[raffaeler]

The ASP.NET repo is probably a better place to ask this question, but is it possible that ASP internals will start using N-N as well? For example for user roles in Identity and such?

@Atulin why do you care?

[Atulin]

Because using User.Roles would make my life easier than User.UserRoles.Select(ur => ur.Role)

[raffaeler]

You don't need that, you can just derive the user class and add a property like this:

public IQueryable<UserRole> Roles => this.UserRoles.Select(ur => ur.Role);

[luizfbicalho]

I strongly agree with @Atulin , i like my model as abstract from the database as possible, no FK in the class, no many to many relationship classes, just plain model classes with the properties that the model need to expose

and the @raffaeler solution doesn't solve the add or remove of a role from the roles properties

I expect a new model cleaner than the actual one, and with the nullable reference properties defined in class

[raffaeler]

The user/role model is tied to the ASP.NET Core infrastructure. For this reason it is better to keep user data in separate entities or even separate database. Otherwise the model would be meaningless outside ASP.NET Core.
For the purpose of updating (my model, not user/roles) I created a query provider to fix the queries, but I am not suggesting to do that.
I just believe this is not a big issue for any model as I would never take any dependencies on infrastucture classes.

In any case, I just don't think that it is valuable for the ASP.NET Core team to modify the structure (with the risk of breaking those who customized the model and are using the intermediate entity).

[iWangJiaxiang]

You don't need that, you can just derive the user class and add a property like this:

public IQueryable<UserRole> Roles => this.UserRoles.Select(ur => ur.Role);

Hi, should the return type be IQueryable<> or IEnumerable<>?

[raffaeler]

@iWangJiaxiang it mostly depends how you will use it
Say, for example, you want to use a Where or any other query operator after UserRoles:

myUser.Roles.Where(r => r.,,,)

• if you declare Roles as IEnumerable the Roles will be all loaded in memory and the following query will be executed in memory
• if instead it is declared as IQueryable, it will be part of the query composed for the database provider you are using

In the specific case of the Roles, my guess is that it won't make any difference as normally apps use a low number of Roles.

Please note that you can always decide to "break" the server-side query with AsEnumerable extension method

[vslee]

I see that basic documentation has already been written in https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-5.0/whatsnew as well as the blog post https://devblogs.microsoft.com/dotnet/announcing-entity-framework-core-efcore-5-0-rc1/. Is it possible to add some documentation on how to have a collection of Ids? Such as:

```C#
public class Post
{
public int Id { get; set; }
public string Name { get; set; }
public ICollection Tags { get; set; }
public ICollection TagIds { get; set; }
}

public class Tag
{
public int Id { get; set; }
public string Text { get; set; }
public ICollection Posts { get; set; }
public ICollection PostIds { get; set; }
}
```

The main things I would be interested in seeing in the documentation are:

• Whether the Id collection gets automatically picked up by EF
• What the naming requirements are for the Id collection
• an example of how to set things up with Fluent instead
• Whether the Posts and PostIds need to be eagerly loaded prior to use

[AndriySvyryd]

@vslee We don't have any plans to support this pattern.

[jzabroski]

@AndriySvyryd Is there a ticket in the backlog for it? It seems like that pattern would allow some join optimizations under certain conditions.

[AndriySvyryd]

@jzabroski No, feel free to open one and include the expected schema and query if possible.