LINQ to Entities supports three types of inheritance:
As Table per Concrete inheritance is not used as often as Table per Hierarchy and Table per Type inheritances, in this book we will only cover the first two inheritance types.
In Table per Hierarchy inheritance, there is a single database table that contains fields for both parent information and child information. With relational data, a discriminator column contains the value that determines which class any given record belongs to.
For example, consider a Persons table that contains everyone employed by a company. Some people are employees and some are managers. The Persons table contains a column named EmployeeType that has a value of 1 for managers and a value of 2 for employees; this is the discriminator column.
In this scenario, you can create a child entity of employees and populate the class only with records that have an EmployeeType value of 2. You can also remove columns that do not apply from each of the classes.
In our Northwind database, the Customers table contains all of the customers in different countries. Suppose that all customers share some common properties and customers from each country also have some unique properties of their own. We can then define a BaseCustomer entity class for all of the common properties of the customers and define a unique child entity class for each country.
We assume that all customers have the following properties:
CustomerIDCompanyNameContactNameContactTitleAddressCityRegionPostalCode
To simplify the example, we will define only two child entity classes in this example—one for customers in USA (called USACustomers) and another for customers in UK (UKCustomers). We assume that a USACustomer has one more property of Phone and a UKCustomer has one more property of Fax.
We will first model these entities with the LINQ to Entities designer:
- Open the entities conceptual model
Northwind.edmxand right-click on an empty space on the designer surface. Then, from the context menu, choose Update Model from Database… and add the Customers table to the model, in the same way as we did for the Products table in the previous chapter. Save the model and then from the Properties window of the Customers entity, change the entity class name from Customer to BaseCustomer (Entity Set Name should be changed to BaseCustomer automatically).
- Right-click on an empty space on the designer surface, then choose Add New | Entity… from the context menu.
- In the Add Entity window, enter
USACustomeras Entity Name, and select BaseCustomer as Base type. Click on OK to close this window.
- On the entity model designer surface, right-click on the Phone property of the BaseCustomer entity, and select Cut from the context menu.
- Still on the entity model designer surface, right-click on the Properties node of the USACustomer entity and select Paste from the context menu.
- Right-click on the Country property of the BaseCustomer entity and select Delete from Model from the context menu. We need to delete this property because we will use it as our discriminator.
- Now select the USACustomer entity on the model designer and go to the Mapping Details window (it should be next to your Output window or you can open it by navigating to View | Other Windows | Entity Data Model Mapping Details).
- On the Mapping Details window, click on <Add a Table or View> and select Customers from the drop-down list. Make sure Phone is mapped to Phone and Country is not mapped.
- Again in the Mapping Details window, click on <Add a Condition> and select Country from the drop-down list. Select = as the operator, and enter
USAas Value/Property. - Now save the model and we have finished modeling the base customer and USA customer entities. If you build the solution now you should see no errors.
Next we need to model the UKCustomer entity. This entity will inherit from the BaseCustomer entity, but will have an extra property of Fax.
- On the
Northwind.edmxentity designer surface, right-click on an empty space, then choose Add New | Entity… from the context menu. - On the Add Entity window, enter
UKCustomeras Entity Name and select BaseCustomer as Base type. Click on OK to close this window. - On the entity model designer surface, right-click on the Fax property of the BaseCustomer entity and select Cut from the context menu.
- Still on the entity model designer surface, right-click on the Properties node of the UKCustomer entity and select Paste from the context menu.
- Now select the UKCustomer entity on the model designer and go to the Mapping Details window (it should be next to your Output window or you can open it by navigating to View | Other Windows | Entity Data Model Mapping Details).
- In the Mapping Details window, click on <Add a Table or View> and select Customers from the drop-down list. Make sure Fax is mapped to Fax, and Phone and Country are not mapped.
- On the same window, click on <Add a Condition> and select Country from the drop-down list. Select = as the operator and enter
UKas Value/Property. - Now the TPH inheritance model is finished. The model for the customer part should be as shown in the following screenshot:
Save the model
by clicking on the Save button. Now you will find that three classes have been added to the model. The first class is the BaseCustomer class, which is a pure POCO class.
Note that its class body neither contains the properties, Phone and Fax, nor Country. This is because Phone is now contained by the USACustomer entity and Fax by UKCustomer, and Country is used as the discriminator for the inheritance.
The other two classes are for the derived classes; each has only one property:
public partial class USACustomer : BaseCustomer public partial class UKCustomer : BaseCustomer
Now we can write a query to show the inheritance between BaseCustomer and the two derived classes.
First we can retrieve all of the USA customers by using the is operator as follows:
var USACustomers1 = from c
in NWEntities.BaseCustomers
where c is USACustomer
select c;
We can also use the OfType operator to retrieve the same products, as follows:
var USACustomers2 = from c
in NWEntities.BaseCustomers.OfType<USACustomer>()
select c;
Console.WriteLine("Total number of USA customers: {0}",
USACustomers1.Count());
Console.WriteLine("Total number of USA customers: {0}",
USACustomers2.Count());Run the program and you will see both queries return 13.
We can also use the as operator to search for all the customers that are USA customers:
var USACustomers3 = from c
in NWEntities.BaseCustomers
select c as USACustomer;In all of the above three queries, Phone is a property of the returning item, which means it is of the USACustomer type. Also, all of the BaseCustomer properties are available because the returning item's datatype is a child of the BaseCustomer type.
Note
If you trace the SQL statements for the previous three queries, you will find that the first two queries are identical and very simple, but the third query is huge and very complex (it actually returns all customers and for non-USA customers, it returns null). Between the first two queries, the first one returns a variable of type BaseCustomer, but holds a list of objects of type USACustomer. You have to cast them to type USACustomer if you need to get the child properties. The second one is the best, if you need to retrieve all USA customers.
Similarly we can retrieve all UKCustomers and use its Fax property, as follows:
var UKCustomers = from c
in NWEntities.BaseCustomers.OfType<UKCustomer>()
select c;The method should be as follows:
static void TestTPHInheritance()
{
using(NorthwindEntities NWEntities = new NorthwindEntities())
{
var USACustomers1 = from c
in NWEntities.BaseCustomers
where c is USACustomer
select c;
var USACustomers2 = from c
in NWEntities.BaseCustomers.OfType<USACustomer>()
select c;
Console.WriteLine("Total number of USA customers: {0}",
USACustomers1.Count());
Console.WriteLine("Total number of USA customers: {0}",
USACustomers2.Count());
var USACustomers3 = from c
in NWEntities.BaseCustomers
select c as USACustomer;
foreach (var c in USACustomers3)
{
if (c != null)
{
Console.WriteLine("USA customer: {0}, Phone: {1}",
c.CompanyName, c.Phone);
}
}
var UKCustomers = from c
in NWEntities.BaseCustomers.OfType<UKCustomer>()
select c;
foreach (var c in UKCustomers)
Console.WriteLine("UK customer: {0}, Fax: {1}",
c.CompanyName, c.Fax);
}
}The output of this is shown in the following screenshot:
In Table per Type inheritance, there is a parent database table that contains fields for parent information and a separate database table that contains additional fields for child information. With relational data, a foreign key constraint links those tables together to provide the detailed information for each entity.
For example, let's consider the same Persons table that contains common properties for everyone employed by a company. Some people are employees and some are managers. All employee-specific information is saved in a separate table Employees, while all manager-specific information is saved in the Managers table.
In this scenario, you can create a parent entity for people and two child entities—one for employees and another for managers.
Again in our Northwind database, the Customers table contains all of the customers in different countries. Suppose that all customers share some common properties and customers from each country also have some unique properties of their own. We can then define a BaseCustomer entity class for all of the common properties of the customers and define a unique child entity class for each country.
We assume that all customers have the following properties:
CustomerIDCompanyNameContactNameContactTitleAddressCityRegionPostalCode
To simplify
the example, we will define only two child entity classes in this example—one for customers in USA (called USACustomers) and another for customers in UK (UKCustomers). We assume that USACustomer has one more property of Phone and UKCustomer has one more property of Fax.
However, this time we will create those two child entities from two new database tables, not from the same Customers table as we did in the last section.
We first need to create those two new database tables so that later on we can add them to our model.
- Open SQL Management Studio and execute the following SQL statements to create two new tables. These two statements also fill in these two new tables with some initial data from the
Customerstable:select CustomerID,Phone into USACustomers from Customers where Country = 'USA' select CustomerID,Fax into UKCustomers from Customers where Country = 'UK'
- Set
CustomerIDas the primary keys for both tables. - Add a foreign key relationship between the
USACustomerstable and theCustomerstable. TheCustomerIDcolumn should be used for the foreign key. Do the same for theUKCustomerstable. The foreign key mappings forUSACustomersshould be as shown in the following screenshot:
Now that we have the database tables ready, we need to go to the entity designer to add them to our model.
- From Visual Studio, open the
Northwind.edmxentity designer, right-click on an empty space and select Update Model from Database…. Add the two new tablesUSACustomersandUKCustomersto the model. Note that the entity names for these two tables areUSACustomer1andUKCustomer1as there are already two entities with the name ofUSACustomerandUKCustomer. - Because of the foreign keys between those two tables and the
Customerstable, there is an association betweenUSACustomer1andBaseCustomeras well as betweenUKCustomer1andBaseCustomer. Right-click on each of these two associations and delete them. This is because we don't want these two child tables to be associated with the base table; instead, we want them to be inherited from the base table.Note
With association, you can access all base customers' properties from the child entity through the navigation property
Customerand vice versa. Once you delete the associations, those navigation properties will also be deleted. For learning purpose, we are deleting these associations, but in your real project, you may want to keep them. - Then right-click on an empty space and select Add New | Inheritance… from the context menu. Specify BaseCustomer as the base entity and USACustomer1 as the derived entity. Click on OK to close this window. Also add an inheritance between
UKCustomer1andBaseCustomer. - Select CustomerID from the USACustomer1 entity and delete it. Also delete CustomerID from the UKCustomer1 entity.
- Select the USACustomer1 entity, go to the Mapping Details window, and make sure CustomerID : nchar is maped to CustomerID : String. Verify the same mapping for the CustomerID property in the UKCustomer1 entity.
The finished model should now contain eight entities. The part of the model that contains the two new customer entities should look like the following screenshot:
Save the model and you will find that two classes have been added to the model, each having only one property:
public partial class USACustomer1 : BaseCustomer public partial class UKCustomer1 : BaseCustomer
Now we can write a query to show the inheritance between BaseCustomer and the two new derived classes.
For example, the following code will retrieve the first USA customer from the USACustomers table and print out its phone:
var usaCustomer1 = (from c
in NWEntities.BaseCustomers.OfType<USACustomer1>()
select c).FirstOrDefault();
if (usaCustomer1 != null)
{
var phone1 = usaCustomer1.Phone;
Console.WriteLine("Phone for USA customer1:{0}",
phone1);
}The following code will retrieve and print out the first UK customer's fax:
var ukCustomer1 = (from c
in NWEntities.BaseCustomers.OfType<UKCustomer1>()
select c).FirstOrDefault();
if (ukCustomer1 != null)
{
var fax1 = ukCustomer1.Fax;
Console.WriteLine("Fax for UK customer1:{0}",
fax1);
}In the same way, as with the test result in previous sections, when we used TPH inheritance in the previous query to the USACustomer1 entity, Phone was a property of the returning item and all of the BaseCustomer properties were also available because the returning item's datatype is a child of the BaseCustomer type. To the UKCustomer1 entity, it has all of the properties from the BaseCustomer entity plus the Fax property.
Then, what about the Phone column in the BaseCustomer entity? We know that there is a Phone column in the database table Customers and now USACustomer1 inherits BaseCustomer. Does this mean the Phone property in the child entity overrides the Phone property in the parent entity? The answer is no. Actually, there is no Phone property in the parent entity BaseCustomer, because we have moved it to another child entity USACustomer.
We can get the Phone value in the database table Customers through the USACustomer entity, like in the following code snippet:
var usaCustomer = (from c
in NWEntities.BaseCustomers.OfType<USACustomer>()
where c.CustomerID == usaCustomer1.CustomerID
select c
).SingleOrDefault();There is no compiling error for this code snippet, but if you run the program now, you will get an error as shown in the following screenshot:
This is because within the same object context, there should be only one entity for a primary key. In our model, both USACustomer and USACustomer1 share the same primary key customerID. So if two entity objects are pointing to the same record in the database, we have a problem.
To solve this problem, we can change the preceding code to construct a new objet, which will not be managed by the object context:
var usaCustomer = (from c
in NWEntities.BaseCustomers.OfType<USACustomer>()
where c.CustomerID == usaCustomer1.CustomerID
select new { c.CustomerID, c.Phone }
).SingleOrDefault();If you run the program, you will see that both phones from the usaCustomer1 and usaCustomer for the first USA customer are (503) 555-7555, though we know they are from two different database tables. If you are not sure if they are all retrieved from the right tables, you can go to the database, change one of the phones to a different value, run it again, and verify that each entity is from the correct database table.
The method should be as follows:
static void TestTPTInheritance()
{
using(NorthwindEntities NWEntities = new NorthwindEntities())
{
var usaCustomer1 = (from c
in NWEntities.BaseCustomers.OfType<USACustomer1>()
select c).FirstOrDefault();
if (usaCustomer1 != null)
{
var phone1 = usaCustomer1.Phone;
Console.WriteLine("Phone for USA customer1:{0}",
phone1);
}
var ukCustomer1 = (from c
in NWEntities.BaseCustomers.OfType<UKCustomer1>()
select c).FirstOrDefault();
if (ukCustomer1 != null)
{
var fax1 = ukCustomer1.Fax;
Console.WriteLine("Fax for UK customer1:{0}",
fax1);
}
/*
var usaCustomer = (from c
in NWEntities.BaseCustomers.OfType<USACustomer>()
where c.CustomerID == usaCustomer1.CustomerID
select c
).SingleOrDefault();
*/
var usaCustomer = (from c
in NWEntities.BaseCustomers.OfType<USACustomer>()
where c.CustomerID == usaCustomer1.CustomerID
select new { CustomerID = "new PK", c.Phone }
).SingleOrDefault();
if (usaCustomer != null)
{
var phone = usaCustomer.Phone;
Console.WriteLine(
"Phone for USA customer from Customers table:{0}",
phone);
}
}
}The output of this is shown in the following screenshot (note the phone number for the first customer has been changed to (503) 555-7000 in the USACustomers table, to distinguish it from the phone in the Customers table):
