.NET - Functies in het Entity Framework 4.0: deel 1

The Entity Framework together with LINQ to Entities are very powerful technologies to access and manipulate data from a database. At first glance everyone is impressed by the conceptual layer which is mapped to tables in a database. When building large scale enterprise applications you will notice that the EF has a few shortcomings. When you need to gain performance or need extra security, stored procedures and user defined functions are often required. In this first article in a series of three I will focus on how to use stored procedures in the Entity Framework 4.0. In the second article I will demonstrate how to map user defined functions to model defined functions and to call them in LINQ to Entities queries. And in the final article SqlClr functions and aggregates will be explored.

• Stored procedures (SP/SPROC)
• Stored procedures examples
  • SalesByCategory
  • UpdateProductPrices
  • UpdateCategory
  • GetCategoryCount
• Executing stored procedures with T-SQL
• Importing stored procedures in the EDM
• Mapping functions to CRUD operations
• Adding Function Imports
• No return value
• Returning a scalar value
• Returning a complex type

Stored procedures (SP/SPROC)

Dynamic SQL or stored procedures, it is an everlasting discussing. The landscape of this debate has changed dramatically because of the powerful LINQ to Entities features. I will not discuss the pros or contras but in my opinion you should be pragmatic. I love LINQ to Entities so all my queries are dynamic SQL's. I also try to avoid stored procedures for simple CRUD operations. Stored procedures (SP/SPROC) are only advisable to boost performance when you have to execute complex data operations. And when security is very important stored procedures are more flexible because in SQL Server you can assign execute permissions to each stored procedure. In this article I will demonstrate several stored procedures and how to use them in the Entity Framework. Decide for yourself which approach is best in your developments.

Stored procedures exampleS

The following stored procedures in the Northwind database have been implemented in T-SQL. I've tried to keep them very simple to demonstrate the different techniques.

SalesByCategory

This SalesByCategory stored procedure (which is included in the default Northwind database) has 2 parameters (CategoryName and OrderYear) and it will return a collection of products of the given category with their TotalPurchase value for the given year.

ALTER PROCEDURE [dbo].[SalesByCategory]
  @CategoryName nvarchar(15), 
  @OrdYear nvarchar(4) = '1998'
AS
IF @OrdYear != '1996' AND @OrdYear != '1997' AND @OrdYear != '1998' 
BEGIN
  SELECT @OrdYear = '1998'
END

SELECT ProductName,
  TotalPurchase=ROUND(SUM(CONVERT(decimal(14,2), OD.Quantity * (1-OD.Discount) * OD.UnitPrice)), 0)
FROM [Order Details] OD, Orders O, Products P, Categories C
WHERE OD.OrderID = O.OrderID 
  AND OD.ProductID = P.ProductID 
  AND P.CategoryID = C.CategoryID
  AND C.CategoryName = @CategoryName
  AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear
GROUP BY ProductName
ORDER BY ProductName

UpdateProductPrices

This stored procedure has one parameter and it will increase or decrease all UnitPrices of all products. It has no return value.

ALTER PROCEDURE [dbo].[UpdateProductPrices]
  @value float
AS
BEGIN
  UPDATE Products
  SET UnitPrice = UnitPrice + @value
END

UpdateCategory

The UpdateCategory stored procedure will use the given parameters to update a record in the Categories table.

ALTER PROCEDURE [dbo].[UpdateCategory]
  @id int,
  @categoryname nvarchar(15),
  @description ntext
AS
BEGIN
  UPDATE Categories
  SET CategoryName = @categoryname,
    Description = @description
  WHERE CategoryID = @id
END

GetCategoryCount

This stored procedure will return the number of categories in the database.

ALTER PROCEDURE [dbo].[GetCategoryCount]
AS
BEGIN
  SELECT COUNT(c.CategoryID) AS Count
  FROM Categories c
END

Executing stored procedures with T-SQL

The ObjectContext class in EF4 introduces some handy new methods like ExecuteStoreQuery. This method allows you to execute a native T-SQL (not E-SQL) command directly against the datasource. It will return an ObjectResult<T> object and it can be used to execute SELECT, INSERT, DELETE, ... statements or stored procedures or UDF's. Following example shows how to execute the GetCategoryCount stored procedure.

var categoryCount2 = context.ExecuteStoreQuery<int>("EXEC dbo.GetCategoryCount");

It is also possible to return complex types. A ComplexType class can be created manually in the Model Browser or it can be created automatically by the Function Import dialog which I will discuss later in this article. This example shows how to execute the SalesByCategory SP which has some parameters and which will return a collection of SalesByCategoryResult objects.

var salesByCategory6 = context.ExecuteStoreQuery<SalesByCategoryResult>(
    "EXEC dbo.SalesByCategory {0}, {1}", "Confections", "1998");

var salesByCategory = context.ExecuteStoreQuery<SalesByCategoryResult>(
    "EXEC dbo.SalesByCategory @CategoryName={0}, @OrdYear={1}", "Confections", "1998");

Another method of the ObjectContext is ExecuteStoreCommand that executes an arbitrary command directly against the data source.

