Importing Stored Procedures and Functions

So far we have only worked with the database tables, we have learned how to import database tables into the entity data model, in this article we will learn how to import and use stored procedure and Functions with database-First workflow .

Step 1. Create a database and some table.

Open SSMS and using visual designer create a database, name it as Organization and create two tables called Employee and Department. If you are not a fan of visual designers and prefer SQL scripts to generate Databases and tables, then copy and paste the below code as a new query and run the script.

Create database Organization
   go
   use Organization
   go
  CREATE TABLE [dbo].[Department] (
    [Id]   INT          IDENTITY (1, 1) NOT NULL,
    [Name] VARCHAR (50) NULL,
    CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED ([Id] ASC)
  );

  CREATE TABLE [dbo].[Employee] (
    [Id]           INT          IDENTITY (1, 1) NOT NULL,
    [Name]         VARCHAR (50) NULL,
    [Salary]       INT          NULL,
    [DOB]         DATETIME      NULL,
    [DepartmentId] INT          NULL,
    CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_Employee_Department] FOREIGN KEY ([DepartmentId]) REFERENCES [dbo].[Department] ([Id])
  );

Step 2. Create a Stored Procedure and a Function.

use Organization
   go
  CREATE procedure [dbo].[spGetEmployeeByDepartment]
@DepartmentName varchar(50)
as
begin
select emp.Name, emp.DOB,emp.Salary
from Employee emp
join Department dep
on emp.DepartmentId=dep.Id
where dep.Name=@DepartmentName
end

use Organization
   go



CREATE FUNCTION AGE(@DateOfBirth AS DATETIME)

RETURNS INT

AS

BEGIN

DECLARE @Years AS INT

DECLARE @BirthdayDate AS DATETIME

DECLARE @Age AS INT

--Calculate the difference in years

SET @Years = DATEDIFF(YY,@DateOfBirth,GETDATE())

--Add years to DateOfBirth

SET @BirthdayDate = DATEADD(YY,@Years,@DateOfBirth)

--Subtract a year if the birthday is after today

SET @Age = @Years -

CASE

WHEN @BirthdayDate>GETDATE() THEN 1

ELSE 0

END

--Return the result

RETURN @Age

END
use Organization
   go

create function UDF_GetEmployeesByAge(@EmpAge int)
returns Table
as
return 
select * from Employee emp
where dbo.AGE(emp.DOB)=@EmpAge

Here we have create one stored procedure and two Functions
  1. The Stored Procedure (spGetEmployeeByDepartment) : will return Employees based on their department.
  2. The Scalar Valued Function ( AGE): it returns the Age of an Employee which we will use inside our next Function, EF doesn't support Importing Scalar Valued Function, so we have just created it to use inside our next Table-valued function.
  3. The Table Valued function (UDF_GetEmployeesByAge) : It returns all employee of a specific age.

Now , Let's see how to import the Stored procedure and table valued Function to our conceptual Model.

Step 3.Go to visual studio and create a new project , Name it as DatabaseFirstDemo

The primary focus here is to learn database-first work flow , so for now , I am going to stick to a console app because we don't want to get distracted by the complications of web or desktop app , so to keep things simple and to focus on EF , create a console application.

    • Open visual studio.
    • Go to Files ---->New---->Project
    • Under New Project window select console application.
    • Give the project a name (DatabaseFirstDemo)
    • Click Ok

Step 3.Install Entity Framework.

With the new project the first step is to Install EF. To do that , we use package manager console.

    • Go to Tools.
    • Select Nuget Package Manger and Then go to Package Manager Console.
    • Install Entity Framework using the install-package command.

  Install-Package EntityFramework -Version 6.2.0

Step 4. Add Ado.Net Entity Data Model.

    • Right Click the project.
    • Add ----> Add New Item.
    • Under Add new Item window select Data tab and under that select Ado.Net Entity Data Model and click Add.

This is going to be our conceptual Model that represents the mapping between our database tables and domain classes. I have given it a meaningful name as OrganizationModel.
  • Under the Entity Data Model wizard, select EF Designer From database , cause we already have a database and click next.

