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.

Comments

Anonymous
August 06, 2009
Fantastic blog entry.Take a common problem, show how to implement it in EF1, then show how muche easier it is in EF4. Love it.More of the same please!
Anonymous
August 07, 2009
@Dave,Glad you like it. :)Thanks for the positive comment, it really is appreciated.
Anonymous
August 07, 2009
Yes very cool, you just pointed me to the trick in EF v1 to use LINQ to call store function.And of course what the much better support in EF4 is away better.Cool tip
Anonymous
February 24, 2010
can you tell meEdmFunction must be in ssdl or csdl
Anonymous
February 25, 2010
EdmFunction is in the SSDL
Anonymous
February 25, 2010
can tell me,EdmFunction In SSDL not Support<EdmFunction.......[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");}
Anonymous
February 25, 2010
The [EdmFunction] attribute is C# code and lives on a function stub defined anywhere in your code. Generally it should be static though. i.e.public static class MyCustomFunctions{[EdmFunction("StorageNamespace", "DistanceBetween")]public static 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");}}
Anonymous
March 03, 2010
And you can define this LINQ import function also as extension method to provide more streamline syntax and the translation will still work. http://blog.cincura.net/id/230897
Anonymous
March 04, 2010
Yeah nice Jiri
Anonymous
March 31, 2010
When I use a function with objectContext.Entities.Where( t=> udf(para1, para2) == 1), here the Entities is not ObjectQuery, but a ObjectSet<T>, the first time I call the method, it runs correctly, if I reuse the objectContext,and run it again but with different para1, para2, then the previous value still cached, there is not way, and the result is same as previous one, which is wrong. The sql profiler shows that both query hit the database, but the t-sql is the same. Am I missing something?
Anonymous
March 31, 2010
Fred,That sounds like a bug... I've forward this on to some people on the EF team to investigate. It would help if you have a repro you can share with me or the team?Alex

Partager via