Custom SQL functions and Code First (EF 4.1)

Question

I'm using Entity Framework 4.1 RC and code first approach. How can I call custom SQL functions?

If I use EdmFunction attribute, what namespace should I specify?

[EdmFunction("Namespace", "GetAge")] 
public static int GetAge(Person p) 
{  
    throw new NotSupportedException(…); 
}

When I try to execute a LINQ query with such function the following exception is thrown:

The specified method '...' on the type '...' cannot be translated into a LINQ to Entities store expression.

Answer 1

If you want to call SQL function you must execute a custom SQL query. To do that use context.Database.SqlQuery. Entity framework supports mapping of stored procedures but this feature is not supported in DbContext API (EF 4.1). If you want to call a stored procedure you must again use context.Database.SqlQuery. Stored procedures can't never be used in Linq queries.

EdmFunction is feature of ObjectContext API and Entity designer. The namespace is set to the namespace defined in the EDMX file. When using code-first you don't have the EDMX file and you can't define function mapping.

Btw. if you follow the code first approach you should not have any stored procedures or SQL functions because you database is defined by you model (code) and generated by entity framework.

Answer 2

As of EF 6.1 it's now possible to map functions, because now it's possiblw to acces the EDM from Code First.

Here is a sample of an implementation that allows to map TVFs and Stored Procedures, but with some limitations. It also uses EF 6 custom conventions.

Hopefully there will be soon more complete solutions:

https://codefirstfunctions.codeplex.com/

NOTE: you can use git to copy the code to your machine and modify/improve it