Notice :- At this point EF looks at our database and displays the tables, views , stored procedure and Functions , currently we have two tables , one stored procedure and a Function so simply select all these database objects.

Notice we have checked the "import selected Stored Procedure and Functions" checkbox. This checkbox will bring the Stored Procedure and Functions to our Conceptual Model

At this point , you will get a security warning like "Running this template can harm your computer" , don't worry about it and just click next. It gives us the warning because visual studio tries to run a template to generate the code.

Unfolding Edmx

EF generates domain models by running some templates, these templates are present under an EDMX file, the extension of templates is .tt. Let's uncover these templates. As you Notice in the above figure, we have two templates.
    1. OrganizationModel.Context.tt and
    2. OrganizationModel.tt

Here the "tt" stands for template , the first template is responsible for generating DbContext and DBSet while the second template is responsible for generating our domain Entities. This is the reason when you expand the first template that is your context.tt you will find your DbContext class, similarly on expanding Model.tt you will find all your domain Entities.

The EDMX designer that you see is actually a visual representation of XML , Let's check out the real XML which is responsible to generate our Entities in the designer.

Go to solution explorer ,Right click on the Edmx file and open with XML text Editor.

Note : please close the designer before opening the EDMX with XML editor , because you can't open the different representation of same thing in two places at the same time. The XML content is shown in the below figure .

As you can see , it has two main sections
    1. EF Runtime content and
    2. EF designer content

The designer section includes the data about the visual representation of the EDMX , like if we zoom in the diagram or move entities around , anything we do visually in the designer will be represented here.

The runtime section includes everything about our Entity Data Model , so it is where we are interested in, let's expand this and check what does it contain?

On expanding the Runtime section , we find that it has 3 subsections
    1. Storage Models (SSDL)
    2. Conceptual Models (CSDL)
    3. Mappings (C-S)
   

Let's take a deeper look at each of these sections

Under storage model, there is data about our database , tables and columns , In short storage model knows everything about our database. Notice the data type of columns are actual SQL server data Type like varchar , int etc and not C# data type like string or int32. So , storage model is a complete replica of our database.

Under conceptual model , there is everything about our Entities , Like Name of the entity , their properties and type of the properties , notice here the data type of properties is actual C# data type and not SQL Server data type , so , we can say, storage model represents our Entities completely.

Under Mappings section , the relation between storage model and conceptual model is defined , for example the Name column of Employee in storage model maps to the Name property of Employee Entity.

The above three section is the core of any ORM , they define how to map a object oriented Model to a Relational Model.

whatever we see in the EDMX designer is only our Entities. We don't see the conceptual model , storage model or Mappings between the two , they all are hidden , but it is possible to visually analyze them and modify them as per our requirement, if we need.
  • To Edit the Storage Model objects or Conceptual Model objects we use Model Browser. To open the model browser, Right-click on an empty area in the EDMX designer and Select the Model Browser.
    As you can see in below figure the model browser has two section.
      1. OrganizationModel : it displays the conceptual model in a tree structure.
      2. OrganizationModel.store : it displays the storage model in a tree structure.

    Here we can customize them in case if we need any customization , we will discuss when do we need to actually Edit them later in this article.
  • To Edit the mappings we use Table Mapping. To open table mappings , select the Entity for whom you want to see the mappings and select table mappings.

Here you can see the Department's Name column in the database maps to Department's Name property of Department Entity. If we want we can alter this mapping if it make sense.

How to Check Stored procedure and Functions are imported into Conceptual Model or Not?

All our Stored Procedures and Functions are present inside Function Imports under conceptual Model. So, Right click on an Empty area under EDMX designer and select Model Browser , under Model browser Expand the Function Import node and you will find the Stored Procedures and Functions.

Following things here are worth noticing :
    1. The Storage model contains everything that is in our database, so notice it has all the tables, stored procedures,s and all functions including scalar-valued functions.
    2. The Conceptual Model on the other hand contains everything that should be in our domain model, everything that is present here can be used with DbContext as these become the part of our Entity Data Model, that is why the scalar-valued Function is missing here, as EF doesn't support mapping for the scalar-valued function to our EDMX, in other words, a scalar-valued function can't be added as a function import.

Function Imports

