Calling a SQL User-defined function in a LINQ query

by

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: http://msdn.microsoft.com/en-us/library/dd456847(VS.100).aspx

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" />
</Function>

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.

More Related Contents:

  1. Dynamic where clause (OR) in Linq to Entities
  2. How to create LINQ Expression Tree to select an anonymous type
  3. ‘Contains()’ workaround using Linq to Entities?
  4. “A lambda expression with a statement body cannot be converted to an expression tree”
  5. Bulk-deleting in LINQ to Entities
  6. The type of one of the expressions in the join clause is incorrect in Entity Framework
  7. Entity framework left join
  8. LEFT JOIN in LINQ to entities?
  9. LINQ to SQL multiple tables left outer join
  10. How to compare only date components from DateTime in EF?
  11. Entity Framework. Delete all rows in table
  12. “The LINQ expression node type ‘Invoke’ is not supported in LINQ to Entities” – stumped!
  13. Join and Include in Entity Framework
  14. Linq to Entity Join table with multiple OR conditions
  15. ‘Date’ is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported
  16. Dynamic query with OR conditions in Entity Framework
  17. The LINQ expression node type ‘ArrayIndex’ is not supported in LINQ to Entities
  18. Is there a good LINQ way to do a cartesian product?
  19. LINQ to SQL – Left Outer Join with multiple join conditions
  20. Why is .Contains slow? Most efficient way to get multiple entities by primary key?
  21. Entity Framework/Linq EXpression converting from string to int
  22. Error: “The specified LINQ expression contains references to queries that are associated with different contexts”
  23. LINQ to Entities does not recognize the method ‘Int32 Int32(System.String)’ method, and this method cannot be translated into a store expression
  24. linq to entities doesn’t recognize a method
  25. How to convert an expression tree to a partial SQL query?
  26. Generate EF orderby expression by string [duplicate]
  27. How does LINQ expression syntax work with Include() for eager loading
  28. Only primitive types or enumeration types are supported in this context
  29. How to include() nested child entity in linq
  30. LINQ Query to Convert string to datetime

Leave a Comment