Tip 30 – How to use a custom database function

Article
08/06/2009

Imagine you have a database function like the DistanceBetween function in Nerd Dinner:

CREATE FUNCTION [dbo].[DistanceBetween](
@Lat1 as real,
@Long1 as real,
@Lat2 as real,
@Long2 as real)
RETURNS real
AS
BEGIN
…
END

And you want to use it with the Entity Framework.

Declaring the Function

The first step is to open your EDMX file in the XML editor and add a <Function> inside the <Schema> inside the <edmx:StorageModels> element.

When you are done it should look like this:

</Function>

Using the Function in eSQL

Now this function can be called in eSQL like this:

SELECT VALUE(D) FROM MyModel.Dinners AS D
WHERE StorageNamespace.DistanceBetween(
D.Latitude,D.Longitude,-34,174) < 50

MyModel is simply the name of your EntityContainer (generally the same as your ObjectContext) and StorageNamespace is your storage model schema namespace.

Using the Function in LINQ

Most of you don’t use eSQL so you are probably wondering can I do this with LINQ?

In 3.5 SP1 this is how:

var nearbyDinners =
from d in ctx.Dinners.Where(
“StorageNamespace.DistanceBetween(it.Latitude, it.Longitude, –34, 174) < 50”
) select d;

Here we are mixing LINQ with eSQL by using a query builder method, which takes an eSQL fragment, in which we call our database function. Notice how this snippet refers to the current item using the ‘it’ keyword. You can even refer to parameters if necessary.

This is great.

But it would be much better without any strings.

Improvements in EF 4.0

Well in EF 4.0 you can write something like this instead:

var nearbyDinners =
from d in ctx.Dinners
where DistanceBetween(d.Latitude, d.Longitude, –34,174) < 50
select d;

Which looks a lot better. No magic strings *and* compile time checking.

To make this work you need a method like this:

[EdmFunction("StorageNamespace", "DistanceBetween")]

public double DistanceBetween(
double lat1,
double long1,
double lat2,
double long2)

{

throw new NotImplementedException("You can only call this method as part of a LINQ expression");

}

You may be wondering why the method throws an exception?

Well we never actually need to execute this method directly. We just use it to write LINQ queries, which are in translated into SQL without ever really calling the method.

The EF uses the EdmFunction attribute to know which database function needs to be called instead.

Cool huh.

Enjoy.

Tip 30 – How to use a custom database function

Declaring the Function

Using the Function in eSQL

Using the Function in LINQ

Improvements in EF 4.0

Additional resources