If the schema of the return type matches up exactly with an existing type in your model, you are a few clicks away from mapping the function. However, there is one caveat to this. The function truly expects an exact match. If you have changed property names in entities and they do not match column names being returned, the function will fail.

One function in the model that demonstrates this problem is ActivitiesonaTrip. Example 13-1 shows the database procedure for this function. Because of the SELECT *, the procedure returns all of the columns from Activities.

SELECT * FROM [Activities] WHERE activityid IN (SELECT ActivityID
FROM [EventActivities] WHERE eventid=@tripid)

In the model, the Activity entity has a one-to-one mapping to the Activities table, so the fields and properties should line up exactly. The Activity entity has the same fields; or does it? The field names in the Activities table are ActivityID, Activity, imagepath, and Category. You may recall that when changing the original entity name from Activities to Activity, there was a conflict with the property named Activity, so you changed the property name to ActivityName. Even this minor change causes the function to fail when it attempts to match up the results of the returned data with the Activity entity.

You’ll be able to implement the mapping function in the model, but when you try to execute the function you will get this error:

The data reader is incompatible with the specified 'BAModel.Activity'. A member of
the type, 'ActivityName', does not have a corresponding column in the data reader
with the same name.

Because neither the model nor the Designer gives you an opportunity to define the mapping between the results and Activity, you can’t provide the necessary information to make this work.

What about inherited types? If you have a procedure whose results match up with a derived type such as Customer, you can map the function in the Designer with no problem. The CustomersWhoTravelledinDateRange stored procedure returns all of the appropriate fields to match up with the Customer type. This includes fields from the Customer table, fields from the Contact table, and fields from the ContactPersonalInfo table.

PROCEDURE  CustomersWhoTravelledinDateRange
--returns customer records with contact info for customers
@startdate DATETIME,
@enddate datetime

AS

SELECT Customers.ContactID, Customers.PrimaryDesintation,
       Customers.CustomerTypeID, Customers.InitialDate,
       Customers.SecondaryDestination, Customers.PrimaryActivity,
       Customers.SecondaryActivity, Customers.Notes, Contact.FirstName,
       Contact.LastName, Contact.Title, Contact.AddDate,
       Contact.ModifiedDate, ContactPersonalInfo.BirthDate,
       ContactPersonalInfo.HeightInches,
       ContactPersonalInfo.WeightPounds,
       ContactPersonalInfo.DietaryRestrictions,Contact.TimeStamp
FROM   Customers INNER JOIN Contact
       ON Customers.ContactID = Contact.ContactID
       INNER JOIN ContactPersonalInfo
       ON Customers.ContactID = ContactPersonalInfo.ContactID
WHERE customers.contactid IN
 (SELECT Customers.ContactID
  FROM Customers INNER JOIN Reservations
       ON Customers.ContactID = Reservations.ContactID
       INNER JOIN Events ON Reservations.EventID = Events.EventID
  WHERE events.startdate>=@startdate AND events.startdate<=@enddate
  GROUP BY Customers.contactid)

You can use the Model Browser to create a function import for this stored procedure and point the return type to the Customer entity. You can test the function with the code in Example 13-2.

VB
Using context As New BAEntities
  Dim customers = context.CustomersWhoTravelledinDateRange _
    (New Date(2006, 1, 1), New Date(2006, 12, 31))
End Using

C#
using (BAEntities context = new BAEntities())
{
  var customers = context.CustomersWhoTravelledinDateRange=
   (new DateTime(2006, 1, 1), new DateTime(2006, 12, 31));
}

More commonly, your existing stored procedures will not conveniently return results that match up with a predefined entity. Unfortunately, the Designer does not directly support these procedures. However, there are two ways to incorporate them into your model.

The first is to create an entity that matches up with the returned data. This is a little involved because the model requires every entity to map to something in the store layer. Therefore, although creating an entity in the conceptual layer is simple, you’ll need to manually create a matching entity in the store layer and then map the two. In effect, you are creating in the Store Schema Definition Layer (SSDL) a “fake” table; a representation of a table that does not actually exist in the database. This isn’t so bad for a handful of stored procedures, but it may become tiresome if you have a lot of stored procedures of this type.

