Tip 30 – How to use a custom database function

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)


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 Name=DistanceBetween
   <Parameter Name=Lat1 Type=float Mode=In/>
   <Parameter Name=Long1 Type=float Mode=In/>
   <Parameter Name=Lat2 Type=float Mode=In/>
   <Parameter Name=Long2 Type=float Mode=In/>

Using the Function in eSQL

Now this function can be called in eSQL like this:

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 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.
Comments (11)

  1. Dave says:

    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!

  2. Alex D James says:


    Glad you like it. 🙂

    Thanks for the positive comment, it really is appreciated.

  3. 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

  4. u2462 says:

    can you tell me

    EdmFunction must be in ssdl or csdl

  5. Alex D James says:

    EdmFunction is in the SSDL

  6. u2462 says:

    can tell me,EdmFunction In SSDL not Support


    [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");


  7. Alex D James says:

    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");



  8. Jiri Cincura says:

    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

  9. Fred says:

    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?

  10. Alex D James says:


    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?