Thursday 16 February 2012

Adding Abstract Entity in Entity Framework

This post will show how to create an Abstract entity and its derived entities in Entity Framework 4.1.

Suppose we have an 'Employee' table as shown below that is set as a table to store two different types of employees, namely staffs and managers. For simplicity; staffs are under managers, both staffs and managers have 'FirstName' and 'LastName', only staffs have 'DeskNumbers' while managers have 'OfficeRoomNumbers'. They are differentiate by 'Type' flag. We could see that 'Table per Hierarchy' (TPH) style is used here.

We would like to create these mappings of staffs and managers to the 'Employee' table in Entity Framework designer. To implement this 'Table per Hierarchy' style, we will create an abstract Employee entity and have Staff and Manager entities as the derived/child entities from Employee entity.

Assume we create Employee entity from scratch:
1. Right click an empty space on designer
2. Select Add > Entity
3. Type 'Employee' as Entity name
4. Leave Base type as '(None)'
5. On the 'Key Property' section, type 'EmployeeID' as the Property name. This is the same name as the primary key column name in the database table
6. Click 'OK' then a new entity is added
7. A new entity called 'Employee' is added
8. Right click the entity then select Properties
9. Change 'Abstract' value to 'True.

Then we create the child entities:
1. Right click an empty space on designer
2. Select Add > Entity
3. Type 'Staff' as Entity name
4. On Base type dropdown select' Employee'. This will make 'Key Property' section disabled.
5. Click 'OK' then the new entity is added
6. Repeat the same process to add 'Manager' entity

Next we need to add properties that are common to both derived entities on the abstract entity. In this case we need to add 'FirstName' and 'LastName' as Scalar Properties on Employee entity. Make sure to modify the 'Type' and 'MaxLength' values of the properties according to their data types in database.

After we add the common properties, we need to add properties that are specific to the derived entities. Add 'DeskNumber' Scalar Property to Staff entity and 'OfficeRoomNumber' Scalar Property to Manager entity. Right click each of the newly added Scalar Properties then select Properties. Modify the 'Type' of the properties and make sure the 'Nullable' value is set to 'True'.

After doing all of those, we will have this:

Finally we need to map the entities to the table in database. First we map the abstract entity.
1. Right click 'Employee' entity then select Table Mapping
2. Click '<Add a Table or View>' then select 'Employee' from dropdown
3. Then you will see under 'Column Mappings' all the columns from 'Employee' table in the database are displayed on the left side, while the entity properties are displayed on the right side. All matched properties are automatically mapped.

The derived entities need to be mapped as well. They will be mapped to the same table as the abstract entity.
1. Right click 'Staff' entity then select Table Mapping
2. Click '<Add a Table or View>' then select 'Employee' from dropdown
3. Click '<Add a Condition>' then select 'Type' from dropdown
4. Type 'S' as the ' When Type' value
5. Do the same process with 'Manager' entity, however the 'When Type' value will be 'M'

Thursday 2 February 2012

Adding Complex Type in Entity Framework

This post will show how to create and add Complex Type to entity class in Entity Framework 4.1.

Let's say that we have a Customer table that looks like the following:
We can see that the table stores billing and shipping addresses information. The addresses information have similar structure and data types. Other tables in the same database might have other addresses information similar to these as well. Therefore, instead of handling each of the address part individually for each address information, we can use a common type for these addresses. In Entity Framework, we call this Complex Type.

So now we are going to add 'AddressInfo' Complex Type. Here are the steps to do that:
1. Right click an empty space on designer
2. Select Add > Complex Type
3. A new complex type is added, rename it to 'AddressInfo'
4. To add its properties, right click the complex type then select Add > Scalar Poperty > String
5. A new property is added, rename it to 'Address'
6. then right click 'Address', select Properties
7. change 'Max Length' value to 150
8. Repeat steps 4-7 above to add 'Suburb', 'Country' and 'PostCode'

After adding the Complex Type, we need to add that to the 'Customer' entity. Say that we want to create 'Customer' entity manually then add the Complex Type to it:
1. Right click an empty space on designer
2. Select Add > Entity
3. Type 'Customer' as Entity name
4. On the 'Key Property' section, type 'CustomerID' as the Property name. This is the same name as the primary key column name in the database table
5. Click 'OK' then a new entity is added
6. To add 'FirstName' property, right click the entity then select Add > Scalar Property
7. A new property is added, rename it to 'FirstName'
8. Right click 'FirstName' then select Properties
9. Change the 'Max Length' value to 50
10. Repeat the steps 6-9 to add 'LastName' property
11. To add 'BillingAddress', select Add > Complex Property
12. A new property is added, rename to 'BillingAddress'
13. Right click 'BillingAddess' then select Properties
14. Make sure the 'Type' is 'AddressInfo'
15. Repeat steps 11-14 to add 'ShippingAddress'

Finally we need to map the entity and its Complex Type and Scalar properties to the actual columns in the database.
1. Right click 'Customer' entity then select Table Mapping
2. Click '<Add a Table or View>' then select 'Customer' from dropdown
3. Then you will see under 'Column Mappings' all the columns from 'Customer' table in the database displayed on the left side, while the entity properties are displayed on the right side. All matched properties are automatically mapped, however our Complex Types are not recognized.
4. Click on the 'Value / Property' column of 'BillingAddress' then select 'BillingAddress.Address'
5. Repeat the process for other columns that will be mapped to Complex Type properties. When we have done all the mappings we will have this: