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.

image

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

image

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.

image

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

image

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

image .

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.

image

Published Thursday, April 8, 2010 12:07 PM by zhirani

Comments

# re: Calling User Defined Database Function From LINQ

Friday, March 2, 2012 11:09 PM by juliedoggy

what is the namespace to be imported for getting the EDMFunctionattribute: my code is not recognising the same.even after having a function in the model store.