Accessing join table extra fields

7 posts, 0 answers
  1. Robin
    Robin avatar
    8 posts
    Member since:
    Oct 2013

    Posted 27 Nov 2013 Link to this post

    I made a custom join table and used like in this article:

    http://documentation.telerik.com/openaccess-orm/developers-guide/openaccess-orm-domain-model/working-with-associations/many-to-many-associations/developer-guide-domain-model-working-associations-many-to-many-custom-table

    Its pretty convenient as often the join table itself isnt very interesting to the application (and can be very annoying when trying to serialize datastructures to JSON). But sometimes it is the most logical place to put information. Is there a way to access the extra fields in the join table?

    Example:

    Team -> Teammembers <- Person
    Suppose you would want to introduce a field named 'active', or 'IsLead' to the Teammembers join table. How can I get to it?
  2. Kristian Nikolov
    Admin
    Kristian Nikolov avatar
    206 posts

    Posted 28 Nov 2013 Link to this post

    Hello Robin,

    In order to access and modify the entries in a join table, you need to map the join table to a Domain Class and redesign the m:n association as a combination of two 1:m associations.

    As per your example, you would have three classes mapped to their respective tables in your Domain Model - Team, Teammember and Person. Between Team and Teammember there would be 1:m association. Person and Teammember would also have 1:m association between them.

    In case you do not yet have the join table defined in your model, you will have to create the required classes, map them to tables and create the 1:m associations using the designer.

    In case your model already uses a join table, follow these steps to expose it:
    1. Delete the m:n association between your classes.
    2. From the properties window of the join table (located in Model Schema Explorer), set the IsJoinTable property to false.
    3. Right-click on the table and chose Create Domain class from the menu.
    4. Create the 1:m associations between the class of the join table and the classes which were participating in the m:n association.

    After your model is ready, you should be able to access and modify the join table through its Domain Class as you would with any other table.

    I have prepared a sample project which illustrates a Domain Model with exposed join table and demonstrates executing CRUD operations over it. Please find it attached to my answer.

    Note that before running the project you should create the database in your Microsoft SQL Server Express instance and then update it from the OpenAccess Domain Model.

    I hope this helps.


    Regards,
    Kristian Nikolov
    Telerik
    OpenAccess ORM Q3 2013 simplifies your model operations even further providing you with greater flexibility. Check out the list of new features shipped with our latest release!
  3. DevCraft banner
  4. Robin
    Robin avatar
    8 posts
    Member since:
    Oct 2013

    Posted 28 Nov 2013 Link to this post

    Hi Kristian,


    Thanks for your example, but perhaps I have explained myself not good enough. Sorry for that.


    The scenario you describe is what I used first. This of course gives you access to the fields in the join table. But there is a problem with it in my case (unless there is some way to circumvent that, but I haven’t found it yet). The thing is, Im using a javascript tree widget that needs (json) data in a pretty literal hierarchical structure. In this case for example it would be a list of teams on the first level; each team has its fields and the container field for people in it for the second level of the tree.

    The problem arises when you set it up like in your example. When you query the team table and specify a fetchstrategy for the navigation properties, the join table is added to the json data tree result. So when the tree is rendered with this, there is an extra unwanted level caused by the join table.
    To avoid this, I used the way described in my first post. This solved the problem at first, the join table is absent from the data when queried. But in this way, you cant access the extra fields.
    Hence my question if the direct modelling of n:m relationship supports this.

    It could be my newness to this that I am overlooking something though, either with a setting in OpenAccess, or with linq queries. Of course I could build up the tree by hand iterating through everything, but that is both slow in performance and also a lot more work (I plan to use many trees in the app).

  5. Kristian Nikolov
    Admin
    Kristian Nikolov avatar
    206 posts

    Posted 03 Dec 2013 Link to this post

    Hello Robin,

    Thank you for the additional information about your scenario.

    In order to access the columns of a table, OpenAccess ORM needs the table to be mapped to a Domain Class. Unfortunately this means that it is not possible to access the columns of a join table used to directly model m:n association.

    However, I can suggest a possible workaround to avoid introducing an additional level to your tree view. When your join table is mapped to a DomainClass (Teammember), you can define two new non persistent classes which would emulate the structure of directly modeled m:n association and use them to populate your tree view:
    1. Define a class which has the properties from both Person and Teammember (IsActive and IsLead). For example lets call this class TeammemberCustom.
    2. Define a class which has the same properties as Team, but Teammembers is  a List<TeammembersCustom>. For example lets call this class TeamCustom.
    3. Then, based on the teams you have in your database, you can get a list of TeamCustom objects like so:
      List<TeamCustom> retrievedTeams = context.Teams
          .Select(team => new TeamCustom()
                  {
                      TeamId = team.TeamId,
                      Name = team.Name
                  }).ToList();
    4. You can now set the Teammember property of each TeamCustom object with the following code:
      foreach (TeamCustom currentTeam in retrievedTeams)
      {
          List<TeamMemberCustom> teamMembers = context.Teams
              .FirstOrDefault(team => team.TeamId == currentTeam.TeamId)
              .Teammembers
              .Select(member => new TeamMemberCustom()
                      {
                          Id = member.Person.PersonId,
                          Name = member.Person.Name,
                          Address = member.Person.Address,
                          IsActive = member.IsActive,
                          IsLead = member.IsLead
                      }).ToList();
          currentTeam.Teammembers = teamMembers;
      }

    You could now use the retrievedTeams list to populate your tree view while avoiding the additional level which would be introduced by the class of the join table.

    I hope this helps.

    Regards,
    Kristian Nikolov
    Telerik
    OpenAccess ORM Q3 2013 simplifies your model operations even further providing you with greater flexibility. Check out the list of new features shipped with our latest release!
  6. Robin
    Robin avatar
    8 posts
    Member since:
    Oct 2013

    Posted 05 Dec 2013 Link to this post

    Thanks, that could be a good suggestion.
    Im not sure how many join tables there will be with extra attributes. For those reading, Im guessing an alternative would be to just map the join tables to a single domain class (perhaps in another model file even), if there's not a lot of them. Its less convenient to work with, but if this is the exception to the rule, workable. You could even just to turn to some manual sql queries if going down this road.
  7. Kristian Nikolov
    Admin
    Kristian Nikolov avatar
    206 posts

    Posted 10 Dec 2013 Link to this post

    Hello Robin,

    As discussed earlier, to expose a join table you have to map it to a Domain Class. In order for such mapping to be valid, the m:n association should be reconstructed as a combination of two 1:m associations to the Domain Class to which the join table is mapped.

    That means that if I have understood you correctly, the alternative mapping you suggested would not be valid and you will receive a MetadataException when accessing one of the tables participating in the m:n relation. Because the metadata of multiple models for a single database is aggregated, mapping the join table in a separate model is also inapplicable approach which will result in the same exception.

    As for accessing the join table directly, you can do that using our ADO API to execute queries or stored procedures.

    I hope this helps.

    Regards,
    Kristian Nikolov
    Telerik
    OpenAccess ORM Q3 2013 simplifies your model operations even further providing you with greater flexibility. Check out the list of new features shipped with our latest release!
  8. Robin
    Robin avatar
    8 posts
    Member since:
    Oct 2013

    Posted 10 Dec 2013 Link to this post

    I didnt try it yet, so thanks for the heads up!
Back to Top
DevCraft banner