Many databases use stored procedures to perform predefined logic on database tables. Although one of the key features of the Entity Framework is its ability to automatically build native commands based on your LINQ to Entities or Entity SQL queries, as well as the commands for inserting, updating, or deleting data, you may want to override these steps and use your own predefined stored procedures. Although the dynamically built commands are secure, efficient, and generally as good as or better than those you may write yourself, there are many cases where stored procedures already exist and your company practices may restrict direct use of the tables. Alternatively, you may just want to have explicit control over what is executed on the store and prefer to create stored procedures.
The sample database includes four stored procedures that we skipped in our discussion of model creation in Chapter 2. In this chapter, you will update the model, pulling in those four stored procedures, implementing them in the model, and interacting with them in some code.
In this chapter, you will override the Entity Framework’s command
generation feature for a particular entity and direct it to use your
stored procedures instead when SaveChanges
is called. The end of the
chapter will also address the concept of combining entities that map to
database views with stored procedures to provide fully functional entities
to completely avoid direct database access.
This chapter will focus on the stored procedures functionality that the Entity Data Model (EDM) Designer readily supports. In Chapter 13, we will work with stored procedures that are not so easily implemented.
The EDM tools provide a feature called Update Model from Database, which is available from the Designer context menu. You can use it to add previously skipped database objects or those that have been added to the database since the time you originally created the model. Update Model from Database can also recognize new fields added to tables that have already been mapped in the database. You’ll spend a little more time with this tool at the end of the chapter, but for now, let’s dive right into it to bring in those stored procedures that we overlooked in Chapter 2.
To update the model, start by right-clicking anywhere in the Model Browser and selecting Update Model from Database. This will open the Update Model Wizard, which instructs you to Choose Your Database Objects. You can expand only the Stored Procedures node because there are no tables or views in the database that aren’t already in your model. The list of database objects available in this view is not based on which entities you have created, but the fact that those tables and views are represented in the Store Schema Definition Layer (SSDL) portion of the model. Because you did not include the stored procedures when you first built the model, they are not represented in the SSDL, and therefore the Update Model from Database tool sees them as being new.
The Stored Procedures node will list procedures and user-defined functions in the database.
Version 1 of the Entity Framework supports scalar user-defined functions (UDFs), but not table-valued UDFs.
Checking the Stored Procedures checkbox will automatically select
all of the available procedures. You can expand the node to see what’s
there or to individually select the objects you want to use. For this
example, you’ll want all four procedures: CustomersbyState
, DeleteContact
, InsertContact
, and UpdateContact
, as shown in Figure 6-1.
The wizard has two additional tabs: Refresh and Delete. These tabs will display which existing items in the model will be refreshed and, if any of the database items have been deleted, which items will be deleted from the model. These two tabs are read-only.
Click Finish to add the stored procedures to the model. When the update is complete, the model will not look any different when viewed in the Designer. Stored procedures are not automatically added to the conceptual layer of the model. Instead, they have been represented in the SSDL as function elements. It will be your job to define how these functions should be implemented in the model using mapping.
In the model, a Function
represents either a stored procedure or a UDF in the database. Example 6-1 lists the four functions that
were created in the SSDL to represent the four stored procedures in the
sample database.
Example 6-1. Functions created in the SSDL
<Function Name="CustomersbyState" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo"> <Parameter Name="statecode" Type="nchar" Mode="In" /> </Function> <Function Name="DeleteContact" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo"> <Parameter Name="contactid" Type="int" Mode="In" /> </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> <Function Name="UpdateContact" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo"> <Parameter Name="contactid" Type="int" Mode="In" /> <Parameter Name="firstname" Type="nchar" Mode="In" /> <Parameter Name="lastname" Type="nchar" Mode="In" /> <Parameter Name="title" Type="nchar" Mode="In" /> </Function>
Each of these four functions represents a different stored
procedure in the database. The first function, CustomersbyState
, represents the following
T-SQL query:
PROCEDURE [dbo].[CustomersbyState] @statecode nchar(50) AS SELECT DISTINCT contact.* from [Contact],[Address] WHERE [Contact].[ContactID]=[Address].[contactID] AND [Address].[StateProvince]=@statecode
The Insert
, Update
, and Delete
procedures perform the changes you
would expect to the database.
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.
What the heck does niladic mean anyway?
Niladic is a mathematical term meaning that the function takes no
input parameters. SQL Server’s GetDate()
is an example of a niladic
function.
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:
In
In
parameters are read
by the stored procedure.
Out
Out
parameters are
populated by the procedure and returned.
InOut
InOut
parameters are
read by the stored procedure and returned. The procedure may or
may not update this parameter before returning it.
ReturnValue
ReturnValue
parameters
indicate that the stored procedure may create a value and return
it as the last operation in the procedure. The returned value
will be captured by the Entity Framework. For example, after an
Insert
, the stored procedure
might return a newly generated identity value.
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.
Notice that for this query function, the SSDL defines only what is necessary to call the function. There is no indication of returned data. You’ll learn more about how stored procedures are implemented from the SSDL back to the Conceptual Schema Definition Layer (CSDL) later in this chapter.
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.
As you saw in Chapter 5, the default behavior
of the Entity Framework is to construct the necessary Insert
, Update
, and Delete
commands on the fly when you call
SaveChanges
.
You can override this behavior for specific entities by using
functions instead. You can map functions to specific entities. Then,
when SaveChanges
is called, the
Entity Framework will use the stored procedures for updates to entities
that have functions mapped to them. For entities that have no function
mappings, the Entity Framework will perform the default behavior of
generating the commands dynamically.
The CustomersbyState
stored
procedure is for reading data, not updating it. You can link functions
for “read” stored procedures to entities that match what the procedure
returns.
You can use these functions in the EDM in other ways, but the Designer supports only these two scenarios where you can map a function directly to an entity. These are the scenarios we will cover in this chapter. A later chapter will dig into working with stored procedures that are not as simple to implement.
You will find the terms stored procedures and functions used interchangeably throughout the model and the Designer. The model consistently refers to functions, whereas the Designer, in an effort to use familiar terminology, uses stored procedures in a number of places.
There are some strict rules for mapping functions to entities:
You must map either all three functions or none of them. This is a requirement based on how the Entity Framework works with these mappings. Visual Studio will display an error (Error 2025) that reads “The schema validation failed for mapping schema”. The error details explain that the mapping is incomplete and will even tell you which of the three function mappings is missing.
Every input parameter of a function must line up with a property in the entity. You can’t substitute your own data to use as an input parameter. You only can use one of the entity’s properties.
EntityReference
s require input
parameters on all three functionsWe won’t focus on this rule in this chapter. However, if
you were mapping functions to the Address
, you would have to make some
special considerations with respect to the Contact
navigation property. You’ll
see this in action in the next chapter.
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.
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.
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).
One last point regarding the Mapping Details window concerns
the Use Original Value checkbox for the Update
function. As you learned in Chapter 5, an entity will
have an original value and a current value stored in its ObjectStateEntry
. If the entity has been
modified, the current value will be used for the update by default.
Here you have the ability to modify that behavior.
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.
Example 6-2. EntitySetMapping with function mappings added
<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.
Example 6-3. Testing the function mapping
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.
So far, we have dealt with only the Insert
, Update
, and Delete
functions. Another stored procedure
came into the model: the CustomersbyState
procedure, which does a
query of the database and returns a set of rows from the Contact
table.
In our current model, a row from the Contact
table maps back exactly to the
Contact
entity, which means this procedure passes an important rule for
wiring up read procedures in the EDM. The rule is that the results
from the procedure must match an existing entity. Because CustomersbyState
returns what equates to a
Contact
entity, it is easy to map
this function in the Designer.
In Chapter 13, you will learn how to wire up procedures that return results that do not match up to an entity.
You map the CustomersbyState
procedure using the Model
Browser, not the Mapping Details window. The next section discusses
the Model Browser in detail.
To access the Model Browser, you need to right-click in the background of the model in the Designer, and then select Model Browser from its context menu.
In Figure 6-5, a number of the model’s objects have been expanded. This view of the model gives you a great way to see the overall picture of the conceptual layer and the store layer without all of the nitty-gritty XML.
The Model Browser helps you navigate the objects in the conceptual layer (entities, properties, and associations). The lower portion allows you to navigate the items in the SSDL. Notice that in the Model Browser, these are referred to as Tables, Views, and Stored Procedures and not by their SSDL schema names of Entity and Function.
Many of the features of the Designer are available in the context menu of the Model Browser as well, such as validating the model or view mappings, and updating the model from the database.
The Model Browser also provides a means for mapping the functions from the SSDL. Although you can also map some of these from an entity’s Mapping Details window, you can map functions that are for reading data from the store only from the Model Browser.
Right-click the CustomersbyState
stored procedure in the Model
Browser and choose Create Function Import from its context menu. The Add
Function Import dialog box will let you name the function import and map
it to an existing entity or a scalar type (e.g., an integer, string,
etc.); see Figure 6-6.
The new function import will not be displayed in the model in the Designer, but you can see it in the Model Browser if you open the first node and drill first into EntityContainer and then into Function Imports.
In the XML, you will find the following additions to the CSDL
section inside the EntityContainer
element:
<FunctionImport Name="CustomersbyState" EntitySet="Contacts" ReturnType="Collection(ProgrammingEFDB1Model.Contact)"> <Parameter Name="statecode" Mode="In" Type="String" /> </FunctionImport>
Notice that the return type is not a single contact, but a collection of contacts. If only one contact is returned, you will end up with a collection containing a single item.
The mapping information is in a new FunctionImportMapping
element in the MSL’s
EntityContainerMapping
section.
Unlike the Update
, Insert
, and Delete
mappings, this is not included as part
of the contact’s EntitySet
mappings,
but rather stands alone:
<FunctionImportMapping FunctionImportName="CustomersbyState" FunctionName="ProgrammingEFDB1Model.Store.CustomersbyState" />
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()
Not every function mapping will become a method of the
ObjectContext
. Methods that return
scalar values or those that send data to the database will be part
of the model but not be available as a method. These can be called
from Entity Client. You will learn more about these variations in
Chapter 13.
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.
At the beginning of the chapter, you used the Update Model Wizard to pull in the four stored procedures from the database.
Although you used the wizard to add database objects that you skipped over when first creating the model, you can also use the wizard to add objects that were created in the database after you originally built the model.
For example, if a new table has been added to the database, the Update Model Wizard will discover that the table is not already listed in the SSDL of the model and will display it in the Add page of the wizard. If you select this new table, the wizard will add the table to the model and will create a new entity for it. This is the same way that the Entity Data Model Wizard works when you are creating new models.
The Update Model Wizard does not allow you to specify changes to existing objects—for example, tables that were included in the model but have since been modified in the database. The wizard will automatically apply those changes. If you have added new columns to an existing table for which an entity exists in the model, those fields will come into the model and will be added to the entity automatically. Not all changes will affect the conceptual model, however. For example, if you change the spelling of a column name in the database, the wizard will not know to line it up with the existing entity property and instead will create a new property. In this case, you would need to remove the new property and modify the entity mappings so that the existing property points to the correct column.
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.
Many developers and database administrators rely on stored procedures for a variety of reasons, including consistency, security, and reliability. Even though the Entity Framework composes queries and commands automatically, you can override this default behavior by implementing your own stored procedures in the model.
This chapter highlighted functionality that the Designer readily supports: mapping procedures to entities when the procedure’s input parameters and results line up with existing entities and their properties.
Chapter 13 will dig further into these implementations as well as those that are a little trickier to pull off.
3.148.103.210