Custom SQL and Code First Functions (EF 4.1)

Question

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

If I use the EdmFunction attribute, which 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 a function, the following exception is thrown:

The specified method "..." for type "..." cannot be converted to LINQ to Objects save expression.

Answer 1

If you want to call an SQL function, you must execute your own SQL query. To do this, use context.Database.SqlQuery. Entity structure supports stored procedure mapping, but this function is not supported in the DbContext API (EF 4.1). If you want to call a stored procedure, you must use context.Database.SqlQuery again. Stored procedures can never be used in Linq queries.

EdmFunction is an ObjectContext API function and an Entity constructor. The namespace is set to the namespace defined in the EDMX file. When using the code, you do not have the EDMX file at first, and you cannot determine the mapping of functions.

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

Answer 2

As in EF 6.1, you can now display functions because it is now possible to get EDM from Code First.

Here is an example implementation that allows you to display TVF and stored procedures, but with some limitations. It also uses EF 6 user agreements.

We hope that more complete solutions will be available soon:

https://codefirstfunctions.codeplex.com/

NOTE: you can use git to copy code to your computer and make changes / improvements

Answer 3

I see success in this project https://github.com/divega/UdfCodeFirstSample