Many-to-Many Associations

This topic illustrates how to configure many-to-many associations. To define a many-to-many association, you have to define both of the navigation ends. When you want to create a many-to-many association, instead of the HasConstraint method you should use the MapJoinTable method. It specifies the join table that defines a many to many relation and its endpoints.

Many-to-Many Associations

The following example demonstrates how to create a many-to-many association between the Employee and Territory classes. Sample FluentMetadataSource implementation could be found at the end of the topic.

The MapJoinTable method has three overloads. The first one (used in this example), specifies the name of the join table and the endpoints for the relation. For example, if you execute the DDL script, then the join table in the database will be named EmployeeTerritories and the end points (the foreign keys) will be named EmployeeID and TerritoryID. The EmployeeID and TerritoryID columns are foreign keys that refer respectively to the Employee and Territory tables. The second MapJoinTable overload accepts only one string parameter that specifies the name of the join table. The last overload takes no parameters. In this case Telerik Data Access will provide default names for the join table and the relation endpoints (foreign keys).

When you define mapping for a collection property, the HasAssociation property expects the type of the property to be IList<T>. Note that in the previous example, the Territories property in the Employee class is of type IList<Territory>. Respectively the Employees property in the Territory property is of type IList<Employee>.

territoryMapping.
   HasAssociation(t => t.Employees).
   WithOpposite(e => e.Territories).
   MapJoinTable("EmployeeTerritories",
                   (territory, employee) => new
                           {
                               EmployeeID = employee.EmployeeId,
                               TerritoryID = territory.TerritoryId
                           });

territoryMapping.
    HasAssociation(Of Employee)(Function(t) t.Employees).
    WithOpposite(Function(e) e.Territories).
    MapJoinTable("EmployeeTerritories", Function(territory, employee) New With { _
        Key .EmployeeID = employee.EmployeeId,
        Key .TerritoryID = territory.TerritoryId})

Adding a Sequence Column

You can add a Seqence column to the join table of your many to many association. The Sequence column indicates the order in which related records were added. The WithSequenceColumn and WithSequenceColumnOpenAccessType methods are used to map a Sequence column.

territoryMapping.
   HasAssociation(t => t.Employees).
   WithOpposite(e => e.Territories).
   MapJoinTable("EmployeeTerritories",
                   (territory, employee) => new
                               {
                                   EmployeeID = employee.EmployeeId,
                                   TerritoryID = territory.TerritoryId
                               })
                   .WithSequenceColumn("SequenceId")
                   .WithSequenceColumnOpenAccessType(OpenAccessType.Int32);

territoryMapping.
    HasAssociation(Of Employee)(Function(t) t.Employees).
    WithOpposite(Function(e) e.Territories).
    MapJoinTable("EmployeeTerritories", Function(territory, employee) New With { _
            Key .EmployeeID = employee.EmployeeId,
            Key .TerritoryID = territory.TerritoryId}).
        WithSequenceColumn("SequenceId").                                                                  
        WithSequenceColumnOpenAccessType(OpenAccessType.Int64)

Many-To-Many Self-Reference Associations

The following example demonstrates how to create a many-to-many self-reference association for the SalesPerson class. Sample FluentMetadataSource implementation could be found at the end of the topic.

salesPersonConfiguration.
   HasAssociation(t => t.Minions).
   WithOpposite(e => e.ReportsTo).
   MapJoinTable("SalesPersonManyToMany",
                   (person1, person2) => new
                           {
                               SalesPerson1ID = person1.Id,
                               SalesPerson2ID = person2.Id
                           });

salesPersonConfiguration.
    HasAssociation(Of SalesPerson)(Function(t) t.Minions).
    WithOpposite(Function(e) e.ReportsTo).
    MapJoinTable("SalesPersonManyToMany", Function(person1, person2) New With { _
        Key .SalesPerson1ID = person1.Id,
        Key .SalesPerson2ID = person2.Id})

Employee

public class Employee
{
   public Employee()
   {
       this.Territories = new List<Territory>();
   }
   public int EmployeeId {get;set;}
   public string Name { get; set; }
   public IList<Territory> Territories {get;set;}
}

