Posted on 1/7/2021 1:23:14 PM by Admin

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 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

Create new project
Create a new project

Step 3.Install Entity Framework.

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

Package Manager Console
Package Manager Console

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


  Install-Package EntityFramework -Version 6.2.0

successfully installed EF
EF installed successfully message

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.

Add New Item
Add New Item
Ado.Net Entity Data Model
Ado.Net Entity Data Model

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.

Entity Data Model Wizard
Entity Data Model Wizard

  • In the next window select or specify the connection to your database. 1. Specify Server Name 2. Choose Database. 3. Test the connection and 4. Click Ok.
Entity Data Model Wizard
Entity Data Model Wizard

  • Change the connection name to OrganizationDbContext and click on Next.
Connection Name
Connection Name

  • In the next window select your table, Stored Procedure and Function and give the model namespace a meaningful name as OrganizationModel and click finish.

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.

data objects and settings window1
data objects and settings window

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.

OrganizationModel.edmx diagram
OrganizationModel.edmx diagram

Unfolding Edmx

Unfolding edmx
unfolding edmx

EF generates domain models by running some templates, these templates are present under an EDMX file, the extension of templates are .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.

edmx overview
Edmx overview
context.tt
expand context.tt
edmx overview
expand model.tt

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.

Open with
Open with option
open with xml editor
open with XML 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 .

EDMX as XML
open EDMX as XML

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?

Runtime Section
Runtime section

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

Storage Model
Storage Model

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.

Conceptual Model
Conceptual Model

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.

Mappings
Mapping Section

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.

Model Browser
Model Browser

    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.

table mapping
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.

Function Imports
function Imports

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. Adding a function import allows you to call the corresponding stored procedure from your application code. A 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.

DbContext
DbContext

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.

Edit Function Imports
Function Imports1
function Imports

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.

Edit Function Imports
Edit Function Import for SP

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.

Edit Function Imports2
Edit Function Import for 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.

Edit Function Imports2
After Editing Return type and Name

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.

Edit Function Imports3
DbContext after Editing Function imports

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.

Model.tt
OrganizationModel.tt
Department Class
Department Class generated by EF
Domain Classes
Employee class generated by EF

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.