If you have control of the database, a “trick” to get around the problem of stored procedures that return randomly shaped results is to create a view in the database that returns data of the same structure as the stored procedure. Remember that views become read-only entities in the model.

Once you have done this, you now have two roads you can follow.

The first is to just use the view in your model and forget the stored procedure. A nice benefit of this is that a view is composable, whereas the function derived from the stored procedure is not. You can write queries against the view and those queries will become native store commands, executed on the server. When using the function, though, you can call the function and you can even use it in a query; however, the function itself, as you saw in Chapter 6, will execute the stored procedure on the server, and then the results will be further manipulated on the client side by the rest of the query operators. This could result in very inefficient queries if your stored procedure returns many more entities than your query specifies.

If you have Insert, Update, and Delete procedures that align with the results of that view, you can map them back to that new entity using function mapping and use it as your object. If you do this, you’ll want to remove the entity that this is replacing so that you don’t have update collisions.

The other path to follow is useful if you still want to use the function that represents the read stored procedure. You can map the FunctionImport to that entity.

However, you are still looking at a hack here, and this particular hack of using a view leaves you with your results sitting in a new entity type.

Let’s walk through the scenario with the PaymentsforPeriod stored procedure. This procedure pulls fields from a number of tables defined by INNER JOINs. The results do not match up with any entities in the model. Example 13-3 shows the SQL for this procedure.

PROCEDURE PaymentsforPeriod
--returns payment, reservation, trip, and contact information
@startDate AS DATETIME,
@endDate AS DATETIME

AS

SELECT     Payments.PaymentDate, Payments.Amount,
           Reservations.ReservationDate, Contact.FirstName,
           Contact.LastName, Events.StartDate, Events.EndDate,
           Locations.LocationName
FROM       Payments INNER JOIN
           Reservations ON Payments.ReservationID =
           Reservations.ReservationID INNER JOIN
           Contact ON Reservations.ContactID = Contact.ContactID
           INNER JOIN Events ON Reservations.EventID = Events.EventID
           INNER JOIN Locations ON Events.LocationID =
             Locations.LocationID
WHERE payments.[PaymentDate] BETWEEN @startdate AND @enddate

The problem with this procedure is that it returns fields with the original column names from the table, and these don’t match the modified property names in the entity.

You could create a new view in the database, such as vPaymentsforPeriod, that matches the procedure with the exception that it takes no input parameters and has no WHERE clause. When this view is pulled into the model, you will have a new entity named vPaymentsforPeriod. Now, as shown in Figure 13-1, you can simply create a FunctionImport from the PaymentsforPeriod function that returns a vPaymentsforPeriod type.

Figure 13-1. Mapping a function to an entity that was created from a view

Now you can call that function and work with the read-only results. Example 13-4 shows this function being called in code.

VB
Using context As New BAEntities
  Dim pmts = context.PaymentsforPeriod
   (New Date(2008, 1, 1), New Date(2008, 12, 31))
End Using

C#
using (BAEntities context = new BAEntities())
{
  var pmts = context.PaymentsforPeriod
   (new DateTime(2008, 1, 1), new DateTime(2008, 12, 31));
}

The Entity Framework does not directly support queries that return multiple resultsets either. However, Colin Meek, one of the members of the Entity Framework team, created a project called EFExtensions that contains a method for using stored procedures that return multiple resultsets. In its current iteration, each resultset can match up with an existing entity or it can return arbitrary types. You can find EFExtensions on the MSDN Code Gallery at http://code.msdn.microsoft.com/EFExtensions/. Colin wrote an in-depth explanation of how these extensions work, along with a walkthrough of his sample application, on his blog, at http://blogs.msdn.com/meek/archive/2008/03/26/ado-entity-framework-stored-procedure-customization.aspx/.

