Most of the function attributes align with attributes that are common to database procedures. Because the SSDL is describing the data store, these attributes are applied in the model so that the Entity Framework API will have a thorough description of the procedures.

Aggregate, BuiltIn, and NiladicFunction are attributes that apply to UDFs, not stored procedures. For stored procedures, they will always be false. Because these are optional and false by default, they are not even required here. If you were adding functions manually for stored procedures, you wouldn’t even need to use these, but the wizard inserts them.

IsComposable refers to whether you can use the results of the function in another query. This must always be false for stored procedures.

The ParameterTypeSemantics attribute refers to the input parameter, statecode. The AllowImplicitConversion enum (which is the default) merely means that the data type input can be converted implicitly to a store provider data type if necessary. For example, if an integer is passed into this parameter, the Entity Framework will just go ahead and convert it to a char when creating the command to execute the stored procedure.

The Parameter element describes any input or output parameters. In this case, there is only an input parameter, specified by Mode="In". Additional mode options are InOut, Out, and ReturnValue. All four align with the stored procedures flags to define parameters that are being sent to the procedure. Here is a description of each mode option:

You’ll notice that the parameter in the SSDL is nchar, whereas the parameter in the database’s procedure is more explicit: nchar(50). This won’t pose a problem when data is sent to the stored procedure, thanks to the AllowImplicitConversion enum. The other enumerations are explained in the documentation.

Now let’s take a look at a more complex function, UpdateContact. Here is the actual stored procedure:

PROCEDURE UpdateContact
@contactid INT,
@firstname NCHAR(50),
@lastname NCHAR(50),
@title NCHAR(50)
AS
UPDATE Contact
SET [FirstName]=@firstname,[LastName]=@lastname,[Title]=@title,
[ModifiedDate]=GETDATE()
WHERE [ContactID]=@contactid

The UpdateContact function in the SSDL has the same attributes as the CustomersbyState function, as well as parameter elements to represent the input parameters. You will see later in this chapter how you can use mappings to easily leverage the Update, Insert, and Delete stored procedures when coding against the EDM. You’ll also see how the query stored procedure (CustomersbyState) is handled differently than the Data Manipulation Language (DML) functions.

There are some strict rules for mapping functions to entities:

If you look more closely at the Mapping Details window, you will notice two icons in the upper-left corner. Select the Contact entity in the Designer to display its mappings. The icons will become active. Clicking the top icon causes the Mapping Details window to display the table mappings. The lower icon is for displaying function mappings. You can also display function mappings by right-clicking an entity and choosing Stored Procedure Mappings.

In the Mapping Details window, you will see three placeholders for selecting an Insert function, an Update function, and a Delete function, as shown in Figure 6-2.

Figure 6-2. The function or stored procedures view of the Mapping Details window

Click the first item, Select Insert Function, which will display an arrow to the right that represents a drop-down list. Click the arrow to see your options. The Designer will identify all of the unmapped functions in the store layer and present them in the drop-down list. Select the InsertContact function. The Designer will discover the parameters that are defined in the SSDL and will automatically map them to properties in the Contact entity that have matching names. In this example, everything lines up perfectly, as you can see in Figure 6-3.

Figure 6-3. The InsertContact function mapped to the Contact entity

The InsertContact stored procedure happens to return the new ContactID that was generated when the contact is inserted:

PROCEDURE [dbo].[InsertContact]
@firstname NCHAR(50),
@lastname NCHAR(50),
@title NCHAR(50)
AS

INSERT INTO [Contact]
           ([FirstName]
           ,[LastName]
           ,[Title]
           ,[AddDate]
           ,[ModifiedDate])
     VALUES
           (@firstname,@lastname,@title,GETDATE(),GETDATE())

SELECT SCOPE_IDENTITY() AS NewContactID WHERE @@ROWCOUNT > 0

