.NET - Functions in the Entity Framework 4.0: part 2

In the first article I demonstrated how to use stored procedures in EF4. In this article user defined functions and the new Model Defined Functions will be explored. Based on a series of simple examples I will show how to map UDF's and Entity SQL expressions to Model Defined Functions and how to call them in LINQ to Entities queries.

• Stored procedures (SP/SPROC)
  • Executing stored procedures with T-SQL
  • Importing stored procedures in the EDM
  • Mapping functions to CRUD operations
  • Adding Function Imports
• User Defined Functions (UDF)
  • Executing UDF's with T-SQL
  • Mapping functions to CRUD operations
  • Calling UDF's in Entity SQL statements
• Creating custom functions in the storage model
• Model Defined Functions
  • SqlFunctions
  • Mapping UDF's to Model Defined Functions
  • Mapping Entity SQL expressions to Model Defined Functions
  • EntityFunctions

User Defined Functions (UDF)

In many cases you will be able to accomplish the same task using either a stored procedure or a user defined function. User defined functions cannot execute DML (INSERT, UPDATE, DELETE) statements and they are designed to return a value. One of the big advantages of a UDF’s over SP’s, is the fact that a UDF can be used in SELECT and WHERE statements.

Executing UDF's with T-SQL

The ObjectContext class in EF4 provides new methods like ExecuteStoreQuery that allows you to execute a native T-SQL (not E-SQL) command directly against the datasource. If you create a ComplexType manually with the Model Browser then you can execute a T-SQL SELECT query and store the results in a collection of ProductSold complex type objects.

var soldProducts3 = context.ExecuteStoreQuery<ProductSold>(
   "SELECT p.ProductName, dbo.GetTotalSold(p.ProductID) AS Sold FROM Products AS p");

Mapping functions to CRUD operations

In my previous article I demonstrated how to import stored procedures (SP) and user defined functions (UDF) in the Entity Data Model. Once they are imported in the storage model they can be assigned to the Insert, Update or Delete operation of an entity.

Calling UDF’s in Entity SQL statements

Adding Function Imports for UDF’s in the conceptual model (CSDL) is not supported by the EDM. A workaround is to execute the imported UDF in the storage model (SSDL) by using Entity SQL (eSQL) statements. Make sure the add .Store in the namespace of UDF.

var sql = "SELECT p.ProductName, NorthwindModel.Store.GetTotalSold(p.ProductID) AS Sold FROM NorthwindEntities.Products AS p";
var soldProducts = context.CreateQuery<DbDataRecord>(sql);

This eSQL statement will be translated to following SQL statement:

SELECT 
  [Extent1].[ProductID] AS [ProductID], 
  [Extent1].[ProductName] AS [ProductName], 
  [dbo].[GetTotalSold]([Extent1].[ProductID]) AS [C1]
FROM [dbo].[Products] AS [Extent1]

Creating custom functions in the storage model

There is also a way to implement inline T-SQL commands in custom functions which are defined in the storage model (SSDL). Therefore you have to open the EDMX file as XML. This can be done by right clicking on the EDMX file and choose "Open With". Then select the "Automatic Editor Selector (XML)" option in die dialog window. Now look for the closing tag of the StorageModels. Here you can add Functions which do not refer to a stored procedure or user defined function in the database but which have an inline SQL statement. Use the CommandText and Parameter elements to define the function.

    <Function Name="GetProductsOfCategory" IsComposable="false">
      <CommandText>
        SELECT * FROM Products WHERE CategoryID = @CategoryId
      </CommandText>
      <Parameter Name="CategoryId" Type="int" Mode="In"></Parameter>
    </Function>      
 
  </Schema>
</edmx:StorageModels>

After defining this function in the storage model (SSDL), you can add a new Function Import by opening this dialog.

var products = context.GetProductsOfCategory(1);

exec sp_executesql N'  SELECT * FROM Products WHERE CategoryID = @CategoryId ',N'@CategoryId int',@CategoryId=1

Always keep in mind that the inline CommandText will be executed in the database but extra Where or OrderBy clauses will be executed in a LINQ to Objects query.

var products = context.GetProductsOfCategory(2).OrderByDescending(p => p.ProductName);

exec sp_executesql N'  SELECT * FROM Products WHERE CategoryID = @CategoryId ',N'@CategoryId int',@CategoryId=2

Model Defined Functions