Although the Designer supports queries that return primitive types, such as Int32 and String, the Designer does not generate code for them. Therefore, you won’t be able to call the function directly from the context, as you saw with some of the functions discussed earlier in this chapter.

However, you can use EntityClient to call these function imports.

The TripSalesforDateRange procedure shown in Example 13-5 returns a scalar value representing the total sales from reservations made within a specific date range.

PROCEDURE TripSalesforDateRange
    @startdate DATETIME,
    @enddate datetime
AS
SET NOCOUNT ON;
SELECT     SUM(Events.TripCostUSD) AS Expr1
FROM       Reservations INNER JOIN
           Events ON Reservations.EventID = Events.EventID
           WHERE reservationdate BETWEEN @startdate AND @enddate

When creating a FunctionImport from this, you would specify that the return type is a scalar value of type Int32, as shown in Example 13-6.

VB
Private Sub ScalarFunction()
  Dim result As Int32
  Dim eConn = New EntityConnection("Name = BAEntities")
  Dim eComm = eConn.CreateCommand
  With eComm
    .CommandType = CommandType.StoredProcedure
    .CommandText = "BAEntities.TripSalesforDateRange"
    .Parameters.AddWithValue("startdate", New Date(2006, 1, 1))
    .Parameters.AddWithValue("enddate", New Date(2006, 12, 31))
    eConn.Open()
    result = Convert.ToInt32(.ExecuteScalar())
    eConn.Close()
  End With
End Sub

C#
private static void ScalarFunction()
{
  Int32 result = 0;
  var eConn = new EntityConnection("Name = BAEntities");
  var eComm = eConn.CreateCommand();
  eComm.CommandType = CommandType.StoredProcedure;
  eComm.CommandText = "BAEntities.TripSalesforDateRange";
  eComm.Parameters.AddWithValue("startdate", new DateTime(2006, 1, 1));
  eComm.Parameters.AddWithValue("enddate", new DateTime(2006, 12, 31));
  eConn.Open();
  result = Convert.ToInt32(eComm.ExecuteScalar());
  eConn.Close();
}

A DefiningQuery is comparable to a database view, which is a virtual table. Like a view, a DefiningQuery cannot take parameters or return multiple resultsets, as a stored procedure can.

However, because a DefiningQuery is a virtual table, when you write LINQ or Entity SQL queries against these views, query operators such as WHERE filters will be processed on the server side. This is quite different from working with functions that are embedded in the model. Queries written against functions will have filters performed on the client side.

If you open the BreakAway model in the XML Editor, you will find that you already have two DefiningQuery elements. The EDM Wizard turned all of the database views into DefiningQuery elements in the SSDL.

The first one is for vOfficeAddresses and contains a T-SQL query against the database’s vOfficeAddresses view:

<EntitySet Name="vOfficeAddresses"
 EntityType="BreakAwayModel.Store.vOfficeAddresses" store:Type="Tables"
 store:Schema="dbo" store:Name="vOfficeAddresses">
   <DefiningQuery>
     SELECT
      [vOfficeAddresses].[FirstName] AS [FirstName],
      [vOfficeAddresses].[LastName] AS [LastName],
      [vOfficeAddresses].[addressID] AS [addressID],
      [vOfficeAddresses].[Street1] AS [Street1],
      [vOfficeAddresses].[Street2] AS [Street2],
      [vOfficeAddresses].[City] AS [City],
      [vOfficeAddresses].[StateProvince] AS [StateProvince],
      [vOfficeAddresses].[CountryRegion] AS [CountryRegion],
      [vOfficeAddresses].[PostalCode] AS [PostalCode],
      [vOfficeAddresses].[AddressType] AS [AddressType],
      [vOfficeAddresses].[ContactID] AS [ContactID],
      [vOfficeAddresses].[ModifiedDate] AS [ModifiedDate]
     FROM [dbo].[vOfficeAddresses] AS [vOfficeAddresses]
   </DefiningQuery>
</EntitySet>

The EntitySet attributes are different from standard EntitySet definitions in the store layer. For the sake of comparison, here is the EntitySet for the Payments table:

