Introduction
One of the things that happens a lot in databases but less in object oriented software is normalization. In databases we often split one logical entity into multiple entities to remove redundant data. However in our client that uses this data it’s often the other way around.
Looking at ADO.Net entity framework I wondered if it was possible to map several tables to one entity. It can be done but it’s not as flexible as one would like. I wrote down a short tutorial on how to map multiple tables with an alternative that you can use if the scenario I described here doesn’t work for you.
Constraints
Although it is possible to map multiple tables to one entity you need to be aware of the limitations. There is a nasty limitation for mapping multiple tables to one entity. You need to create one-to-one relations between the tables you want to be mapped together. Otherwise it simply doesn’t work. There is an alternative to fix this problem however, which I will talk about later.
Specifying the conceptual model
The conceptual model is pretty easy to setup. Specify one entity containing all the properties from the separate tables. There is no need to specify navigation properties for the separate tables as at the conceptual level there are no separate tables anymore for the entity. As a sample I made a customer entity, I marked the various parts so that it’s easier to see where the parts will come from.
<Schema Namespace=”CustomerDBModel” Alias=”Self” >
<EntityContainer Name=”CustomerDBEntities”>
<EntitySet Name=”Customer” EntityType=”CustomerDBModel.Customer” />
</EntityContainer>
<EntityType Name=”Customer”>
<Key>
<PropertyRef Name=”CustomerId” />
</Key>
<Property Name=”CustomerId” Type=”Int32″ Nullable=”false” />
<Property Name=”FirstName” Type=”String” Nullable=”false” MaxLength=”50″ Unicode=”false” />
<Property Name=”LastName” Type=”String” Nullable=”false” MaxLength=”50″ Unicode=”false” />
<!– Address information –>
<Property Name=”Street” Type=”String” Nullable=”false” MaxLength=”255″ Unicode=”false” />
<Property Name=”StreetNumber” Type=”String” Nullable=”false” MaxLength=”10″ Unicode=”false” />
<!– City and state information –>
<Property Name=”ZipCode” Type=”String” Nullable=”false” MaxLength=”10″ Unicode=”false” />
<Property Name=”City” Type=”String” Nullable=”false” MaxLength=”50″ Unicode=”false” />
<Property Name=”State” Type=”String” Nullable=”false” MaxLength=”50″ Unicode=”false” />
<Property Name=”Country” Type=”String” Nullable=”false” MaxLength=”50″ Unicode=”false” />
</EntityType>
</Schema>
</edmx:ConceptualModels>
Mapping the store model to the conceptual model
The store model features the various tables that make up the database with the associations between them. I will omit the mapping here as it’s all standard stuff. The more interesting part is the mapping between the store and the conceptual model. All the magic needed to load and save the data of the conceptual entity is done here.
You start out with a standard scenario where you map the customer store entity to the store conceptual entity.
<edmx:Mappings>
<Mapping Space=”C-S” >
<EntityContainerMapping StorageEntityContainer=”dbo” CdmEntityContainer=”CustomerDBEntities”>
<EntitySetMapping Name=”Customer”>
<EntityTypeMapping TypeName=”IsTypeOf(CustomerDBModel.Customer)”>
<MappingFragment StoreEntitySet=”Customer”>
<ScalarProperty Name=”CustomerId” ColumnName=”CustomerId” />
<ScalarProperty Name=”FirstName” ColumnName=”FirstName” />
<ScalarProperty Name=”LastName” ColumnName=”LastName” />
</MappingFragment>
…
The mapping fragment here is important as it tells the entity framework where the properties of the conceptual entity are coming from. You can have multiple mapping fragments that come from various store entity sets. Adding the mapping fragment for the street and streetnumber is easy, because we have a one-on-one relation between customer and address. The same goes for the zipcode,city,state and country properties. The final mapping looks like this:
<edmx:Mappings>
<Mapping Space=”C-S” >
<EntityContainerMapping StorageEntityContainer=”dbo” CdmEntityContainer=”CustomerDBEntities”>
<EntitySetMapping Name=”Customer”>
<EntityTypeMapping TypeName=”IsTypeOf(CustomerDBModel.Customer)”>
<MappingFragment StoreEntitySet=”Customer”>
<ScalarProperty Name=”CustomerId” ColumnName=”CustomerId” />
<ScalarProperty Name=”FirstName” ColumnName=”FirstName” />
<ScalarProperty Name=”LastName” ColumnName=”LastName” />
</MappingFragment>
<MappingFragment StoreEntitySet=”CityState”>
<ScalarProperty Name=”CustomerId” ColumnName=”CustomerId”/>
<ScalarProperty Name=”ZipCode” ColumnName=”ZipCode” />
<ScalarProperty Name=”City” ColumnName=”City” />
<ScalarProperty Name=”State” ColumnName=”State” />
<ScalarProperty Name=”Country” ColumnName=”Country” />
</MappingFragment>
<MappingFragment StoreEntitySet=”Address”>
<ScalarProperty Name=”CustomerId” ColumnName=”CustomerId” />
<ScalarProperty Name=”Street” ColumnName=”Street” />
<ScalarProperty Name=”StreetNumber” ColumnName=”StreetNumber” />
</MappingFragment>
</EntityTypeMapping>
</EntitySetMapping>
</EntityContainerMapping>
</Mapping>
</edmx:Mappings>
An alternative solution
The solution I described allows you to map multiple tables with one-on-one relations between them, it also requires that you have one “master” table to which the other tables are linked. Otherwise it doesn’t work. A different solution to mapping multiple tables to one entity is to create a view on the database that contains a denormalized version of the entity that you need and map that view. inserting, updating and deleting data is a bit more tricky in this scenario, but that can be solved using triggers on the view or by using stored procedures, which I like better anyway.
Conclusion
The mappings that are possible in ADO.NET are fine, but sometimes it leaves something to wish for. Certainly when it comes to mapping multiple tables to one entity. However the alternatives are a good way to go, so there’s no real problem when you need to do this kind of thing. I’m interested to see what Microsoft will come up with to really solve this scenario using the ADO.Net entity framework.