Using User Defined Functions with Code First Approach

• Download source - 77.9 KB
• Download source + sample - 55.4 KB

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")] // This is optional 
                                        // as it is set as dbo as default if not provided.
        [CodeFunctionAttributes.Name("ufn_MyFunction")] // Name of function in database.
        [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")] // This is optional 
                                               // as it is set as dbo as default if not provided.
        [CodeFunctionAttributes.Name("ufn_MyFunction")] // Name of function in database.
        [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