<EntitySet Name="Payments" EntityType="BreakAwayModel.Store.Payments"
 store:Type="Tables" />

The DefiningQuery in the vOfficeAddresses EntitySet surfaces the results of the vOfficeAddresses view as a SELECT query. Rather than just duplicating the SQL of the existing view, it does a SELECT against the existing view. Since database views are most often read-only, using this SELECT explicitly restricts the Entity Framework from attempting to perform inserts, updates, or deletes against the view.

Because a database view has no primary key, the wizard infers an EntityKey by combining the non-nullable fields of the view. In the SSDL, the wizard also inserts a comment indicating this action:

<!--Errors Found During Generation:
warning 6002: The table/view BreakAway.dbo.vOfficeAddresses'
does not have a primary key defined. The key has been inferred and the
definition was created as a read-only table/view.
-->
<EntityType Name="vOfficeAddresses">
  <Key>
    <PropertyRef Name="FirstName" />
    <PropertyRef Name="LastName" />
    <PropertyRef Name="addressID" />
    <PropertyRef Name="AddressType" />
    <PropertyRef Name="ContactID" />
    <PropertyRef Name="ModifiedDate" />
  </Key>
  <-- Property Elements -->
</EntityType>

The fact that the EntitySet is defined with a DefiningQuery has no other impact on the entity in the CSDL or the mappings. Figures 13-2 and 13-3 show the entity in the model and its mappings back to the entity defined in the SSDL. The only difference from table-based entities is the inability to persist changes to the database from the view-based entities without using stored procedures for updating.

Figure 13-2. An entity that represents a view in the database and is mapped to a DefiningQuery in the SSDL of the model

Figure 13-3. The mappings for the view entity, which are the same as any other entity’s mappings

DefiningQuery really is the ultimate escape hatch. Even with the incredible flexibility that the model’s various mapping capabilities provide, there still may be some things that you just cannot manage to pull off.

A DefiningQuery lets you add queries using the store’s native language—for example, T-SQL or PL/SQL—directly to the store layer of the model. It’s the last step before swallowing your modeling wizardry pride and asking the database administrator to add another view or stored procedure to the database; or in cases where modifying the database is not a possibility.

In addition to creating completely new database views with a DefiningQuery, there are other uses for DefiningQuery. One example is to write a DefiningQuery that returns an entity with properties that don’t exist in the database, such as a calculated property. Although you could achieve the same effect using partial classes, that does not get the property into the model itself, and this could make a big difference if the model is to be shared with other application developers, report builders, or additional EDM consumers in your enterprise.

Also, when you create your own DefiningQuery, if the model does not already have an entity that lines up with its results, you will have to create all of the model elements yourself: the Entity and EntitySet in the CSDL, the Entity and EntitySet in the SSDL, and the mappings. You already did that the easy way by creating a new view in the database. In the next walkthrough, along with creating a DefiningQuery, you will see how to implement these additional necessary elements manually in the model.

A view that would be very useful for BreakAway’s team to have is one that displays reservations that have not been fully paid. Although you can express the query in Entity SQL, as shown in Example 13-7, it is somewhat challenging to construct.

(SELECT r.reservationID,r.Customer.ContactID,r.Trip.TripID,
       SUM(SELECT VALUE p.amount from r.Payments as p) AS PaymentsTotal,
       r.Trip.TripCostUSD AS TripCost
 FROM BAEntities.reservations AS r
 WHERE SUM(SELECT VALUE p.amount FROM r.Payments AS p)
        < r.Trip.TripCostUSD)
UNION
(SELECT r.reservationID,r.Customer.ContactID,r.Trip.TripID,0,
       r.Trip.TripCostUSD
FROM BAEntities.Reservations AS r
WHERE EXISTS(SELECT p from r.Payments AS p)= false)

It would be nice to have this view be part of the model, since it would come in handy in a number of places. You could get at this particular data in other ways, such as by having a PaidinFull Boolean added to the Reservation table in the database and then creating a derived type, but that would result in the need for additional logic to keep that Boolean valid.

