Calling a SQL User-defined function in a LINQ query

Ok, I think I understand the question – the gist of it is you want to be able to call a SQL UDF as part of your Linq to Entities query.

This is if you’re using database or model first:

This article explains how to do it:

To sum it up, you first need to edit your edmx file in an xml editor, in the edmx:StorageModels >> Schema section you need to specify a mapping to your sql udf, eg

<Function Name="SampleFunction" ReturnType="int" Schema="dbo">
    <Parameter Name="Param" Mode="In" Type="int" />

Then you need to create a static function somewhere with the EdmFunction attribute on it, something like this:

public static class ModelDefinedFunctions
    [EdmFunction("TestDBModel.Store", "SampleFunction")]
    public static int SampleFunction(int param)
      throw new NotSupportedException("Direct calls are not supported.");

This method will get mapped to the UDF at query time by entity framework. The first attribute argument is the store namespace – you can find this in your edmx xml file on the Schema element (look for Namespace). The second argument is the name of the udf.

You can then call it something like this:

var result = from s in context.UDFTests
            select new
                TestVal = ModelDefinedFunctions.SampleFunction(22)

Hope this helps.

Leave a Comment