Using Custom Join Table

This article is relevant to entity models that utilize the deprecated Visual Studio integration of Telerik Data Access. The current documentation of the Data Access framework is available here.

This topic demonstrates how to create many-to-many associations by using a custom join table. This approach gives you a full control over the configuration of the association.

Suppose, you have the following domain model:

Both entities are mapped to tables by using explicit mapping.

Creating a New Custom Join Table

To create a new custom join table:

1. In Model Schema Explorer, right-click the Tables node and select Add table.

   

2. This will open the Table Editor. Give the join table a name (e.g. CustomersOrders). Add two columns that will be used as foreign keys (e.g. OrderId and CustomerId). Specify both columns as primary keys, i.e. set the Identity option to true.

   

3. Click OK to create the table.

   

4. Select the join table in the Model Schema Explorer (e.g. CustomersOrders) and press F4 to open the Properties pane. Set the IsJoinTable property to True.

   

The custom join table is created. The next step is to create the many-to-many association.

Creating a New Many-To-Many Association

To use the custom join table in a many-to-many association:

1. Create a new association between your entities.
2. In the Association Editor, navigate to the Relational View.
3. Check the Use Join Table option.
4. From the drop-down, select the custom join table (e.g. CustomersOrders).

5. In the grid below the drop-down, set up the foreign key constraints. E.g. the CustomersOrders.CustomerId foreign key column will refer to the Customer.Id column. Respectively, the CustomersOrders.OrderId foreign key column will refer to the Order.Id column.

   

6. Finally, click OK to close the editor and create the association.

The final step is to migrate your databases schema to the latest model state by using the Update Database from Model wizard. The generated script should be similar to this:

-- OpenAccessModel.Customer
CREATE TABLE [Customer] (
   [NAME] varchar(255) NULL,               -- _name
   [Id] INT NOT NULL,                      -- _id
   CONSTRAINT [pk_Customer] PRIMARY KEY ([Id])
)
GO

-- System.Collections.Generic.IList`1 OpenAccessModel.Customer._order
CREATE TABLE [CustomersOrders] (
   [CustomerId] INT NOT NULL,
   [OrderId] INT NOT NULL,
   CONSTRAINT [pk_CustomersOrders] PRIMARY KEY ([CustomerId], [OrderId])
)
GO

-- OpenAccessModel.Order
CREATE TABLE [Order] (
   [OrderDate] datetime NOT NULL,          -- _orderDate
   [Id] INT NOT NULL,                      -- _id
   CONSTRAINT [pk_Order] PRIMARY KEY ([Id])
)
GO

CREATE INDEX [idx_CstmrsOrdrs_Or] ON [CustomersOrders]([OrderId])
GO

ALTER TABLE [CustomersOrders] ADD CONSTRAINT [ref_CstmrsOrdrs_Cs] 
FOREIGN KEY ([CustomerId]) REFERENCES [Customer]([Id])
GO

ALTER TABLE [CustomersOrders] ADD CONSTRAINT [ref_CstmrsOrdrs_Or] 
FOREIGN KEY ([OrderId]) REFERENCES [Order]([Id])
GO