Another option might be to create a QueryView. But because it is a complex query, you might want to have more control over the SQL that hits the database. More importantly, you may be unable to work out the Entity SQL even if you are able to express the query in your native query syntax (e.g., T-SQL). A DefiningQuery allows you to do just that: add the native query into your model.

To create the DefiningQuery you’ll need the following elements in your model:

You can create items 2 and 5 in the preceding list using the Designer, whereas you must create the others using the XML Editor.

The first step to implementing the UnpaidReservations DefiningQuery is to rewrite the Entity SQL expression in Example 13-7 as a native database query. For SQL Server, that would look like Example 13-8. This is much simpler than the query that would be constructed from the Entity SQL expression shown earlier.

SELECT r.reservationid, SUM(amount) AS PaymentTotal,
       MIN(events.[TripCostUSD]) AS cost
FROM reservations r
JOIN payments p ON r.[ReservationID]=p.[ReservationID]
JOIN events ON r.[EventID]=events.[EventID]
GROUP BY r.[ReservationID]
HAVING SUM(amount)<min(TripCostUSD)
UNION
SELECT r.reservationid, 0,MIN(events.[TripCostUSD]) AS cost
FROM reservations r
JOIN events ON  r.[EventID]=events.[EventID]
WHERE r.[ReservationID] NOT IN (SELECT reservationID FROM payments)
GROUP BY r.[ReservationID]

This DefiningQuery will be a permanent addition to the BreakAway model, not the test model you used for QueryViews. Be sure to switch back to the BreakAway model when making these changes:

Figure 13-4. Mapping the new entity to the new virtual table

Creating an association from the new entity

Now that you have the entity in the model, you can create an association back to the Reservation entity and tie right into the model and all of the other relationships. This will be a great benefit because you will be able to provide additional details from queries against UnpaidReservation.

Note

Although we know the relationship to be a One to Zero or One relationship, where there may be an UnpaidReservation entity for a particular reservation but never more than one, you should not define the association as a 1:0..1. This “virtual” entity will create a problem when you attempt to delete a reservation entity. The model constraints will expect you to delete an UnpaidReservation as well. With entities that are mapped to database tables, this is not a problem. In this case you can avoid this problem by defining a One to Many relationship between Reservation and UnpaidReservation.

You can do this in a few ways; one is by right-clicking the UnpaidReservation entity, and then choosing Add and then Association from the context menu. Fill out the New Association form as shown in Figure 13-5.

Figure 13-5. Creating a 1:* association between the new entity (UnpaidReservation) and the Reservation entity

The last step is to map the new association. Create the mapping so that it looks like Figure 13-6.

Figure 13-6. The Mapping Details window for the new association

VB
Private Sub DefiningQuery()
  Using context As New BAEntities
    Dim unpaidReservations =
        From ur In context.UnpaidReservations _
                          .Include("Reservation.Customer") _
        Select ur
    For Each unpaid In unpaidReservations
      Dim custOwes = Convert.ToDecimal(unpaid.Cost-unpaid.PaymentTotal)
      Dim custname As String
        With unpaid.Reservation.Customer
          custname = .LastName.Trim & ", " & .FirstName.Trim
        End With
        Console.WriteLine("Cust: {0} owes {1} for the {2} Res", _
                          custname, custOwes,
                          unpaid.Reservation.ReservationDate)
    Next
  End Using
End Sub

C#
private static void DefiningQuery()
{
  using (BAEntities context = new BAEntities())
  {
    var unpaidReservations =
        from ur in context.UnpaidReservations
                          .Include("Reservation.Customer")
        select ur;
    foreach (var unpaid in unpaidReservations)
    {
      var custOwes = Convert.ToDecimal(unpaid.Cost - unpaid.PaymentTotal);
      string custname = null;
      custname = unpaid.Reservation.Customer.LastName.Trim() + ", " +
                 unpaid.Reservation.Customer.FirstName.Trim();
      Console.WriteLine("Cust: {0} owes {1} for the {2} Reservation",
                        custname, custOwes,
                        unpaid.Reservation.ReservationDate);
    }
  }
}

