none
CRUD Stored procedures and many-to-many relationships RRS feed

  • Question

  • Hello,

    I faced with next problem. I use EF database first and edmx model. I have many-to-many relationships in database and visual studio generated all correct, but database user which I use doesn't have permissions to select data from tables, instead of SELECT permission user has EXECUTE permission for CRUD procedures. But I didn't find how can I specifi CRUD SPs for bridge table in database. As result I have exception when EF try to insert to this bridge table without SP. 

    Is in possible to add CRUD SPs for bridge tables or probably exists setting for many-to-many relationship which tell EF model generator don't use many-to-many relationship and use 3 table like in MSSQL?

    • Moved by CoolDadTx Friday, March 28, 2014 5:18 PM EF related
    Friday, March 28, 2014 1:55 PM

Answers

  • Hello,

    >>Is in possible to add CRUD SPs for bridge tables or probably exists setting for many-to-many relationship which tell EF model generator don't use many-to-many relationship and use 3 table like in MSSQL?

    Could you modify the database schema? If you can, then you can use way in Code First changing the many to many relationship to two one to many relationships in order to touch the junction table.

    For generating the junction table in .edmx model, adding an extra field to it and then there are three tables in the model as:

    Then we can map three tables CRUD operations to stored procedures each:

    http://channel9.msdn.com/Blogs/wriju/CRUD-using-Stored-Procedure-in-ADONET-Entity-Framework-40

    If I misunderstand, please let me know.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, March 31, 2014 3:15 AM
    Moderator

All replies

  • Can I ask a silly question? Why not just adjust the permissions for the user on the SQL Server instance to allow READ on the objects in question. I would guess that rights isn't a problem since the SP in question is already inserting into the table.
    • Proposed as answer by Christopher84 Friday, March 28, 2014 4:02 PM
    Friday, March 28, 2014 3:22 PM
  • 1. It's denied use any tables (select/insert/update/delete), only SPs alowed, user don't see tables at all.

    2. All entities use SPs, so SQL Server use only SPs except bridge tables for many-to-many relationship where I can't specify procedures and EF generate direct INSERT  statement, so only READ permission not enough.


    Friday, March 28, 2014 7:01 PM
  • Hello,

    >>Is in possible to add CRUD SPs for bridge tables or probably exists setting for many-to-many relationship which tell EF model generator don't use many-to-many relationship and use 3 table like in MSSQL?

    Could you modify the database schema? If you can, then you can use way in Code First changing the many to many relationship to two one to many relationships in order to touch the junction table.

    For generating the junction table in .edmx model, adding an extra field to it and then there are three tables in the model as:

    Then we can map three tables CRUD operations to stored procedures each:

    http://channel9.msdn.com/Blogs/wriju/CRUD-using-Stored-Procedure-in-ADONET-Entity-Framework-40

    If I misunderstand, please let me know.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, March 31, 2014 3:15 AM
    Moderator
  • Yes, it works, but for existing edmx model need to drop existing many-to-many relationship before and then update model from database.

    This column not needed, but this is not a big problem, thanks!

    Monday, March 31, 2014 7:23 AM