Public Class Employee
    Public Sub New()
        Me.Territories = New System.Collections.Generic.List(Of Territory)()
    End Sub
    Private _employeeId As Integer
    Public Property EmployeeId() As Integer
        Get
            Return _employeeId
        End Get
        Set(ByVal value As Integer)
            _employeeId = value
        End Set
    End Property
    Private _name As String
    Public Property Name As String
        Get
            Return _name
        End Get
        Set(value As String)
            _name = value
        End Set
    End Property
    Private _territories As System.Collections.Generic.IList(Of Territory)
    Public Property Territories() As System.Collections.Generic.IList(Of Territory)
        Get
            Return _territories
        End Get
        Set(ByVal value As System.Collections.Generic.IList(Of Territory))
            _territories = value
        End Set
    End Property
End Class

Territory

public class Territory
{
   public Territory()
   {
       this.Employees = new List<Employee>();
   }
   public string TerritoryId {get;set;}
   public string TerritoryDescription {get;set;}
   public int RegionId {get;set;}
   public IList<Employee> Employees {get;set;}
}

Public Class Territory
    Public Sub New()
        Me.Employees = New System.Collections.Generic.List(Of Employee)()
    End Sub
    Private _territoryId As String
    Public Property TerritoryId() As String
        Get
            Return _territoryId
        End Get
        Set(ByVal value As String)
            _territoryId = value
        End Set
    End Property
    Private _territoryDescription As String
    Public Property TerritoryDescription() As String
        Get
            Return _territoryDescription
        End Get
        Set(ByVal value As String)
            _territoryDescription = value
        End Set
    End Property
    Private _regionId As Integer
    Public Property RegionId() As Integer
        Get
            Return _regionId
        End Get
        Set(ByVal value As Integer)
            _regionId = value
        End Set
    End Property
    Private _employees As System.Collections.Generic.IList(Of Employee)
    Public Property Employees() As System.Collections.Generic.IList(Of Employee)
        Get
            Return _employees
        End Get
        Set(ByVal value As System.Collections.Generic.IList(Of Employee))
            _employees = value
        End Set
    End Property
End Class

SalesPerson

public class SalesPerson
{
   public SalesPerson()
   {
       this.ReportsTo = new List<SalesPerson>();
       this.Minions = new List<SalesPerson>();
   }
   public int Id { get; set; }
   public decimal Bonus { get; set; }
   public IList<SalesPerson> ReportsTo { get; set; }
   public IList<SalesPerson> Minions { get; set; }
}

Public Class SalesPerson
    Public Sub New()
        Me.ReportsTo = New List(Of SalesPerson)()
        Me.Minions = New List(Of SalesPerson)()
    End Sub
    Private _id As Integer
    Public Property Id As Integer
        Get
            Return _id
        End Get
        Set(value As Integer)
            _id = value
        End Set
    End Property
    Private _bonus As Decimal
    Public Property Bonus As Decimal
        Get
            Return _bonus
        End Get
        Set(value As Decimal)
            _bonus = value
        End Set
    End Property
    Private _reportsTo As IList(Of SalesPerson)
    Public Property ReportsTo As IList(Of SalesPerson)
        Get
            Return _reportsTo
        End Get
        Set(value As IList(Of SalesPerson))
            _reportsTo = value
        End Set
    End Property
    Private _minions As IList(Of SalesPerson)
    Public Property Minions As IList(Of SalesPerson)
        Get
            Return _minions
        End Get
        Set(value As IList(Of SalesPerson))
            _minions = value
        End Set
    End Property
End Class

Sample FluentMetadataSource Implementation - Many-To-Many Association

public class FluentModelMetadataSource : FluentMetadataSource
{
   protected override IList<MappingConfiguration> PrepareMapping()
   {
       List<MappingConfiguration> configurations = new List<MappingConfiguration>();

       MappingConfiguration<Territory> territoryMapping = new MappingConfiguration<Territory>();
       territoryMapping.MapType( t => new
           {
               Id = t.TerritoryId,
               Name = t.TerritoryDescription,
               RegionId = t.RegionId
           } ).ToTable( "Territories" );
       territoryMapping.HasProperty( t => t.TerritoryId ).IsIdentity();

       MappingConfiguration<Employee> employeeMapping = new MappingConfiguration<Employee>();
       employeeMapping.MapType( e => new
           {
               Id = e.EmployeeId,
               Name = e.Name,
           } ).ToTable( "Employees" );
       employeeMapping.HasProperty( e => e.EmployeeId ).IsIdentity();

       territoryMapping.HasAssociation( t => t.Employees ).WithOpposite( e => e.Territories )
           .MapJoinTable( "EmployeeTerritories", ( territory, employee ) => new
               {
                   EmployeeID = employee.EmployeeId,
                   TerritoryID = territory.TerritoryId
               } );

       configurations.Add( employeeMapping );
       configurations.Add( territoryMapping );

       return configurations;
   }
}