I prefer to use LINQ to Entities queries instead of Entity SQL statements and LINQ to Objects queries. So this brings us by a great new feature, introduced in version 4.0 of the Entity Framework, which are Model Defined Functions.

SqlFunctions

In EF4 there is a new static class named SqlFunctions in assembly System.Data.Entity. It contains a long list of methods like Cos, DateAdd, DateDiff, DatePart, GetDate, Exp, Sign, …  which are mapped to SQL Server functions. These static functions can be called in LINQ to Entities queries.

var soldJanuary =
    from o in context.Orders
    let year = SqlFunctions.DatePart("year", o.OrderDate)
    where SqlFunctions.DatePart("month", o.OrderDate) == 1
    select new { Year = year, TotalSold = o.OrderDetails.Sum(d => d.Quantity) };

This is the final SQL statement that will be executed by SQL Server:

SELECT 
[Project1].[OrderID] AS [OrderID], 
[Project1].[C1] AS [C1], 
(SELECT 
  SUM( CAST( [Extent2].[Quantity] AS int)) AS [A1]
  FROM [dbo].[Order Details] AS [Extent2]
  WHERE [Project1].[OrderID] = [Extent2].[OrderID]) AS [C2]
FROM   (SELECT 
    [Extent1].[OrderID] AS [OrderID], 
    DATEPART(year, [Extent1].[OrderDate]) AS [C1]
    FROM [dbo].[Orders] AS [Extent1]
    WHERE 1 = (DATEPART(month, [Extent1].[OrderDate]))
  ) AS [Project1]

Another interesting function is StringConvert. e.g. The following query will throw an "Unable to cast the type 'System.Nullable1' to type 'System.Object'. LINQ to Entities only supports casting Entity Data Model primitive types." exception because the nullable double can not be casted to a string.

var products =
    from p in context.Products
    select new { p.ProductName, Price = p.UnitPrice + " dollars" };

This problem can be solved easily by calling StringConvert. This function adds spaces so maybe you have to use it in combination with the Trim function.

var products =
    from p in context.Products
    select new 
    { 
        p.ProductName, 
        Price = SqlFunctions.StringConvert(p.UnitPrice).Trim() + " dollars" 
    };

SELECT 
  1 AS [C1], 
  [Extent1].[ProductName] AS [ProductName], 
  LTRIM(RTRIM(STR([Extent1].[UnitPrice]))) + N' dollars' AS [C2]
FROM [dbo].[Products] AS [Extent1]

When you take a look at the source code of these SqlFunctions you will see that the an EdmFunction attribute is used to map them to corresponding T-SQL functions.

[EdmFunction("SqlServer", "DATENAME")]
public static string DateName(string datePartArg, TimeSpan? date);

This technique can also be used to create custom Model Defined Functions.

Mapping UDF’s to Model Defined Functions

One way is to map imported UDF’s in the storage model to Model Defined Functions in conceptual model (CSDL). First let me show two of my UDF’s in the Northwind database which have been implemented in T-SQL.

PriceToEuro

The PriceToEuro UDF has a parameter which represents an amount of money in dollars. The result is the corresponding value in euros.

ALTER FUNCTION [dbo].[PriceToEuro]
(
  @priceInDollar money
)
RETURNS money
AS
BEGIN
  RETURN @priceInDollar * 0.785854617
END

GetTotalSold

This UDF executes a query with the given ProductId and returns the total amount of sold products.

ALTER FUNCTION [dbo].[GetTotalSold]
(
  @productId int
)
RETURNS int
AS
BEGIN
  DECLARE @totalSold int
    
  SET @totalSold = (SELECT SUM(d.Quantity) AS TotalSold
  FROM [Order Details] d
  WHERE d.ProductID = @productId)
    
  RETURN @totalSold
END

Mapping these imported UDF’s to Model Defined Functions can be accomplished by creating a static class with static methods. Add the EdmFunction attribute to the methods to define the mapping. Make sure the namespace is correct and be aware that the namespace ends with .Store. In following examples the PriceToEuro and GetTotalSold functions in my MySqlLinqFunctions class are mapped to the corresponding UDF's.

public static class MySqlLinqFunctions
{
    [EdmFunction("NorthwindModel.Store", "PriceToEuro")]
    public static decimal? PriceToEuro(decimal? priceInDollar)
    {
        throw new NotSupportedException("This function can only be used in a LINQ to Entities query");
    }
 
    [EdmFunction("NorthwindModel.Store", "GetTotalSold")]
    public static int? GetTotalSold(int? productId)
    {
        throw new NotSupportedException("This function can only be used in a LINQ to Entities query");
    }
}