You may recall from Chapter 5 that when the Entity Framework constructs its own Insert command, it selects the new identity value and automatically pushes it into the entity object that was inserted. You can achieve the same effect by mapping the returned NewContactID value directly to the entity’s ContactID property. That will mean it will not be necessary to requery the database to acquire the ContactID for an inserted contact. To map the returned value, type NewContactID over the text “<Add ResultBinding>”. The ContactID will be automatically chosen as the property to map to because it is the EntityKey for Contact, and therefore is a very good first guess for the Designer to make for you.

Select the DeleteContact and UpdateContact functions to map to the other two functions. There are no other return values, so you will not need to apply a ResultColumnBinding for the update (see Figure 6-4).

Figure 6-4. The function mappings for Contact after you’ve finished mapping the stored procedures functions

Now it’s time to see how these mappings have impacted the Mapping Schema Layer (MSL) section of the model in the raw XML.

A second EntityTypeMapping section has been added within the EntitySetMapping section for the Contacts EntitySet. The first is the one that defines the scalar property mappings for Contact. The new EntityTypeMapping contains an inner element called ModificationFunctionMappings. Within this element the three functions are mapped out, as shown in Example 6-2.

<EntitySetMapping Name="Contacts">

  <!-- PROPERTY MAPPINGS-->
  <EntityTypeMapping TypeName="IsTypeOf(ProgrammingEFDB1Model.Contact)">
    <MappingFragment StoreEntitySet="Contact">
      <ScalarProperty Name="ContactID" ColumnName="ContactID" />
      <ScalarProperty Name="FirstName" ColumnName="FirstName" />
      <ScalarProperty Name="LastName" ColumnName="LastName" />
      <ScalarProperty Name="Title" ColumnName="Title" />
      <ScalarProperty Name="AddDate" ColumnName="AddDate" />
      <ScalarProperty Name="ModifiedDate" ColumnName="ModifiedDate" />
    </MappingFragment>
  </EntityTypeMapping>
 
  <!-- FUNCTION MAPPINGS-->
  <EntityTypeMapping TypeName="ProgrammingEFDB1Model.Contact">
    <ModificationFunctionMapping>

      <InsertFunction
          FunctionName="ProgrammingEFDB1Model.Store.InsertContact">
        <ScalarProperty Name="FirstName" ParameterName="firstname" />
        <ScalarProperty Name="Title" ParameterName="title" />
        <ScalarProperty Name="LastName" ParameterName="lastname" />
      </InsertFunction>

      <UpdateFunction
          FunctionName="ProgrammingEFDB1Model.Store.UpdateContact">
        <ScalarProperty Name="Title" ParameterName="title"
                        Version="Current" />
        <ScalarProperty Name="LastName" ParameterName="lastname"
                        Version="Current" />
        <ScalarProperty Name="FirstName" ParameterName="firstname"
                        Version="Current" />
        <ScalarProperty Name="ContactID" ParameterName="contactid"
                        Version="Current" />
      </UpdateFunction>

      <DeleteFunction
          FunctionName="ProgrammingEFDB1Model.Store.DeleteContact">
        <ScalarProperty Name="ContactID" ParameterName="contactid" />
      </DeleteFunction>

    </ModificationFunctionMapping>
  </EntityTypeMapping>
</EntitySetMapping>

In Example 6-2, you can see that a second EntityTypeMapping element has been added to the Contacts EntitySetMapping. Each function is listed within this new section, and based on everything you have already learned about reading this file, the elements should be familiar and the mappings should be logical. Notice in the UpdateContact that each ScalarProperty has a Version attribute. That is the notation that ties back to the Use Original Version checkboxes, which are unchecked, therefore indicating that the version is Current.

Now that the functions have been mapped to the entities, the Entity Framework will automatically use them to handle any Contact entities that need to be persisted to the database anytime you call SaveChanges. That’s all there is to it. You won’t call these functions directly in your code.

To see this in action, you’ll need to watch the SQL Server Profiler to see when the stored procedures are called, rather than a generated command.

Example 6-3 shows a test that retrieves an address and a contact, and then edits both of them.

VB
Private Sub TestFunctionOverride()
  Using context As New PEF
    Dim contact = context.Contacts.Include("Addresses") _
                  .Where(Function(c) c.Addresses.Any).First
   'make a change to contact
    contact.LastName = contact.LastName.Trim & "-Jones"
   'make a change to the address
    Dim address = contact.Addresses.First
    address.Street2 = "Apartment 42"
    'call SaveChanges
    context.SaveChanges()
  End Using