context.ExecuteStoreCommand("EXEC dbo.UpdateProductPrices {0}", 2);

Importing stored procedures in the EDM

A better way of handling stored procedures is importing them in the Entity Data Model. After having created the stored procedures you can add them in the EDM designer by selecting Update Model from Database from the context menu. The wizard shows all the stored procedures and user defined functions in the database. Select them to import them in the Storage Model of the EDM.

Use the Model Browser window to see the imported stored procedures and user defined functions and their parameters.

Mapping functions to CRUD operations

The Entity Framework creates dynamic INSERT, UPDATE and DELETE statements when submitting entity changes to the database. This default behaviour can be overwritten by mapping stored procedures or user defined functions to the CRUD operations of an entity. This is useful when extra database logic should be executed or when you want to set security permissions for each CRUD operation.

Mapping the SP/UDF with an CRUD operation of the entity can be done in the Mapping Details window. Once you have chosen a SP or UDF in the dropdown menu, property names that match parameter names will be mapped automatically. The other property mappings should be set manually. In version 1.0 of the Entity Framework it was required to map the Insert, Update and Delete operation to validate the model. In EF4 it is not required to map all three of the functions. The Result Column Binding can be used to return a value that has been calculated in the stored procedure. e.g. a primary key in the insert operation.

In this example I will map the UpdateCategory stored procedure to the Update operation of the Category entity.

var context = new NorthwindEntities();
var firstCategory = context.Categories.First();
firstCategory.CategoryName = "New category name";
context.SaveChanges();

exec [dbo].[UpdateCategory] @id=1,@categoryname=N'New category name',
    @description=N'Soft drinks, coffees, teas, beers, and ales'

Adding Function Imports

It also possible to map stored procedures to Entity Framework Functions. This feature only supports stored procedures. So user defined functions (UDF's) can be imported in the storage model but they can't be mapped in the conceptual model.

No return value

Choose Add Function Import in the context menu of the stored procedure in the Model Browser. My UpdateProductPrices stored procedure does not return a result.

Executing a function import is very easy because it's just a method in the ObjectContext.

var context = new NorthwindEntities();
context.UpdateProductPrices(1.2); 

If you take a look at the generated code of the ObjectContext then you will see that UpdateProductPrices will execute following code.

public int UpdateProductPrices(Nullable<global::System.Double> value)
{
    ObjectParameter valueParameter;
    if (value.HasValue)
    {
        valueParameter = new ObjectParameter("value", value);
    }
    else
    {
        valueParameter = new ObjectParameter("value", typeof(global::System.Double));
    }
 
    return base.ExecuteFunction("UpdateProductPrices", valueParameter);
}

Returning a scalar value

It is also possible to return a scalar value. Click the Get Column Information button to look up the return value of the stored procedure. The GetCategoryCount stored procedure returns an integer value.

Executing this function import will retun an ObjectResult<int> object which is an enumerable collection. There is just one item so take the Value of the first item in the collection.

var context = new NorthwindEntities();
var categoryCount = context.GetCategoryCount().First().Value; 

exec [dbo].[GetCategoryCount]

These EDM function imports which are mapped to stored procedures cannot be used in LINQ to Entities queries!

var orderDetails =
    from o in context.OrderDetails
    where o.Quantity > context.GetCategoryCount().FirstOrDefault().Value
    select new { o.Product, o.Quantity };

Returning a complex type

It is also possible to return entities or complex types. After resolving the column information a button Create New Complex Type will be enabled. Use this button to create a new complex type that will map the result of the stored procedure.

Use the Model Browser to see all details of this new complex type.

Executing the function import will return a collection of SalesByCategoryResult objects.

var context = new NorthwindEntities();
var salesByCategory = context.SalesByCategory("Confections", "1998");
foreach (var item in salesByCategory)
{
    Console.WriteLine("{0} - {1}", item.ProductName, item.TotalPurchase);
} 

exec [dbo].[SalesByCategory] @CategoryName=N'Beverages',@OrdYear=N'1998'

It is possible to add extra Where conditions or OrderBy expressions but be aware that it is not a LINQ to Entities query! After executing the stored procedure, the data is retrieved by the application and the other expressions will be executed in a LINQ to Objects query. So it works fine but be careful with large amounts of data.

var context = new NorthwindEntities();
var salesByCategory2 = context.SalesByCategory("Confections", "1998")
    .OrderByDescending(s => s.TotalPurchase); 

EF4 introduces some new methods in the ObjectContext. One of them is ExecuteFunction. This method can be used to execute a stored procedure or a UDF that is imported and mapped in the conceptual model. It will return a typed ObjectResult<T>.

var salesByCategory = context.ExecuteFunction<SalesByCategoryResult>("SalesByCategory", 
  new ObjectParameter("CategoryName", "Confections"), new ObjectParameter("OrdYear", "1998"));

This was a short overview on how to use stored procedures in the EF4. I next article I will explore the new and interesting Model Defined Functions. If you have any problems, suggestions or comments be sure to let me know.