CODEWITHSUNDEEP
c#sqlwinformsentity-framework
JonC
JonC

Reputation: 978

Entity-Framework: Sort on Many-to-Many

I have two entities with a many-to-many relationship and I'm looking for a way to be able to sort the result from the tables.
In other words, when I get a row from table1 and all the corresponding records from table2 I want to be able to have a stored sort order for table2 that's specific for that row in table1.

My first thought was to add a sort column to the table that represents the relation, but to my knowledge there is no way of accessing the new column in the relation.

Does anybody have any suggestions on how to accomplish this?

Upvotes: 1

Views: 1325

Answers (4)

JonC
JonC

Reputation: 978

Thank you all for the good answers to my question. I now feel more confident about the pros and cons of the different solutions.

What I ended up doing was this: As it turns out, just adding a sort column to the relation-table doesn't affect the model, update from DB still works and the table still gets mapped as a many-to-many relation. Then I created a stored procedure that fetches the sort column from the relation-table and another stored procedure to update the sort-index of a specified record.

Upvotes: 0

Danny Varod
Danny Varod

Reputation: 18069

Due to your requirement of sorting table2 results per table1 row and not globally, you have three non-elegant solutions:

  • The approach Ladislav suggested (with the bad looking model) - add order column, add bridge entity.

  • The approach JotaBe suggested (with the bad looking schema) - add an additional table and maintain both.

  • If the context is used only for reading (no need to change relationships) and you don't mind changing the EDMX manually after every update from DB, then you could hack the emdx and change the SSDL definition of the relationship table to an SQL query e.g.

<EntitySet Name="AS_TO_BS" EntityType="BlaBla.Store.AS_TO_BS">
    <DefiningQuery>
        SELECT ID1, ID2 ORDER BY ORDERVALUE
        FROM AS_TO_BS
    </DefiningQuery>
</EntitySet>

Instead of:

<EntitySet Name="AS_TO_BS" EntityType="BlaBla.Store.AS_TO_BS"
    store:Type="Tables" Schema="MY_SCHEMA" />

See if you can relax your requirements, if not then settle on one of the three solutions.

Edit:

Another idea:

  • Use a view to duplicate the relationship table, then map the relationship to the view (as read only) and the order entity to the table (writable).

Upvotes: 1

JotaBe
JotaBe

Reputation: 39004

As Ladislav Mrnka states, if you add the new column to the junction table, there will be a new entity "in the middle" that will make navigation much harder.

If you want to avoid this, but still be able to make the navigation as usual, you can keep the junction table and add a new table, just like the junction, with the order column added. When you need the order info, you can just join this table to get it and use it.

This new table will, of course, require some maintenance. I.e. you can create a delete on cascade for the junction+order to the junction table. And use a trigger (ooops, that's not good!) to create a new row with default order for each new created relation. So, it would be much more advisable to handle this in you business logic.

I know it's too tricky, but there's no magic solution... just choose what is more comfortable to you.

Upvotes: 2

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364249

You can add new column to the junction table but the table will become a new entity so your model will now consist of three entities where and two one-to-many relations instead of two entities and single many-to-many relation.

Upvotes: 1

Related Questions