When a stored procedure or function is added to a conceptual model, it is called a function import. After adding a function import we can call the corresponding stored procedure from our application code. We know a Stored Procedure can return anything like a single column, all columns of a table or columns from multiple table if the Stored Procedure is based on a Join statement ,accordingly function import can return collections of simple types, EntityTypes, or

ComplexTypes, or no value.

DbContext Class

At this point, if we expand our context class, we see the actual generated code. This is a plain C# code that EF generates for us. This class derives from the DbContext, so we call it our context class. Remember DbContext is an abstraction over the database. It provides a simple API to load the data from the database, save data to the database, and call everything that is on the server which is mapped to the conceptual Model like stored procedure and functions

It has properties of type DbSet. A DbSet represents a Table in our Database. As in our database we had two tables Employee and Department, so EF generated two Dbsets namely DbSet of Employee and DbSet of Department.

Please Notice the Stored Procedure and Functions are now available as simple c# methods. So, it's pretty straight forward to import stored procedure and functions into our conceptual model.

e have two function imports , which could be found under function imports node of our conceptual model. Right click on an Empty area under EDMX designer and go to Model browser , then expand Function imports node, here you will find all your function imports that is your Stored procedure and Functions as shown above, now Let's check their name and return type. Double click on First function import that is our stored procedure , an Edit Function Import window will be opened, here we can Edit its name, Return type etc.

Notice the name of the function import for our stored procedure starts with sp prefix , it is good for a stored procedure perspective but not for a function import , a Function import is simply a c# method of our DbContext class , a Method with a name such as spGetEmployeeByDepartment doesn't sound good , let's change it to something which follows c# method naming convention , so I am giving it a name of GetEmployeeByDepartment.

Also notice it return a complex type. A complex Type is simply a dumped data structure which doesn't support change tracking means if we change anything on these data type changes will not reflect in the database , they are simple data structure for returning data from the database , whenever we import a stored procedure or a function that returns data , EF generates a complex type that maps to the result return from the Stored Procedure or Function. In our case Entity framework has created a complex type with the name spGetEmployeeByDepartment_Result. To get the column information of this complex type click on Get Column Information button. Notice we could map our Stored procedure return type to any of our existing Entity by simply selecting that from the Entity dropdown but none of our Entity contains 3 columns as returned by the stored procedure , if it would have returned the Id column as well then we could have mapped it with our Employee Entity.

Similarly , let's dig into our second Function Import that is for our user defined function.

Notice the name of our Function starts with UDF prefix it is good as long as we are dealing with SQL Server but after importing it into our EDMX as a function import , it is now a normal c# method of our DbContext class , so let's change its name to something that follows the c# method naming convention as GetEmployeeByAge.

Also , Notice the return type is a complex type , but it is actually returning every column of Employee table , so we have no need of a complex type as it could easily be mapped to our Employee Entity. So change the return type from complex to Entity and select Employee from the dropdown.

After editing as soon as we save our EDMX , Entity Framework regenerates everything and all the changes are mapped to conceptual model, so now if we open our DbContext we would find that the name of Function Imports are changed and now they look like pure c# methods.

Domain Classes

On expanding the other template that is model.tt we can see our domain Entities. Each domain Entities has properties based on our table columns.

How to consume SP and Function into our Application

After importing Stored Procedure and Function consuming them into our application is just piece of cake. Now they are simple c# methods of DbContext class, so we can call them anytime with an instance of DbContext as shown in below code.

  class Program
    {
        static void Main(string[] args)
        {
            var db = new OrganizationDbContext();
            var emps = db.spGetEmployeeByDepartment("HR"); // calling Stored procedure
            foreach (var emp in emps)
            {
                Console.WriteLine(emp.Name);
                Console.WriteLine(emp.Salary);
                Console.WriteLine(emp.DOB);         
            }
            var employees = db.GetEmployeesByAge(25); // calling function
            foreach (var emp in employees)
            {
                Console.WriteLine(emp.Name);
                Console.WriteLine(emp.Salary);
            }
            Console.ReadLine();
        }
    }

This was all about how do we import stored procedure and Function, how does EF generate them and how can we Edit their name and Return type as per our requirement.