Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Discussion on many-to-Many relationships (without CLR class for join table) #1368

Closed
0xdeafcafe opened this issue Jan 8, 2015 · 469 comments
Closed

Comments

@0xdeafcafe
Copy link
Contributor

@0xdeafcafe 0xdeafcafe commented Jan 8, 2015

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
Copy link
Member

@bricelam bricelam commented Jan 8, 2015

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

class Product
{
    public int Id { get; set; }
    public ICollection<Categorization> 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<Categorization> Categorizations { get; set; }
}

To navigate, use a Select:

// product.Categories
var categories = product.Categorizations.Select(c => c.Category);
@0xdeafcafe
Copy link
Contributor Author

@0xdeafcafe 0xdeafcafe commented Jan 8, 2015

Thanks!

@0xdeafcafe 0xdeafcafe closed this Jan 8, 2015
@tonysneed
Copy link

@tonysneed tonysneed commented Jan 20, 2015

@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
Copy link
Member

@bricelam bricelam commented Jan 20, 2015

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.

@bricelam bricelam reopened this Jan 20, 2015
@divega
Copy link

@divega divega commented Jan 20, 2015

@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
Copy link

@tonysneed tonysneed commented Jan 20, 2015

@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
Copy link

@tonysneed tonysneed commented Jan 21, 2015

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

@rowanmiller rowanmiller changed the title Many-to-Many relationships Many-to-Many relationships (without CLR class for join table) Jan 23, 2015
@rowanmiller rowanmiller added this to the Backlog milestone Jan 23, 2015
@ctolkien
Copy link

@ctolkien ctolkien commented Jul 27, 2015

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
Copy link

@damccull damccull commented Jul 27, 2015

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
Copy link

@popcatalin81 popcatalin81 commented Jul 28, 2015

@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
Copy link
Contributor

@rowanmiller rowanmiller commented Jul 29, 2015

@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
Copy link
Contributor

@lajones lajones commented Oct 6, 2015

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
Copy link

@bragma bragma commented Nov 3, 2015

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
Copy link
Member

@mjrousos mjrousos commented Nov 19, 2015

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
Copy link

@HerbertBodner HerbertBodner commented Dec 25, 2015

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

@sthiakos
Copy link

@sthiakos sthiakos commented Dec 25, 2015

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
Copy link

@sven-n sven-n commented Jan 8, 2016

@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
Copy link

@bragma bragma commented Jan 12, 2016

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...

@TehWardy
Copy link

@TehWardy TehWardy commented Aug 7, 2020

@TehWardy It should be non-breaking to existing code.

Thank you tank you thank you!
One less pain to suffer, much appreciated.

@iwis
Copy link

@iwis iwis commented Aug 8, 2020

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
Copy link

@Atulin Atulin commented Aug 8, 2020

@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
Copy link

@Aleksej-Shherbak Aleksej-Shherbak commented Aug 8, 2020

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
Copy link

@jzabroski jzabroski commented Aug 9, 2020

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
Copy link

@TehWardy TehWardy commented Aug 10, 2020

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
Copy link

@Aleksej-Shherbak Aleksej-Shherbak commented Aug 12, 2020

One question about new Many-to-Many. Can I set name for the pivot table manually?

@ajcvickers
Copy link
Member

@ajcvickers ajcvickers commented Aug 12, 2020

@Aleksej-Shherbak Yes, the table mapping can be specified just as for any other table.

@Saibamen
Copy link

@Saibamen Saibamen commented Aug 22, 2020

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
Copy link

@Atulin Atulin commented Aug 22, 2020

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
Copy link

@raffaeler raffaeler commented Aug 22, 2020

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
Copy link

@Atulin Atulin commented Aug 22, 2020

Because using User.Roles would make my life easier than User.UserRoles.Select(ur => ur.Role)

@raffaeler
Copy link

@raffaeler raffaeler commented Aug 22, 2020

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
Copy link

@luizfbicalho luizfbicalho commented Aug 22, 2020

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
Copy link

@raffaeler raffaeler commented Aug 23, 2020

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
Copy link

@iWangJiaxiang iWangJiaxiang commented Aug 24, 2020

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
Copy link

@raffaeler raffaeler commented Aug 24, 2020

@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
Copy link

@vslee vslee commented Sep 27, 2020

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:

public class Post
{
    public int Id { get; set; }
    public string Name { get; set; }
    public ICollection<Tag> Tags { get; set; }
    public ICollection<int> TagIds { get; set; }
}

public class Tag
{
    public int Id { get; set; }
    public string Text { get; set; }
    public ICollection<Post> Posts { get; set; }
    public ICollection<int> 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
Copy link
Member

@AndriySvyryd AndriySvyryd commented Sep 27, 2020

@vslee We don't have any plans to support this pattern.

@jzabroski
Copy link

@jzabroski jzabroski commented Sep 30, 2020

@AndriySvyryd Is there a ticket in the backlog for it? It seems like that pattern would allow some join optimizations under certain conditions.

@AndriySvyryd
Copy link
Member

@AndriySvyryd AndriySvyryd commented Sep 30, 2020

@jzabroski No, feel free to open one and include the expected schema and query if possible.

@robertechie
Copy link

@robertechie robertechie commented Jul 29, 2021

how can you link 3 tables together without setting the joining table as specified in core 5?

@jzabroski
Copy link

@jzabroski jzabroski commented Jul 29, 2021

@robertechie Did you read the documentation before posting your question?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet