Database Reference
In-Depth Information
Figure 11-5.
A model for hotel reservations
You want to retrieve the total number of reservations and the total room revenue for each visitor. Because you will
need this information in several places, you want to create a model-defined function that takes in a search parameter
and returns a collection of anonymous types containing the summary information for each visitor.
To create and use this model-defined function, do the following:
1.
Right-click the .edmx file in the Solution Explorer, and click Open With
➤
XML Editor. This
will open the .edmx file in the XML Editor.
Insert the code in Listing 11-9 just below the
<Schema>
tag in the conceptual models
section of the .edmx file. This defines the function in the model.
2.
Listing 11-9.
The VisitorSummary() Model-Defined Function
<Function Name="VisitorSummary">
<Parameter Name="StartDate" Type="Edm.DateTime" />
<Parameter Name="Days" Type="Edm.Int32" />
<ReturnType>
<CollectionType>
<RowType>
<Property Name="Name" Type="Edm.String" />
<Property Name="TotalReservations" Type="Edm.Int32" />
<Property Name="BusinessEarned" Type="Edm.Decimal" />
</RowType>
</CollectionType>
</ReturnType>
<DefiningExpression>
Select
r.Visitor.Name,
COUNT(r.ReservationId) as TotalReservations,
SUM(r.Cost) as BusinessEarned
from EFRecipesEntities.Reservations as r
where r.ReservationDate between StartDate and
AddDays(StartDate,Days)
group by r.Visitor.Name
</DefiningExpression>
</Function>