Once this mapping has been set, these functions will work in all LINQ to Entities queries.

var productPrices =
    from p in context.Products
    select new
    {
        ProductName = p.ProductName,
        UnitPriceInDollars = p.UnitPrice,
        UnitPriceInEuros = MySqlLinqFunctions.PriceToEuro(p.UnitPrice)
    };

SELECT 
  1 AS [C1], 
  [Extent1].[ProductName] AS [ProductName], 
  [Extent1].[UnitPrice] AS [UnitPrice], 
  [dbo].[PriceToEuro]([Extent1].[UnitPrice]) AS [C2]
FROM [dbo].[Products] AS [Extent1]

var soldProducts =
    from p in context.Products
    let sold = MySqlLinqFunctions.GetTotalSold(p.ProductID)
    where sold > 1000
    select new
    {
        ProductName = p.ProductName,
        Sold = sold,
    };

SELECT 
  [Extent1].[ProductID] AS [ProductID], 
  [Extent1].[ProductName] AS [ProductName], 
  [dbo].[GetTotalSold]([Extent1].[ProductID]) AS [C1]
FROM [dbo].[Products] AS [Extent1]
WHERE ([dbo].[GetTotalSold]([Extent1].[ProductID])) > 1000

Mapping Entity SQL expressions to Model Defined Functions

It is also possible to declare Model Defined Functions which are only available in the conceptual model (CSDL). These functions have to be implemented in Entity SQL (eSQL). There is no designer for this so you should open the EDMX file as XML. Now you should look for the closing tag of ConceptualModels. Here you can add new conceptual functions with Parameter elements and a DefiningExpression element.

Following function will calculate the total price for the given unit price, quantity and discount. To avoid the “The argument types 'Edm.Decimal' and 'Edm.Double' are incompatible for this operation” exception you should cast the discount.

    <Function Name="CalculatePrice1" ReturnType="Decimal">
      <Parameter Name="UnitPrice" Type="Decimal" />
      <Parameter Name="Quantity" Type="Int32" />
      <Parameter Name="Discount" Type="Double" />
      <DefiningExpression>
        (UnitPrice * Quantity) * CAST(1 - Discount AS DECIMAL)
      </DefiningExpression>
    </Function>
 
  </Schema>
</edmx:ConceptualModels>

You can also rewrite this and pass an OrderDetail entity as a parameter. Both functions will return the same result.

    <Function Name="CalculatePrice1" ReturnType="Decimal">
      <Parameter Name="UnitPrice" Type="Decimal" />
      <Parameter Name="Quantity" Type="Int32" />
      <Parameter Name="Discount" Type="Double" />
      <DefiningExpression>
        (UnitPrice * Quantity) * CAST(1 - Discount AS DECIMAL)
      </DefiningExpression>
    </Function>
 
    <Function Name="CalculatePrice2" ReturnType="Decimal">
      <Parameter Name="OrderDetail" Type="NorthwindModel.OrderDetail" />
      <DefiningExpression>
        (OrderDetail.UnitPrice * OrderDetail.Quantity) * CAST(1 - OrderDetail.Discount AS DECIMAL)
      </DefiningExpression>
    </Function>
 
  </Schema>
</edmx:ConceptualModels>

public static class MySqlLinqFunctions
{
    [EdmFunction("NorthwindModel", "CalculatePrice1")]
    public static decimal CalculatePrice1(decimal unitPrice, int quantity, double discount)
    {
        throw new NotSupportedException("This function can only be used in a LINQ to Entities query");
    }
 
    [EdmFunction("NorthwindModel", "CalculatePrice2")]
    public static decimal CalculatePrice2(OrderDetail orderDetail)
    {
        throw new NotSupportedException("This function can only be used in a LINQ to Entities query");
    }
}

Some LINQ to Entities examples:

var orderDetails1 =
    from o in context.OrderDetails
    select new
    {
        o.OrderID,
        TotalPrice = MySqlLinqFunctions.CalculatePrice1(o.UnitPrice, o.Quantity, o.Discount)
    };

SELECT 
[Extent1].[OrderID] AS [OrderID], 
[Extent1].[UnitPrice] *  CAST( [Extent1].[Quantity] AS int) 
   *  CAST( 1 -  CAST( [Extent1].[Discount] AS float) AS decimal(18,0)) AS [C1]
FROM [dbo].[Order Details] AS [Extent1]