DefiningQuery also allows you to solve more complex problems. Here is another quote from Mike Pizzo, taken from the MSDN forums, describing the ability to create mappings that you cannot create with entities that map directly to tables:

The BreakAway database has a stored procedure called CreateCustomerfromContact. This is an important function for BreakAway’s business model. The company has many contacts who are potential customers, yet they are not customers until they book their first trip. That’s when BreakAway begins to track more details regarding the customer. Sometimes the company needs to create a new Customer that does not already have a Contact in the database, and the inheritance in the model takes care of that.

But if the Contact record already exists and you want to create a new Customer record to tie back to that Contact, inserting a Customer entity won’t work, because that will attempt to insert a new Contact as well.

CreateCustomerfromContact solves this problem, and not only extends the Contact to be a Customer but also passes back the newly created customer so that it can be used immediately. It takes a ContactID as a parameter, inserts a new row into the Customer table using that ContactID, and then returns a complete Customer. The results map directly back to a Customer entity. Here is the T-SQL for the procedure:

INSERT INTO customers (ContactID,customers.[InitialDate])
VALUES (@contactid,GETDATE())

INSERT INTO ContactPersonalInfo (ContactID) VALUES (@contactid)

SELECT Customers.*,
 Contact.FirstName, Contact.LastName, Contact.Title, Contact.AddDate,
 Contact.ModifiedDate, CPI.BirthDate, CPI.HeightInches,
 CPI.WeightPounds, CPI.DietaryRestrictions
FROM Customers INNER JOIN
  Contact ON Customers.ContactID = Contact.ContactID INNER JOIN
  ContactPersonalInfo CPI ON Customers.ContactID = CPI.ContactID
  WHERE customers.contactid

You can create a FunctionImport for this stored procedure and set its return type to Customer. Then you can call the function pretty easily in your code. Example 13-10 shows a method that calls the function and retrieves the newly created customer.

Before it executes the function, it first tests to ensure that the contact already exists in the database and that it is not already a customer.

VB
Public Function ConvertContacttoCustomer(ByVal contactID As Integer)
 As Customer
  Using context As New BAEntities
    Dim contact = context.Contacts _
                         .Where(Function(c) c.ContactID = contactID) _
                         .FirstOrDefault
    If contact IsNot Nothing Then
      If Not TypeOf (contact) Is Customer Then
       'remove contact from the context and then from memory
        context.Detach(contact)
        contact = Nothing
       'call the function which returns a customer
        Return context.CreateCustomerfromContact(contactID).First
      End If
    End If
  End Using
  Return Nothing
End Function

C#
public static BAGA.Customer ConvertContacttoCustomer(int contactID)
{

  using (BAEntities context = new BAEntities())
  {
    var contact = context.Contacts
                         .Where((c) => c.ContactID == contactID)
                         .FirstOrDefault();
    if (contact != null)
    {
      if (!((contact) is Customer))
      {
        //remove contact from the context and then from memory
        context.Detach(contact);
        contact = null;
        //call the function which returns a customer
        return context.CreateCustomerfromContact(contactID).First();
      }
    }
  }
  return null;
}

This is nice for a test module, but what about the real world? This function gives you the ability to turn a contact into a customer, and you have a number of options for leveraging it. You may want it to be an explicit action whereby a user would select a contact and do something in the UI to tell the system to create a customer from the contact. Another option is to have it happen implicitly, whereby if the user attempts to perform a Customer-like action on a non-Customer (e.g., add a reservation), the system could, in the background, go ahead and create the customer identity for that contact if necessary.

What if you have a stored procedure in your database that performs a modification to the database, but doesn’t return any results? Or returns a scalar value as the result? Or returns randomly shaped results? Only functions that return entities will be built into the classes that are generated from the model. Functions that return scalars or nothing won’t be available as methods of the context. Instead, you’ll need to call them from EntityClient.