Public Class FluentModelMetadataSource
    Inherits FluentMetadataSource
    Protected Overrides Function PrepareMapping() As _
        System.Collections.Generic.IList(Of Telerik.OpenAccess.Metadata.Fluent.MappingConfiguration)
        Dim configurations As List(Of MappingConfiguration) = New List(Of MappingConfiguration)()

        Dim territoryMapping As New MappingConfiguration(Of Territory)()
        territoryMapping.MapType(Function(t) New With {
            Key .Id = t.TerritoryId,
            Key .Name = t.TerritoryDescription,
            Key .RegionId = t.RegionId}).ToTable("Territories")
        territoryMapping.HasProperty(Function(t) t.TerritoryId).IsIdentity()
        territoryMapping.FieldNamingRules.AddPrefix = "_"

        Dim employeeMapping As New MappingConfiguration(Of Employee)()
        employeeMapping.MapType(Function(e) New With {Key .Id = e.EmployeeId}).ToTable("Employees")
        employeeMapping.HasProperty(Function(e) e.EmployeeId).IsIdentity()
        employeeMapping.FieldNamingRules.AddPrefix = "_"

        territoryMapping.
            HasAssociation(Of Employee)(Function(t) t.Employees).
            WithOpposite(Function(e) e.Territories).
            MapJoinTable("EmployeeTerritories", Function(territory, employee) New With {
                Key .EmployeeID = employee.EmployeeId,
                Key .TerritoryID = territory.TerritoryId})

        configurations.Add(employeeMapping)
        configurations.Add(territoryMapping)

        Return configurations
    End Function
End Class

Sample FluentMetadataSource Implementation - Many-To-Many Self-Reference Association

public class FluentModelMetadataSource : FluentMetadataSource
{
   protected override IList<MappingConfiguration> PrepareMapping()
   {
       List<MappingConfiguration> configurations = new List<MappingConfiguration>();

       MappingConfiguration<SalesPerson> salesPersonConfiguration = new MappingConfiguration<SalesPerson>();
       salesPersonConfiguration.MapType( t => new
           {
               Id = t.Id,
               Bonus = t.Bonus
           } ).ToTable( "SalesPerson" );
       salesPersonConfiguration.HasProperty( t => t.Id ).IsIdentity( KeyGenerator.Autoinc );

       salesPersonConfiguration.HasAssociation( t => t.Minions ).
            WithOpposite( e => e.ReportsTo )
           .MapJoinTable( "SalesPersonManyToMany", ( person1, person2 ) => new
               {
                   SalesPerson1ID = person1.Id,
                   SalesPerson2ID = person2.Id
               } );

       configurations.Add( salesPersonConfiguration );
       return configurations;
   }
}

Public Class FluentModelMetadataSource
    Inherits FluentMetadataSource
    Protected Overrides Function PrepareMapping() As IList(Of MappingConfiguration)
        Dim configurations As New List(Of MappingConfiguration)()

        Dim salesPersonConfiguration As New MappingConfiguration(Of SalesPerson)()
        salesPersonConfiguration.MapType(Function(t) New With {
            Key .Id = t.Id, Key .Bonus = t.Bonus}).ToTable("SalesPerson")
        salesPersonConfiguration.HasProperty(Function(t) t.Id).IsIdentity(KeyGenerator.Autoinc)
        salesPersonConfiguration.FieldNamingRules.AddPrefix = "_"

        salesPersonConfiguration.
            HasAssociation(Of SalesPerson)(Function(t) t.Minions).
            WithOpposite(Function(e) e.ReportsTo).
            MapJoinTable("SalesPersonManyToMany", Function(person1, person2) New With {
                Key .SalesPerson1ID = person1.Id,
                Key .SalesPerson2ID = person2.Id})

        configurations.Add(salesPersonConfiguration)
        Return configurations
    End Function
End Class