var orderDetails2 =
    from o in context.OrderDetails
    select new
    {
        o.OrderID,
        TotalPrice = MySqlLinqFunctions.CalculatePrice2(o)
    };

SELECT 
[Extent1].[OrderID] AS [OrderID], 
[Extent1].[UnitPrice] * [Extent1].[Quantity] *  CAST( 1 - [Extent1].[Discount] AS decimal(18,0)) AS [C1]
FROM [dbo].[Order Details] AS [Extent1]

When the parameter is an entity type you can also write the function as an extension method. And when you implement the function in C# then you can use it in LINQ to Entities queries and for loaded entities.

[EdmFunction("NorthwindModel", "CalculatePrice2")]
public static decimal CalculatePrice3(this OrderDetail orderDetail)
{
    return (decimal)(decimal.ToDouble(orderDetail.UnitPrice) * decimal.ToDouble(orderDetail.Quantity) 
        * (1 - orderDetail.Discount));
}

var orderDetails6 =
    from o in context.OrderDetails
    select new
    {
        o.OrderID,
        TotalPrice = o.CalculatePrice3()
    };
 
var order = context.OrderDetails.First();
var totalPrice = order.CalculatePrice3();

EntityFunctions

Besides SqlFunctions EF4 also introduced the static EntityFunctions class. This class exposes conceptual model canonical functions which can be used in LINQ to Entities queries. These functions are mapped to the functions in the System.Data.Metadata.Edm namespace and they are only available in the conceptual model.

An example:

var orders3 =
    from o in context.Orders
    select new
    {
        NewDate = EntityFunctions.AddYears(o.OrderDate, 2),
        ShipName = EntityFunctions.Right(o.ShipName, 5)
    };

I have no idea why but not all functions in the System.Data.Metadata.Edm namespace have a corresponding method in the EntityFunctions class. Fortunately it is very easy to map a method to an Edm function, just specify the Edm namespace name. One of the interesting functions is NewGuid().

public static class MySqlLinqFunctions
{
    [EdmFunction("Edm", "NewGuid")]
    public static Guid NewGuid()
    {
        throw new NotSupportedException("This function can only be used in a LINQ to Entities query");
    }
}

Another example is the Year function. It will be mapped to the Edm.Year function and when executing a LINQ to Entities query this will be translated to the T-SQL DATEPART function.

public static class MySqlLinqFunctions
{
    [EdmFunction("Edm", "Year")]
    public static int Year(DateTime? dateTime)
    {
        throw new NotSupportedException("This function can only be used in a LINQ to Entities query");
    }
}

Edm functions can also be used in the Entity SQL expression of the DefiningExpression element. Some examples:

    <Function Name="Year2" ReturnType="Int32">
      <Parameter Name="DateTime" Type="DateTime" />
      <DefiningExpression>
         Edm.Year(DateTime)
      </DefiningExpression>
    </Function>
 
    <Function Name="ThisCentury" ReturnType="Boolean">
      <Parameter Name="DateTime" Type="DateTime" />
      <DefiningExpression>
        CASE WHEN Edm.Year(DateTime) > 1999 THEN True ELSE False END
      </DefiningExpression>
    </Function>
 
      </Schema>
</edmx:ConceptualModels>

var orders =
    from o in context.Orders
    select new
    {
        Year = MySqlLinqFunctions.Year2(o.OrderDate),
        ThisCentury = MySqlLinqFunctions.ThisCentury(o.OrderDate),
        OrderDate = o.OrderDate
    };

SELECT 
  1 AS [C1], 
  DATEPART (year, [Extent1].[OrderDate]) AS [C2], 
  CASE WHEN ((DATEPART (year, [Extent1].[OrderDate])) > 1999) THEN 
      CAST(1 as bit) ELSE CAST(0 as bit) END AS [C3], 
  [Extent1].[OrderDate] AS [OrderDate]
FROM [dbo].[Orders] AS [Extent1]

As you see Model Defined Functions offer a lot of flexibility and it makes LINQ to Entities much more powerful. In the past it was often needed to create LINQ to Entities queries and add extra conversions and calculations in a second LINQ to Objects query. Now all queries can be implemented in LINQ to Entities and so they will be executed in the database which reduces overhead and which will make them faster. I hope you liked this article. In the final article of this series I will explore the SqlClr functions and aggregates and demonstrate the powerful combination with Model Defined Functions. If you have any suggestions or comments be sure to let me know.