You can use EntityClient and one of EntityCommand’s Execute methods to call these functions. Like the other ADO.NET Command classes, you can call ExecuteNonQuery, ExecuteScalar, or ExecuteReader from EntityCommand. You already used ExecuteReader to perform queries. Let’s take a look at these commands for calling functions.

<Function Name="CancelTrip" Aggregate="false" BuiltIn="false"
          NiladicFunction="false" IsComposable="false"
          ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">
  <Parameter Name="eventID" Type="int" Mode="In" />
</Function>

VB
Private Sub CancelTrip(ByVal TripID As Integer)
  Dim result As Boolean
  Using eConn = New EntityConnection("Name = BAEntities")
    Dim eComm = eConn.CreateCommand
    With eComm
      .CommandType = CommandType.StoredProcedure
      .CommandText = "BAEntities.CancelTrip"
      .Parameters.AddWithValue("eventid", TripID)
      eConn.Open()
      result = Convert.ToBoolean(.ExecuteScalar())
      eConn.Close()
    End With
  End Using
End Sub

C#
private static void CancelTrip(int TripID)
{
  bool result = false;
  using (var eConn = new EntityConnection("Name = BAEntities"))
  {
    var eComm = eConn.CreateCommand();
    eComm.CommandType = CommandType.StoredProcedure;
    eComm.CommandText = "BAEntities.CancelTrip";
    eComm.Parameters.AddWithValue("eventid", TripID);
    eConn.Open();
    result = Convert.ToBoolean(eComm.ExecuteScalar());
    eConn.Close();
  }
}

You already have worked with functions for database stored procedures in previous chapters. For example, the InsertPayment stored procedure is wrapped by the InsertPayment function in the model.

Imagine that the BreakAway database didn’t already have stored procedures for inserting contacts, or that you wanted to change the definition of the InsertContact procedure that exists in the database. Here’s how you would construct a new function:

<Function Name="InsertContactVirtual" IsComposable="false">
   <CommandText>
     INSERT INTO Contact
      ([FirstName]
     ,[LastName]
     ,[Title]
     ,[AddDate]
     ,[ModifiedDate])
     VALUES
      (@FirstName,@LastName,@Title,GETDATE(),GETDATE())
     SELECT SCOPE_IDENTITY() as ContactID
   </CommandText>
   <Parameter Name="FirstName" Type="nchar" Mode="In"/>
   <Parameter Name="LastName" Type="nchar" Mode="In"/>
   <Parameter Name="Title" Type="nchar" Mode="In"/>
</Function>

You can then map this function to the Contact entity using the Designer just as you would map other functions.

Compare this to the function the wizard created from the existing InsertContact function:

<Function Name="InsertContact" Aggregate="false" BuiltIn="false"
          NiladicFunction="false" IsComposable="false"
          ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">
  <Parameter Name="FirstName" Type="nchar" Mode="In" />
  <Parameter Name="LastName" Type="nchar" Mode="In" />
  <Parameter Name="Title" Type="nchar" Mode="In" />
</Function>

As described in Chapter 6, the two required attributes for a function are Name and IsComposable.

Like the DefiningQuery, the Designer only partially supports custom functions. You need to create them in the XML Editor, but once they are there you can still open the Designer and use it to map the virtual functions to entities. Additionally, because these are not contained in actual database objects, they should remain intact if you use the Update Model Wizard to refresh the database elements in the store layer of your model.

You can take a few approaches when working with stored procedures. The procedures in the BreakAway database take the more standard route, which is to simply perform the tasks at hand. The database contains stored procedures for performing inserts, updates, and deletes on customers as well as contacts. InsertCustomer, UpdateCustomer, and DeleteCustomer interact with the Customer, Contact, and ContactPersonalInfo tables, and InsertContact, UpdateContact, and DeleteContact interact with only the Contact table.

When the Entity Framework uses these stored procedures it will not overlap them. When saving changes to Customers it will call only the Customer entity’s functions. When saving changes to Contacts it will call only the Contact entity’s functions.

You can try to map these functions, or just be prepared for when you are defining your own model with inherited entities and stored procedures.