Introduction
A wide range of community now a days are using Entity framework and amongst most of them, code first approach is likely to be known. Using code first approach, the modularity of code is increased and it can be maintained easily. As compared to database first approach, Entity framework till now is not providing a straight way to directly communicate with user defined functions.
Background
While I was working with one of my projects, there comes a requirement in which I have to communicate entity framework directly with User defined function. No doubt, it can be attained in many ways like by using the below code:
Database.SqlQuery<MyModel>("select * from usr_func()");
But we want a more clear and structured code to handle this function. So I decided to write such a library in which I can implement my own custom logic to make this functionality more modular and structural. So, I reviewed different blogs and found Code First Stored Procedures blog written by aureolin. I downloaded the latest version of it and by making significant changes to it, I succeeded.
Using the Code
Here is the simple table valued function created using SQL Server:
CREATE FUNCTION ufn_MyFunction
(
@id int
)
RETURNS TABLE
AS
RETURN
(
SELECT * from tbl_Customer where Id = @id
)
GO
This procedure takes @id
as parameter and will return table records as per id. It's just for an example, the actual implementation can take any type or no parameter and can return query having lots of logic.
Now let us look into the code to map this function using code first approach.
First of all, create a class to pass the parameter to this function. So we create it using the below code:
public class TestFunctionParams
{
[CodeFunctionAttributes.FunctionOrder(1)]
[CodeFunctionAttributes.Name("id")]
[CodeFunctionAttributes.ParameterType(System.Data.SqlDbType.Int)]
public int Id { get; set; }
}
Here, we have mentioned Function Order as it is very much essential to pass function parameter in order. So if we have multiple parameters, then we have to add their order to pass in the function. Also, I have coded many other attributes to handle different operations like the name of parameter and its type.
Now we will initiate our library in datacontext by adding the following code in the constructor of DbContext
class.
public DataContext()
: base("name=SqlConn")
{
this.InitializeTableFunctions();
}
Now, we will add our function as a property in the data context class as below:
[CodeFunctionAttributes.Schema("dbo")]
[CodeFunctionAttributes.Name("ufn_MyFunction")]
[CodeFunctionAttributes.ReturnTypes(typeof(Customer))]
public TableValueFunction<TestFunctionParams> CustomerFunction { get; set; }
Now the whole db context will look like this:
public class DataContext : DbContext
{
public DataContext()
: base("name=SqlConn")
{
System.Data.Entity.Database.SetInitializer<DataContext>(null);
this.InitializeTableFunctions();
}
public DbSet<Customer> Customers { get; set; }
[CodeFunctionAttributes.Schema("dbo")]
[CodeFunctionAttributes.Name("ufn_MyFunction")]
[CodeFunctionAttributes.ReturnTypes(typeof(Customer))]
public TableValueFunction<TestFunctionParams> CustomerFunction { get; set; }
}
So, finally all set at our end. Now we can call this function very easily using the below code:
static void Main(string[] args)
{
using (var db = new DataContext())
{
var funcParams = new TestFunctionParams() { Id = 1 };
var entity = db.CustomerFunction.ExecuteFunction(funcParams).ToList<Customer>();
}
}
The final result of the function will be returned into object and will be converted to list and mapped to Model. In this way, we can add function which can take any number and any type of parameter (we can also use table valued parameter using SqlDbType.Structured
) and map it to our model.
Points of Interest
I think entity framework is an awesome technology and by using it, we can make more user friendly applications which can be easily maintainable. I tried my best to make this library more usable but still if you find that any changes are needed which can make this library more useful, then you are most welcome.
History
- Version 1.0: Initial release