Calling User Defined Database Function From LINQ

Entity Framework 4.0 allows you to call a database function both in an esql query and linq to entities. However database function can only return scalar types. Future version would support table valued functions. To use a database function in a query, you have to perform the following steps.

1. Import the function in the store model.

2. To use the function in esql query you must specify the fully qualified namespace of the store model.

3. To make that function available in a linq query, you must define the function with EdmFunction attribute.

To get started we will create a function in the database that returns average product price within that category.

Create a new model and import the products table and the above function. Figure below shows our category entity.

When you import either a database function or a stored procedure, EF defines the defination of the function using Function element. For database functions, IsComposable is set to true and for stored procedure IsComposable is set to false.

Code below shows the function definition.

We can write an esql statement that returns all the products which have a unit price greater then the average price within that category.

To use the function in a linq query, we need to define a static method which basically acts as a stub for to call our above function on the store layer. To map the stub method to our function in the store layer, we will use EdmFunction attribute whose first parameter defines the namespace the function resides in and the second parameter is the name of the function.  Code below shows the stub method

.

Notice the above method throws NotSupportedException because when we call this method in a query, it gets translate to actual call to the store function defined on the SSDL layer. As a result you would never get NotSupportedException thrown at runtime.

Code below shows how to use the above method in a query to get the same result which our esql query gave.