On part 1 and part 2 I explored how to apply 2 different inheritance models (TPH & TPT) in Entity Framework. In this part I'm going to demonstrate associations between 2 base entities. And how to filter end properties to return specific type (sub entity). Download the sample.
Review Association Ends:
In previous part when I added department tables to the entity model diagram, an associations between Person and Department entities was automatically defined. This relation actually demonstrate the relation between Administrator and Department. It is one-to-many relationship where one person can be administrator of one or many departments.
A collection of Departments is created on Person table and therefor it is inherited on all sub entities! But this make no sense, as a student or instructor has nothing to do with this collection. Beside on the other end at Department a property of type Person is created -I renamed it to Administrator-, while this shouldn't be just any Person, this Person must be of type Administrator.
A few modification should take place in here. The existing association should be deleted and the association ends should be define between Administrator Entity and Department Entity. To do that follow the following steps:
- Delete the current Association between Person and Department.
- Right Click on Administrator Entity, select Add -> Association.
- On Add Association dialog window, optionally you can rename Association Name to "FK_Department_Administrator".
- Confirm that the following information on End at the left hand side:
Entity is Administrator, and Multiplicity is 0..1 (Zero or One) -this is actually because Administrator field in Department table allows Null-. You'll rename the Navigation Property on step 6. - On the other End (right hand side), select Department for Entity, and leave the Multiplicity as it is * (Many). Do not change the Navigation Property name at this End.
- Rename the Navigation Property under the left hand side End to Departments.
- Click Ok.
This is the diagram after applying the above modifications.
Define Association Mapping:
I have to define mapping for the association ends. Will do that using Mapping pane:
- Right Click on the Association between Administrator and Department and select Table Mapping from the context menu.
- On Mapping Details pane, under Association select the foreign key entity which is Department.
- Confirm that Department.DepartmentID is mapped to DepartmentID Column. And for Administrator.PersonID property select Administrator column.
Note: I noticed an issue after applying the above steps. When you validate the diagram immediately after completing the above you'll get an error from EF. For some reason when I change the multiplicity from 0..1 (Zero or One) to 1 (One) and then change it back to 0..1 (Zero or One) the issue disappears. I did that using property window of the Association.
Build Specific Department Query:
As you can see, Departments collection returns all departments of all types. What if you want to return a specific type of departments such as only engineering ones? I raised this question on EF Forums and EF team really responded to that very quickly. To achieve this goal you would add additional properties to the Administrator Entity Class -using another cs file and partial class- Below is the the code that help you achieve this:
public IQueryable<DeptBusiness> BusinessDepartments
{
get
{
return this.Departments.CreateSourceQuery().OfType<DeptBusiness>();
}
}It is that simple. And this will generate a specific SQL query to return only Instances of DeptBusiness from DeptBusiness table of course joined with Department base table.
You can read more about CreateSourceQuery Method and OfType<T> method on MSDN. But the following was taken from the remarks about CreateSourceQuery method documentation:
"This property is used to obtain a new instance of ObjectQuery<T> that returns the same set of objects. This is useful as the starting point for a more complex join, union, or filtered query".
And OfType<T> remarks says:
"OfType<TResultType) is used to filter query results by a specific entity or complex type. This supports an Entity Data Model (EDM) with object inheritance"
Conclusion:
There are lots of things to walk through and discover about Entity Framework. In those 3 parts I explored 2 important subjects about Inheritance and Associations. Feel free to leave an comment and share your ideas and experience regarding Entity Framework.
I have an idea of doing a repost to my custom paging using LINQ, but this time using LINQ to Entities.
Click here to download the sample project.