Database Reference
In-Depth Information
Solution
Suppose that we have a Member table, as depicted in Figure
6-15
. The Member table describes members in our club.
In our model, we want to represent adult members, senior members, and teen members as derived types using Table
per Type inheritance.
Figure 6-15.
The Member table describing members in our club
Entity Framework supports Table per Hierarchy inheritance based on the conditions
=
,
is null
, and
is not
null
. Simple expressions such as
<
,
between
, and
>
are not supported. In our case, a member whose age is less than 20
is a teen (the minimum age in our club is 13). A member between the ages of 20 and 55 is an adult. And, as you might
expect, a member over the age of 55 is a senior. To create a model for the member table and the three derived types,
do the following:
1.
Add a new ADO.NET Entity Data Model to your project, and import the Member table.
Right-click the Member entity, and select Properties. Set the Abstract attribute to
true
.
This marks the Member entity as abstract.
2.
3.
Create the stored procedures in Listing 6-31. We will use them to handle the Insert,
Update, and Delete actions on the entities we'll derive from the Member entity.
Listing 6-31.
Stored Procedures for the Insert, Update, and Delete Actions
create procedure [chapter6].[InsertMember]
(@Name varchar(50), @Phone varchar(50), @Age int)
as
begin
insert into Chapter6.Member (Name, Phone, Age)
values (@Name,@Phone,@Age)
select SCOPE_IDENTITY() as MemberId
end
go
create procedure [chapter6].[UpdateMember]
(@Name varchar(50), @Phone varchar(50), @Age int, @MemberId int)
as
begin
update Chapter6.Member set Name=@Name, Phone=@Phone, Age=@Age
where MemberId = @MemberId
end
go
