7

Is there any way we can get the underlying "many to many table" joining the two entities in entity framework.

For example if we have Product and Order entities, how can we get the underlying conjunction table Product_Order that joins the Product and Order table.

Any help would be appreciated.

skaffman
  • 398,947
  • 96
  • 818
  • 769
Baig
  • 1,489
  • 1
  • 25
  • 41
  • Why do you need that? It will make interaction with objects much more complex. EF by default hides these pure junction tables by correct many-to-many relation. – Ladislav Mrnka May 06 '11 at 11:15
  • @Ladislav: thanks for the quick reply. Actually, I have to do some business logic on the many to many table. And I am doing so by overriding the: public int SaveChanges(SaveOptions options) method of ObjectContext.Is there any way we can get this tablename just as we get a table name for an entity using ToTraceString() method. see this: http://stackoverflow.com/questions/1895455/ – Baig May 06 '11 at 12:22
  • a pure junction table is not a business entity, rather just a way to express a `many-to-many` relationship in SQL (that's why EF hides it). Therefore, `doing business logic on this table` doesn't make sense. Perhaps you could describe your scenario in more detail, and there could be a better solution to your problem. – Yakimych May 06 '11 at 16:04
  • @Yakimych - thanks. I want a list of IDs(List) of Order entity in Product entity instead of list of Orders (List). And the requirement is not to have List in Product entity. So, when the Product entity is loaded the List should also be filled. And when saving the Product entity, the List which contains the corresponding Ids of Order should also be updated. Hope this clears the requirement that i want to do. – Baig May 09 '11 at 07:22

1 Answers1

9

It is possible to expose junction table as an entity but it is not common / needed:

  • You will start with this:

enter image description here

  • Delete many-to-many relation between your Product and Order entities
  • Create new ProductOrder entity in designer (either by using toolbox or context menu)
  • Define two properties in your new entity corresponding to Foreign keys defined in your junction table - ProductId, OrderId - make sure that both are marked as entity key (they must be composite primary key in the database) and have the same type as PKs
  • Open Mapping details and map your new entity to junction table

enter image description here

  • Create two new one-to-many associations. The frist between Product and ProductOrder and the second between Order and ProductOrder.
  • In Property window of each added relation set up referential constraints (as described also here).

enter image description here

There is very big chance that if you need this you are doing something wrong.

Community
  • 1
  • 1
Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
  • 2
    Great answer. As a sidenote: a quick but dirtier solution would be to add some "dummy" column to the junction table (e.g. `Id` as identity). It would get exposed by EF as an entity in that case. – Yakimych May 07 '11 at 15:24
  • @Ladislav, Not sure why you'd say, "There is very big chance that if you need this you are doing something wrong." since this is very common occurrence needed whenever you need to add a attribute to the relationship. You can create a many-to-many relationship between homes and people for example but you'd need to do this to add a purchase date or anything else that belongs to the relationship instead of either primary entity. – spadelives Oct 26 '15 at 05:37
  • @spadelives: I agree with you but once you have additional property in the junction table it becomes a "new entity" and VS will generate it automatically - you will not need to do it manually. The comment was meant for this elementary case where you are working with pure junction table. – Ladislav Mrnka Oct 27 '15 at 10:07
  • I wanted to do this so I could join to the many-many table in linq-sql. Without this, isn't it necessary to have to go through navigation properties to filter on ProductId when selecting from Order. Avoiding the Nav property filter and using a regular join generates cleaner and better SQL (no use of exists). – CRice Jan 08 '16 at 05:46