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

Open
0xdeafcafe opened this Issue Jan 8, 2015 · 202 comments

Comments

Projects
None yet
@0xdeafcafe
Contributor

0xdeafcafe commented Jan 8, 2015

Note:

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


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

This comment has been minimized.

Show comment
Hide comment
@bricelam

bricelam Jan 8, 2015

Member

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);
Member

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

This comment has been minimized.

Show comment
Hide comment
@0xdeafcafe

0xdeafcafe Jan 8, 2015

Contributor

Thanks!

Contributor

0xdeafcafe commented Jan 8, 2015

Thanks!

@0xdeafcafe 0xdeafcafe closed this Jan 8, 2015

@tonysneed

This comment has been minimized.

Show comment
Hide comment
@tonysneed

tonysneed Jan 20, 2015

Contributor

@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!

Contributor

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

This comment has been minimized.

Show comment
Hide comment
@bricelam

bricelam Jan 20, 2015

Member

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.

Member

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

This comment has been minimized.

Show comment
Hide comment
@divega

divega Jan 20, 2015

Member

@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?

Member

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

This comment has been minimized.

Show comment
Hide comment
@tonysneed

tonysneed Jan 20, 2015

Contributor

@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!

Contributor

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

This comment has been minimized.

Show comment
Hide comment
@tonysneed

tonysneed Jan 21, 2015

Contributor

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

Contributor

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 from Many-to-Many relationships to 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

This comment has been minimized.

Show comment
Hide comment
@ctolkien

ctolkien 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?

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

This comment has been minimized.

Show comment
Hide comment
@damccull

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

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

This comment has been minimized.

Show comment
Hide comment
@popcatalin81

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

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

This comment has been minimized.

Show comment
Hide comment
@rowanmiller

rowanmiller Jul 29, 2015

Member

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

Member

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

This comment has been minimized.

Show comment
Hide comment
@lajones

lajones Oct 6, 2015

Member

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.

Member

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

This comment has been minimized.

Show comment
Hide comment
@bragma

bragma 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();

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

This comment has been minimized.

Show comment
Hide comment
@mjrousos

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

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

This comment has been minimized.

Show comment
Hide comment
@HerbertBodner

HerbertBodner Dec 25, 2015

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

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

This comment has been minimized.

Show comment
Hide comment
@sthiakos

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

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

This comment has been minimized.

Show comment
Hide comment
@sven-n

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

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

This comment has been minimized.

Show comment
Hide comment
@bragma

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

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

@scionwest

This comment has been minimized.

Show comment
Hide comment
@scionwest

scionwest Jul 21, 2018

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.

scionwest commented Jul 21, 2018

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

This comment has been minimized.

Show comment
Hide comment
@maxim-shaw

maxim-shaw Jul 23, 2018

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

maxim-shaw commented Jul 23, 2018

@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

This comment has been minimized.

Show comment
Hide comment
@kevdever

kevdever Jul 28, 2018

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.

kevdever commented Jul 28, 2018

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

This comment has been minimized.

Show comment
Hide comment
@msx752

msx752 Aug 3, 2018

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

msx752 commented Aug 3, 2018

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

This comment has been minimized.

Show comment
Hide comment
@maxim-shaw

maxim-shaw Aug 3, 2018

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

maxim-shaw commented Aug 3, 2018

@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

This comment has been minimized.

Show comment
Hide comment
@Cowlephant

Cowlephant Aug 3, 2018

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);

Cowlephant commented Aug 3, 2018

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

This comment has been minimized.

Show comment
Hide comment
@akram1905

akram1905 Aug 7, 2018

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

akram1905 commented Aug 7, 2018

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

@filipbekic01

This comment has been minimized.

Show comment
Hide comment
@filipbekic01

filipbekic01 Aug 7, 2018

Come on .NET, you can do it.

filipbekic01 commented Aug 7, 2018

Come on .NET, you can do it.

@bfsmithATL

This comment has been minimized.

Show comment
Hide comment
@bfsmithATL

bfsmithATL Aug 7, 2018

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

bfsmithATL commented Aug 7, 2018

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

@ytzhakov

This comment has been minimized.

Show comment
Hide comment
@ytzhakov

ytzhakov Aug 7, 2018

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

ytzhakov commented Aug 7, 2018

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

@scionwest

This comment has been minimized.

Show comment
Hide comment
@scionwest

scionwest Aug 7, 2018

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.

scionwest commented Aug 7, 2018

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

This comment has been minimized.

Show comment
Hide comment
@msx752

msx752 Aug 15, 2018

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

msx752 commented Aug 15, 2018

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

This comment has been minimized.

Show comment
Hide comment
@jeffguillaume

jeffguillaume Aug 15, 2018

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.

jeffguillaume commented Aug 15, 2018

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

This comment has been minimized.

Show comment
Hide comment
@zete

zete Aug 28, 2018

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

zete commented Aug 28, 2018

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

@orassr

This comment has been minimized.

Show comment
Hide comment
@orassr

orassr Sep 4, 2018

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

orassr commented Sep 4, 2018

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

@scionwest

This comment has been minimized.

Show comment
Hide comment
@scionwest

scionwest Sep 4, 2018

@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());
    }
}

scionwest commented Sep 4, 2018

@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());
    }
}
@haidari-m

This comment has been minimized.

Show comment
Hide comment
@haidari-m

haidari-m Sep 14, 2018

EF Core Team please fix this!

haidari-m commented Sep 14, 2018

EF Core Team please fix this!

@anatalin

This comment has been minimized.

Show comment
Hide comment
@anatalin

anatalin Sep 25, 2018

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

anatalin commented Sep 25, 2018

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

@thombrink

This comment has been minimized.

Show comment
Hide comment
@thombrink

thombrink Sep 25, 2018

Maybe someone can do something with my solution: #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).

thombrink commented Sep 25, 2018

Maybe someone can do something with my solution: #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

This comment has been minimized.

Show comment
Hide comment
@vsopko

vsopko Sep 25, 2018

@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)

vsopko commented Sep 25, 2018

@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

This comment has been minimized.

Show comment
Hide comment
@eduherminio

eduherminio commented Sep 25, 2018

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

@raffaeler

This comment has been minimized.

Show comment
Hide comment
@raffaeler

raffaeler Sep 25, 2018

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

raffaeler commented Sep 25, 2018

@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

This comment has been minimized.

Show comment
Hide comment
@thombrink

thombrink Sep 26, 2018

@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 commented Sep 26, 2018

@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

This comment has been minimized.

Show comment
Hide comment
@thombrink

thombrink Sep 26, 2018

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

thombrink commented Sep 26, 2018

@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

This comment has been minimized.

Show comment
Hide comment
@vsopko

vsopko Sep 26, 2018

@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?

vsopko commented Sep 26, 2018

@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

This comment has been minimized.

Show comment
Hide comment
@thombrink

thombrink Sep 26, 2018

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

thombrink commented Sep 26, 2018

@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

This comment has been minimized.

Show comment
Hide comment
@vsopko

vsopko Sep 26, 2018

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

vsopko commented Sep 26, 2018

@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

This comment has been minimized.

Show comment
Hide comment
@thombrink

thombrink Sep 26, 2018

@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 :)

thombrink commented Sep 26, 2018

@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

This comment has been minimized.

Show comment
Hide comment
@neridonk

neridonk Oct 2, 2018

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

neridonk commented Oct 2, 2018

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

@ginomessmer

This comment has been minimized.

Show comment
Hide comment
@ginomessmer

ginomessmer commented Oct 15, 2018

+1

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment