Without a 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 without a join table. You are going to use two one-to-many associations instead.

Suppose, you have the following domain model:

All three entities are mapped to tables by using explicit mapping. The CustomerOrder class will be used to map the many-to-many association between the Customer and Order classes. You are going to create two one-to-many associations - from CustomerOrder to Customer and from CustomerOrder to Order:

1. Open the Toolbox and select the Association item.

2. Select the objects to be included in the association by clicking the CustomerOrder entity first and then clicking the Customer entity. Doing this will open the Association Editor dialog.

   

3. You should specify the foreign key property for the child (Source) class. You are creating an one-to-many association between CustomerOrder and Customer. That's why, you need to select the CustomerId property as a foreign key.

4. Click OK to create the first association.
5. The next step is to create the second one-to-many association. Repeat step 2. However, this time the Order entity will be the second entity in the relation.

6. In the Association Editor dialog, select OrderId as a foreign key.

   

7. Click OK to create the second association.

   

Next Steps

The next step is to create/migrate your database schema to the latest model state. For that purpose, you will need to use the Update Database from Model Wizard. The generated script should be similar to this.

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

-- OpenAccessModel.CustomerOrder
CREATE TABLE [CustomersOrders] (
   [OrderId] INT NOT NULL,                 -- _orderId
   [Notes] ntext NULL,                     -- _notes
   [CustomerId] INT NOT NULL,              -- _customerId
   CONSTRAINT [pk_CustomersOrders] PRIMARY KEY ([OrderId], [CustomerId])
)
GO

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

CREATE INDEX [idx_CustomersOrders_CustomerId] ON [CustomersOrders]([CustomerId])
GO

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

ALTER TABLE [CustomersOrders] ADD CONSTRAINT [ref_CustomersOrders_Customers] 
FOREIGN KEY ([CustomerId]) REFERENCES [Customers]([Id])
GO

ALTER TABLE [CustomersOrders] ADD CONSTRAINT [ref_CustomersOrders_Orders] 
FOREIGN KEY ([OrderId]) REFERENCES [Orders]([Id])
GO