How to implement a many-to-many relationship with an intermediate table

Hello Juan,

This task has multiple solutions, depending on your tables structure. The guide below will help you choose one that is appropriate for your scenario. Please feel free to contact us if you have any questions.

Default Mapping

XPO automatically creates an intermediate table when a many-to-many relationship is declared. Check whether this table's structure matches your intermediate table. For example, if you implement the Person.Tasks and Task.Persons collections, XPO will generate the following table:

PersonTasks_TaskPersons

Data Model Settings

To change an intermediate table name, use the Association attribute's name parameter and set the AssociationAttribute.UseAssociationNameAsIntermediateTableName parameter to true. To change its column names, change the names of the corresponding collection properties.

Customize Metadata

It is possible to access the intermediate class metadata and change its mapping settings dynamically in code. Please see an example in the following article: How to change the name of the underlying table and columns for a many-to-many relationship. To apply this solution in XAF, use the ModuleBase.CustomizeTypesInfo and XpoTypesInfoHelper.GetXpoTypeInfoSource methods:

C#
using DevExpress.Xpo;
using DevExpress.Xpo.Metadata;
using DevExpress.ExpressApp.Xpo;

namespace MainDemo.Module {
    public sealed partial class MainDemoModule : ModuleBase {
        public override void CustomizeTypesInfo(ITypesInfo typesInfo) {
            base.CustomizeTypesInfo(typesInfo);
            ...
            XPDictionary xpoDictionary = XpoTypesInfoHelper.GetXpoTypeInfoSource().XPDictionary;
            XPClassInfo intermediateClassInfo = xpoDictionary.GetClassInfo(typeof(Task)).FindMember(nameof(Task.Persons)).IntermediateClass;
            //rename PersonTasks_TaskPersons.Persons to PersonTasks_TaskPersons.PersonId 
            intermediateClassInfo.FindMember(nameof(Task.Persons)).AddAttribute(new PersistentAttribute("PersonId"));
        }

Please refer to the following help topic for additional information: Use Metadata to Customize Business Classes Dynamically.

Use an Intermediate Class

If your intermediate table cannot be converted according to this structure, implement a persistent class mapped to the intermediate table and associate this class with classes that should be associated as many-to-many using one-to-many relationships. Optionally, use the ManyToManyAlias attribute to provide access to related objects directly, without using the intermediate class.
The ManyToManyAlias attribute accepts two parameters:
oneToManyCollectionName - specifies the one-to-many relationship's collection property.
referenceInTheIntermediateTableName - specifies the reference property whose values should compose the ManyToManyAlias collection. The reference property type should match the collection element type.

Here is an example:

C#
public class User : BaseObject {  
    public User(Session session) : base(session) { }  

    private string _FullName;  
    public string FullName {  
        get {  
            return _FullName;  
        }  
        set {  
            SetPropertyValue("FullName", ref _FullName, value);  
        }  
    }  

    [Association("User-GroupLinks"), Browsable(false)]  
    public IList<UserToGroupLink> UserToGroupLinks {  
        get {  
            return GetList<UserToGroupLink>("UserToGroupLinks");  
        }  
    }  

    [ManyToManyAlias("UserToGroupLinks", "LinkGroup")]  
    public IList<Group> Groups {  
        get {  
            return GetList<Group>("Groups");  
        }  
    }  
}  
public class Group : BaseObject {  
    public Group(Session session) : base(session) { }  

    private string _Name;  
    public string Name {  
        get {  
            return _Name;  
        }  
        set {  
            SetPropertyValue("Name", ref _Name, value);  
        }  
    }  

    [Association("Group-UserLinks"), Browsable(false)]  
    public IList<UserToGroupLink> UserToGroupLinks {  
        get {  
            return GetList<UserToGroupLink>("UserToGroupLinks");  
        }  
    }  

    [ManyToManyAlias("UserToGroupLinks", "LinkUser")]  
    public IList<User> Members {  
        get {  
            return GetList<User>("Members");  
        }  
    }  
}  

public class UserToGroupLink : BaseObject {  
    public UserToGroupLink(Session session) : base(session) { }  

    private User _LinkUser;  
    [Association("User-GroupLinks")]  
    public User LinkUser {  
        get { return _LinkUser; }  
        set { SetPropertyValue("LinkUser", ref _LinkUser, value); }  
    }  
    private Group _LinkGroup;  
    [Association("Group-UserLinks")]  
    public Group LinkGroup {  
        get { return _LinkGroup; }  
        set { SetPropertyValue("LinkGroup", ref _LinkGroup, value); }  
    }  
}  

As a result, the associated Members will be shown directly in the Group DetailView, and the Link dialog will show a User lookup instead of a lookup of the intermediate class (UserToGroupLink).

If you have a legacy intermediate table with a composite key, use the following approach to map a persistent class to it without creating an additional key column: What is the best solution for mapping a many-to-many relation to an existing database?

Hello Juan,
You cannot directly add a new object in the nested ListView for a Many-To-Many collection (refer to the NewObjectViewController.NewObjectAction Property article), but you can do that via an intermediate Link ListVew: execute the Link action and then the New action in the Link ListView. I hope you will find this information helpful.
Thanks,
Ilya P