End Sub

C#
private static void TestFunctionOverride()
{
  using (PEF context = new PEF())
  {
    var contact = context.Contacts.Include("Addresses")
                  .Where(c => c.Addresses.Any()).First();
   //make a change to contact
    contact.LastName = contact.LastName.Trim() + "-Jones";
   //make a change to the address
    var address = contact.Addresses.First();
    address.Street2 = "Apartment 42";
   //call SaveChanges
    context.SaveChanges();
  }
}

When the SaveChanges method is called, the required updates are sent to the database. Because you mapped the functions to the Contact entity, the change to this contact object is manifested in the following command, which executes the UpdateContact stored procedure:

exec [dbo].[UpdateContact]
 @contactid=325,@firstname=N'Virginia',
 @lastname=N'Miller-Jones',@title=N'Ms.

The Address entity has no mapped functions; therefore, Object Services constructed this Update command, which was sent to the database:

exec sp_executesql
N'update [dbo].[Address] set [Street2] = @0 where ([addressID] = @1)',
N'@0 nchar(12),@1 int',
@0=N'Apartment 42',@1=2260

The second line of the command is the Update command. The third line defines the parameters for the command, and the last line passes in the parameter values. 'Apartment 42' is the new value of Street2 and 2260 is the AddressID of the address to update.

You will learn a lot more about how the Entity Framework performs saves and how you can impact them as you read through the book. For now, let’s continue to focus on stored procedures.

After you map the function, a new method is automatically generated for the ProgrammingEFDB1Entities class: CustomersbyState.

You can call the method directly in your code using an instantiated context, as shown in the following code:

VB
Dim results = context.CustomersbyState("WA")

C#
var results= context.CustomersbyState("WA");

This is not the same as creating and executing a query. The function will be executed immediately when the function is called in code. The execution will not be deferred. The return type will be a System.Data.Objects.ObjectResult(Of Contact) (in C#, an ObjectResult<Contact>), which you can enumerate through or bind to data controls.

You could also use one of the LINQ conversion methods to return a more common type of IEnumerable. For example, you could return a list of Contact objects rather than the ObjectResult, using the following code:

VB
context.CustomersbyState("WA").ToList()

C#
context.CustomersbyState("WA").ToList()

Because the function returns an IEnumerable (the ObjectResult), it is possible to use the function in a query, as shown in the following code:

VB
Dim results = From c In context.CustomersbyState("WA")
              Where c.LastName.StartsWith("S")

C#
var results =
    from c in context.CustomersbyState("WA")
    where c.LastName.StartsWith("S")
    select c;

However, this is not a LINQ to Entities query, but a LINQ to Objects query—the query will be performed on the results of the function. That means the function will be executed on the server side and then the results will be processed further in memory.

For example, if there are 800 customers in WA but only three of them have last names that begin with S, all 800 customers will be returned from the database and then LINQ will pull out the three that you were really looking for.

Databases do not support using stored procedures as subqueries, which is why it is not possible to compose a LINQ to Entities query using these functions. Therefore, .NET and the Entity Framework coordinate to break up the query into a function call and a separate LINQ to Objects query.

One scenario in particular has confused many developers who work with the EDM. If you delete an entity from the model and later wish to re-create it, you might expect the Update Model Wizard to re-create the entity for you; but it won’t. The wizard bases its proposed changes by comparing the model’s SSDL to the database schema. When you delete an entity from the conceptual model, the table representation in the SSDL remains intact. The wizard will not detect a change, and therefore will not list that table in the Add window.

You can solve this problem in two ways. The first way is to create the entity from scratch in the Designer, and then, using the Mapping Details window, map it back to the table. The second way is to open the model in the XML view, as you did in Chapter 2, and manually delete the SSDL’s EntityType section for that table, as well as its EntitySet. You can refresh your memory on the SSDL’s XML by reviewing Chapter 2.