Menu
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.
842
Entity Framework Core is a modern object-database mapper for . NET. It supports LINQ queries, change tracking, updates, and schema migrations. EF Core works with many databases, including SQL Database (on-premises and Azure), SQLite, MySQL, PostgreSQL, and Azure Cosmos DB.
Step 1: Create an entity class which inherits “DbContext” class. Step 2: The following is the structure of the database with table and stored procedure. Step 3: Create a class to store the returned tabular value. Step 4: Create an object for the entity above and method to call a function.
Step 1 − Select the Console Application from the middle pane and enter TableValuedFunctionDemo in the name field. Step 2 − In Server explorer right-click on your database. Step 3 − Select New Query and enter the following code in T-SQL editor to add a new table in your database.
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.
131
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
38
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
