Custom change tracking of the n - n related tables and entity framework RRS feed

  • Question

  • Hi,
    one way to model n - n relationship between two tables is to introduce association table with composite PK whose columns are behaving at the same time as FK.

    e.g. let presume Role can have many Permissions and any permission can belong to many roles

    Modeling that would result with three data tables:

    1.     Roles data table with PK = RoleID

    2.     Permissions data table with PK = PermissionID

    3.      RolesToPermissions data table
    RoleID (PK)
    PermissionID (PK)

    Entity framework understands this table lay out and encapsulate it in a model allowing the code consuming the entities to use simply Role.Permissions[0] and Permission.Roles[0]

    But, in order to track the inserts and updates made to RolesToPermissions data table I had to add there two columns DateCreated and DateModified which caused EF4 to stop recognizing it as association and now I ended with entity usage like Role.Permissions[0].Permission and Permission.Roles[0].Role which I find very awkward (at best).
    (Right now I am experimenting with modifying t4 templates to leave that artifact in place but also to provide me old syntax delegating the calls to this new model.)

    Can you please provide us some guidance on the ways how to approach change tracking of association tables?


    Saturday, February 20, 2010 11:42 AM


All replies

  • Hi, I would suggest that this would be a question best answered by the ADO.NET Data Services forum:

    Sr. Program Manager, SQL Azure and Sync Framework - http://msdn.microsoft.com/sync/
    Wednesday, February 24, 2010 5:52 PM
  • Well, IMHO there's nothing here in my question at all related to ADO NET services and I posted it here because the question is how to sync assocciation table (with illustration of one way failing the EF4 model), but ok - I'll use my own brain and try to come up with the best solution possible I can and blog about it :)

    Wednesday, February 24, 2010 10:38 PM
  • Hi,
    What about decoupled tracking tables using triggers, etc?

    Monday, March 1, 2010 12:51 AM
  • Well, I was thinking about this but I have faced some issues with Sync Framework and composite PK (couldn't make filtering running properlly).. Were you able to set that up in your code? If yes, would it be possible to see it somehow (blog post etc)?

    The reason why I asked this question is to get from SyncFx team some best practice guidelines so "we" could skip guessing what is the "right" way to tackle it :)

    Monday, March 1, 2010 12:56 AM
  • Hi,
    Answering your questions it was only guess :) No blogs etc. Another way you can try is to add single primary key to intermediate table to get single key instead of compound and then sync it as any other reg. table. Still associations needs to be made manually in ADO.NET but this seems to be possible.

    BTW, MSF has still lack of docs and samples in a lot of area but hope it will be imporved in the future.


    Monday, March 1, 2010 2:02 AM
  • I did add a PK there (my cyrrent solution which works fine) but as described that made my EF model "wierd" at best due to the fact I have to write now


    but that is something I can live for now until I don't finish the POC of my own SyncFX I am doing which is based on:
    - share the edmx between server and client ( I can do that in my WPF app)
    - use EF4 to get STE entities and serialize them in JSON stream
    - wcf to transport "array of JSON strings"
    - client having EF4 pointing to local SqlCe or Sql express connection string
    - client deserializing json strings to EF4 entities
    - client using EF4 repository to reinsert data to client DB (using all the validation etc of EF4)

    So far I have it working like a charm, doing more tests...

    Monday, March 1, 2010 8:32 AM