Multi-Table Entities

Fluent Mapping API allows you to map a class to more than one table in the database. For example, the class User has several properties of type byte[] to store different images for the user. To avoid storing the images in the User table, it is possible to specify another table for these properties only.

To map a class property to a different table, you need to use the second overload of the ToColumn() method that accepts two string parameters - column name and table name.

To use this overload of the ToColumn() method, you need to use/import the Telerik.OpenAccess.Metadata.Fluent.Advanced namespace.

There are several different places where the new method can be used - on HasProperty, HasAssociation or IsIdentity method call. If you use the method after HasProperty or HasAssociation call, the corresponding column will be created in the specified table. The usage of ToColumn after IsIdentity is more specific. In this case, the primary key column will not be stored in an additional table. However, it allows you to specify the names of the primary key columns for the additional tables that will be created.

The following example demonstrates how to map an entity to multiple tables. The Product class is mapped to a table named "Products". Next, the Id property is set as an identity. Note how the ToColumn method is used in this case. It specifies that the primary key column for the Categories table will be named CategoryId and the primary key column for the Orders table will be named OrderId. The last two lines, specify that the Quantity and Name properties will be stored respectively in the Orders and Categories tables.

MappingConfiguration<Product> configuration = new MappingConfiguration<Product>();
configuration.MapType().ToTable( "Products" );
configuration.HasProperty( x => x.Id ).IsIdentity()
               .ToColumn( "CategoryID", "Categories" )
               .ToColumn( "OrderID", "Orders" );
configuration.HasProperty( x => x.Quantity ).ToColumn( "Quantity", "Orders" );
configuration.HasProperty( x => x.Name ).ToColumn( "Name", "Categories" );

Dim configuration As New MappingConfiguration(Of Product)()
configuration.MapType().ToTable("Products")
configuration.FieldNamingRules.AddPrefix = "_"
configuration.HasProperty(Function(x) x.Id).
    IsIdentity().
    ToColumn("CategoryID", "Categories").
    ToColumn("OrderID", "Orders")
configuration.HasProperty(Function(x) x.Quantity).ToColumn("Quantity", "Orders")
configuration.HasProperty(Function(x) x.Name).ToColumn("Name", "Categories")

Telerik Data Access will create three tables for the Product class - Products, Orders and Categories. The Products table will contain the Id and Description properties. The Orders table will contain the value for the Quantity property. In the table Categories will be stored the Name property. Also in this special scenario the ToColumn method is used to specify the primary key names in the additional tables. For Categories, it is CategoryID and for Orders - the primary key column is OrderID.