Defining Explicit Indexes in Join Table

This topic demonstrates how to define explicit indexes in a join table. Consider the following model:

The classes have a many-to-many association defined between them using a join table. The LeftSide class has a collection of RightSide entities. However, the RightSide class does not expose a collection of LeftSide entities. If you try to migrate the database to the latest model state, you will get an index generated in the join table only for the left side of the association, i.e. on the LeftSideId column in the join table.

Now if you try to delete a RightSide entity, this will trigger the corresponding constraint in the database and could lead to a full table scan to find the necessary records on the left side. This scenario can be a performance issue for join table with lots of records.

To avoid this problem, Telerik Data Access implements support for defining explicit indexes for the right side column in the join table, the so called "value column". To create an explicit value index, you should use the HasValueIndex method. This method is available under the Telerik.OpenAccess.Metadata.Fluent.Advanced namespace.

MappingConfiguration<LeftSide> mapping = new MappingConfiguration<LeftSide>();
mapping.MapType( x => new
{
} ).ToTable( "LeftSide" );

mapping.HasProperty( x => x.Id )
   .HasFieldName( "id" )
   .IsIdentity( KeyGenerator.Guid )
   .ToColumn( "LeftSideId" );

mapping.HasProperty( x => x.Name )
   .HasFieldName( "name" )
   .ToColumn( "Name" );

mapping.HasAssociation<RightSide>(x => x.Items)
   .IsManaged()
   .MapJoinTable("MyJoinTable", (x, y) => new
                   {
                       LeftSideId = x.Id,
                       RightSideID = y.Id
                   })
   .HasSequenceColumn("MySequenceColumn")
   .HasValueIndex("MyValueIndex");

Dim mapping As New MappingConfiguration(Of LeftSide)()
mapping.MapType().ToTable("LeftSide")

mapping.HasProperty(Function(x) x.Id).HasFieldName("id").IsIdentity(KeyGenerator.Guid).ToColumn("LeftSideId")

mapping.HasProperty(Function(x) x.Name).HasFieldName("name").ToColumn("Name")

mapping.HasAssociation(Of RightSide)(Function(x) x.Items).
    IsManaged().
    MapJoinTable("MyJoinTable", Function(x, y) New With {Key .LeftSideId = x.Id, Key .RightSideID = y.Id}).
    HasSequenceColumn("MySequenceColumn").
    HasValueIndex("MyValueIndex")