Using Teradata Stored Procedures to Insert, Update and Delete in Entity Framework

Blog
The best minds from Teradata, our partners, and customers blog about whatever takes their fancy.
Teradata Employee

A common question about the Entity Framework(EF) is how to use stored procedures within the framework for insert, update and delete operations. The answer is that leveraging stored procedures for insert, update and delete is actually a built-in capability of EF.

To use stored procedures for a particular entity, EF requires that all three insert, update and delete stored procedures must be provided; if one is missing, the model will not validate. In addition, stored procedure parameters must match up exactly with the properties of the entity; the big caveat here is that no additional parameters that do no match up to the property in the entity may exist.

To demonstrate let’s start with a very simple EF model with a single entity which represents a Category:

Use the following stored procedures for the Category entity:

Insert

CREATE PROCEDURE CategoryInsert (IN c02 VARCHAR)

BEGIN

INSERT INTO Category(CategoryName) VALUES (c02);

END;

Update

CREATE PROCEDURE CategoryUpdate (IN c01 INTEGER, IN c02 VARCHAR)

BEGIN

UPDATE Category SET CategoryName = c02 WHERE CategoryID = c01;

END;

Delete

CREATE PROCEDURE CategoryDelete (IN c01 INTEGER)

BEGIN

DELETE FROM Category WHERE CategoryID = c01;

END;
  1. To add the stored procedures to the EF model, right click on the entity,

    then select Update Model from Database... as shown below:

 

The Choose Your Database Objects dialog will be displayed; select the stored procedures to add to the EF model as shown below:

 

   2.  Now map the stored procedures to the Insert, Update and Delete functions for the entity.

        Right click on the entity, then select Stored Procedure Mapping as shown below:

 

The Mapping Details dialog will be displayed, to allow mapping for the specific stored procedures as shown below:

Click on the Select [Operation] Function and fill in the Mapping Details as shown below:

 

     3.   There is one last step and that is manually modify the SSDL because the MaxLength facet is not written out to the SSDL for a Function parameter. To guarantee correct parameter size with the Entity Provider for Teradata, this information must be added. Otherwise, the Entity Provider will assume the value in the DefaultValue attribute from the manifest.

Here is an example of the generated and modified Function fragment in the SSDL:

Generated

<Function Name="InsertCategory" Aggregate="false" BuiltIn="false"
NiladicFunction="false" IsComposable="false"
ParameterTypeSemantics="AllowImplicitConversion" Schema="ef_blog">

<Parameter Name="c02" Type="varchar" Mode="In"/>

</Function>

Modified

<Function Name="InsertCategory" Aggregate="false" BuiltIn="false"
NiladicFunction="false" IsComposable="false"
ParameterTypeSemantics="AllowImplicitConversion" Schema="ef_blog">

<Parameter Name="c02" Type="varchar"Mode="In" MaxLength="15"/>

</Function>

See Retrieving Entity Types With a Stored Procedure for more information on why this step is needed.

Finally here is some sample code to insert and update a category. Note that it is not necessary to call the stored procedures directly in code. Any time SaveChanges is called, EF will use the mapped stored procedures for any required inserts, updates and deletes.

using (ExampleEntities context = new ExampleEntities())
{

// add a new category
CategorynewCategory = Category.CreateCategory(0, "Test Category");
context.AddToCategory(newCategory);
context.SaveChanges();

// correct the category name
newCategory.CategoryName = "Updated Category";
context.SaveChanges();

// query the categories
var result = from c in context.Category
selectc;

foreach(Category c in result)
{
Console.WriteLine("{0} {1}", c.CategoryID, c.CategoryName);
}

}