Share via

Entity Framework Core 5 Stored Procedures .NET 5 (C#)

  • Article

Introduction

Although EF Core (Entity Framework Core) 5 is commonly used with a DbContext along with one or more models to interact with data there may be times when developers are required to interact with data using stored procedures.

  • Follow through the contents below to learn the very basics for working with stored procedures, in this case the database used is Microsoft SQL-Server. what should not be done is to use stored procedures just to use them as with any direction for coding there are advantages and disadvantages.
  • In most code samples to follow naming of classes are lengthy which self describes the class is for.

Requirements

Definition of Stored Procedures

A stored procedure is a prepared SQL code that you can save, so the code can be reused repeatedly which are stored in a database. Basic usage, common SQL statements with or without parameters while other uses include data-validation or access-control mechanisms. Stored procedures may return result sets (a DataTable for instance), i.e., the results of a SELECT statement. Such result sets can be processed using cursors, by other stored procedures, by associating a result-set locator, or by applications. Stored procedures may also contain declared variables for processing data and cursors that allow it to loop through multiple rows in a table. Stored-procedure flow-control statements typically include IF, WHILE, LOOP, REPEAT, and CASE statements, and more. Stored procedures can receive variables, return results or modify variables and return them, depending on how and where the variable is declared.

Stored procedures have been viewed as the de facto standard for applications to access and manipulate database information using codified methods, or “procedures.” This is largely due to what they offer developers: the opportunity to couple the set-based power of SQL with the iterative and conditional processing control of code development. Instead of writing inline SQL and then attempting to manipulate the data from within the code.

Note that all example which follow are starter code samples, as a stored procedure becomes complex the same basics apply.

Advantages of Using Stored Procedures

The following are not all inclusive reasons in regards to advantages of using Stored Procedures.

These advantages are subjective to developers, database administrators, business and/or security requirements. 

  • Maintainability: Because scripts are in a single location updates and tracking of dependencies based on schema changes become easier.
  • Testing: Can be tested independent of an application.
  • Isolation of Business Rules: Having Stored Procedures in one location means that there’s no confusion of having business rules spread over potentially disparate code files in the application.
  • Speed/Optimization: Stored Procedures are cached on the server. The first execution may take longer while later calls will be shorter. This means the query is optimized along with proper indexing server side.
  • Security: 
    • Limit direct access to tables via defined roles in the database
    • Securing just the data and the code that accesses it is easier than applying that security within the application code itself

Disadvantages of Using Stored Procedures

  • Someone must keep track of changes between database environments, make a test changes in development environment and promote, then test in both test and production environments.
  • Testing: 
    • Stored Procedures tend to be utilized on more than one application. This means there are chances all affected applications do not get tested which may lead to unexpected results.
    • Any data errors in handling Stored Procedures are not generated until runtime
  • Security: When done for the wrong reasons can lead to longer time to work with a specific Stored Procedure where time will vary dependent on the process to gain access to a Stored Procedure followed by time to work on the SQL.
  • Stored procedure code is not as robust as application code, particularly in the area of looping (not to mention that iterative constructs, like cursors, are slow and processor intensive)

Basic example no parameters

A requirement is to select all customers from a customer table joined with a contacts and contacts type table using the following stored procedure.

CREATE PROCEDURE dbo.uspGetCustomers1
AS
  BEGIN
    SET NOCOUNT ON;
    SELECT Cust.CustomerIdentifier, 
        Cust.CompanyName, 
        Cust.ContactId, 
        CT.ContactTitle, 
        C.FirstName, 
        C.LastName, 
        Cust.Street, 
        Cust.City, 
        Cust.Region, 
        Cust.PostalCode, 
        Cust.Phone, 
        Cust.ContactTypeIdentifier, 
        Cust.ModifiedDate
    FROM Customers AS Cust
       INNER JOIN Contacts AS C ON Cust.ContactId = C.ContactId
       INNER JOIN ContactType AS CT ON Cust.ContactTypeIdentifier = CT.ContactTypeIdentifier
                       AND C.ContactTypeIdentifier = CT.ContactTypeIdentifier;
  END;

The first task is to have a class which represents data being returned from the above stored procedures to match columns.

public partial  class uspGetCustomers1Result
{
  public int  CustomerIdentifier { get; set; }
  public string  CompanyName { get; set; }
  public int? ContactId { get; set; }
  public string  ContactTitle { get; set; }
  public string  FirstName { get; set; }
  public string  LastName { get; set; }
  public string  Street { get; set; }
  public string  City { get; set; }
  public string  Region { get; set; }
  public string  PostalCode { get; set; }
  public string  Phone { get; set; }
  public int? ContactTypeIdentifier { get; set; }
  public DateTime? ModifiedDate { get; set; }
}

This is followed by a class to run the stored procedure.

public partial  class StoredProcedures
{
  private readonly  NorthwindContext _context;
 
  public StoredProcedures(NorthwindContext context)
  {
    _context = context;
  }
 
  public async Task<uspGetCustomers1Result[]> UspGetCustomers1()
  {
 
    return await Task.Run(async () =>
    {
      var result = await _context.SqlQuery<uspGetCustomers1Result>("EXEC [dbo].[uspGetCustomers1]");
 
      return result;
    });
 
  }
}
  • Although the method UspGetCustomers1 is asynchronous it's not a requirement, the decision to use asynchronous or not is dependent on responsiveness of the task as it relates to the user interface.
  • SqlQuery extension requires a type which in this case is uspGetCustomers1Result.

Basic example with parameters

This example uses a stored procedures like above while in this case there is a where condition, get customers by country identifier. The likelihood there will more than one record matching the where an array will be returned.

CREATE PROCEDURE  dbo.uspCustomersByCountryIdentifier(@CountryIdentifier INT)
AS
  BEGIN
    SELECT Cust.CustomerIdentifier, 
        Cust.CompanyName, 
        Cust.ContactId, 
        CT.ContactTitle, 
        C.FirstName, 
        C.LastName, 
        Cust.Street, 
        Cust.City, 
        Cust.Region, 
        Cust.PostalCode, 
        Cust.Phone, 
        Cust.ContactTypeIdentifier, 
        Cust.ModifiedDate, 
        Cust.CountryIdentifier, 
        CO.Name
    FROM Customers AS Cust
       INNER JOIN Contacts  AS  C ON  Cust.ContactId = C.ContactId
       INNER JOIN ContactType  AS  CT ON  Cust.ContactTypeIdentifier = CT.ContactTypeIdentifier
                       AND C.ContactTypeIdentifier = CT.ContactTypeIdentifier
       INNER JOIN Countries  AS  CO ON  Cust.CountryIdentifier = CO.CountryIdentifier
    WHERE Cust.CountryIdentifier = @CountryIdentifier;
  END;

This class represents data returned from the stored procedure above.

public partial  class CustomersByCountryIdentifierStoredProcedure
{
  public int  CustomerIdentifier { get; set; }
  public string  CompanyName { get; set; }
  public int? ContactId { get; set; }
  public string  ContactTitle { get; set; }
  public string  FirstName { get; set; }
  public string  LastName { get; set; }
  public string  Street { get; set; }
  public string  City { get; set; }
  public string  Region { get; set; }
  public string  PostalCode { get; set; }
  public string  Phone { get; set; }
  public int? ContactTypeIdentifier { get; set; }
  public DateTime? ModifiedDate { get; set; }
  public int? CountryIdentifier { get; set; }
  public string  Name { get; set; }
}

Next a method is needed to return data which accepts a country identifier and returns an array of CustomersByCountryIdentiferStoredProcedure.

public async Task<CustomersByCountryIdentifierStoredProcedure[]> 
  UspCustomersByCountryIdentifier(int? countryIdentifier)
{
  var parameterCountryIdentifier = new  SqlParameter
  {
    ParameterName = "CountryIdentifier",
    Precision = 10,
    Size = 4,
    SqlDbType = System.Data.SqlDbType.Int,
    Value = countryIdentifier,
  };
 
  CustomersByCountryIdentifierStoredProcedure[] result = await _context
    .SqlQuery<CustomersByCountryIdentifierStoredProcedure>
    ("EXEC [dbo].[uspCustomersByCountryIdentifier] @CountryIdentifier  ",
      parameterCountryIdentifier);
 
  return result;
}
  • EXEC [dbo].[uspCustomersByCountryIdentifier] @CountryIdentifier indicates the stored procedure name followed by the parameter name in the stored procedure. Note the space between the stored procedure name and the parameter. If there are multiple parameters they are to be separated by a comma.
  • parameterCountryIdentifier is the parameter sent to SqlQuery which defines what the stored procedures needs to execute. If not configured properly SQL-Server will thrown a runtime exception.

Repurposing

Suppose a requirement is to get only a single column, contact first name or perhaps two columns, contact first and last name. The same stored procedure may be used by writing a method which calls the stored procedure above.

Here one column is returned, first name of a contact. If two or more columns are needed a select can be performed to a class with properties for contact first and last name.

public async Task<List<string>> CustomersByCountryIdentifier(int? countryIdentifier)
{
  return await Task.Run(async () =>
  {
    var customerResults = await UspCustomersByCountryIdentifier(countryIdentifier);
    return customerResults.Select(cust => cust.FirstName).ToList();
  });
 
}

In both cases the important aspect is to have a class which represents returning data.

Basic insert new record

To insert records the same logic applies as in the last select example with parameters other than to return a new primary key. A basic insert stored procedures to add a record then pass back the new primary key.

CREATE PROCEDURE  [dbo].[uspInsertCategory]  
  @CategoryName NVARCHAR(15), 
  @Description ntext, 
  @Identity INT  OUT
AS
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from 
  -- interfering with SELECT statements. 
  SET NOCOUNT ON; 
INSERT INTO  dbo.Categories (CategoryName, Description) VALUES (@CategoryName, @Description);
SET @Identity = SCOPE_IDENTITY() 
  
END
  • Parameters 1 and two are used to set column values
  • Parameter 3 is used to return the new primary key. Note after the type OUT is used to designate the parameter is for returning a value.
  • SET @Identity = SCOPE_IDENTITY()  retrieves the new primary key on a successful insert.

C# code for performing the insert.

public class Operations
{
  /// <summary>
  /// Insert  new category
  /// </summary>
  /// <param name="categoryName">Category name  required</param>
  /// <param name="description">Category description</param>
  public static  void InsertCategory(string categoryName, string description)
  {
    using var context = new NorthWindContext();
    try
    {
      var parameters = new[] {
        new SqlParameter("@CategoryName", SqlDbType.NVarChar)
        {
          Direction = ParameterDirection.Input, 
          Value = categoryName
        },
        new SqlParameter("@Description", SqlDbType.NText)
        {
          Direction = ParameterDirection.Input, 
          Value = description
        },
        new SqlParameter("@Identity", SqlDbType.Int)
        {
          Direction = ParameterDirection.Output, 
          Value = 0
        }
 
      };
      context.Database.ExecuteSqlRaw(
        "exec uspInsertCategory @CategoryName,@Description,@Identity out", parameters: 
        parameters);
 
      var newPrimaryKey = Convert.ToInt32(parameters[2].Value);
      Debug.WriteLine(newPrimaryKey.ToString());
 
 
    }
    catch (Exception ex)
    {
      Debug.WriteLine(ex.Message);
    }
  }
}
  • parameters array of SqlParamter defines each parameter sent to the stored procedure
  • Note the third parameter Direction is OUT which matches the parameter definition in the stored procedure while the first two direction are set to input.
  • ExecuteSqlRaw is perfect as there is only one value returned.
  • The first parameter sent to ExecuteSqlRaw is the stored procedure name followed by a comma delimited names of parameters expected by the stored procedure.
  • To get the new primary key cast the third paramter to an int.

Deleting records

Deleting records follows the same logic as presented other than there should be assertions performed to see if the record could be removed which may not be possible because of a rule or a rule not set such as cascading to child records which still can pose issues. Note as with the insert this stored procedure has an out parameter which can be handled exactly the same as the insert.

CREATE PROCEDURE  [dbo].[DeleteCustomer] 
  @flag bit  output,-- return 0 for fail,1 for success 
  @Identity int
AS
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from 
  -- interfering with SELECT statements. 
  SET NOCOUNT ON; 
  BEGIN TRANSACTION 
  BEGIN TRY 
    DELETE FROM  Customer WHERE  Identifier = @Identity set @flag=1;  
    IF @@TRANCOUNT > 0 
      BEGIN commit  TRANSACTION; 
    END
  END TRY 
  BEGIN CATCH 
    IF @@TRANCOUNT > 0 
      BEGIN rollback  TRANSACTION;  
    END
    set @flag=0;  
  END CATCH  
 END

Using a tool to assist with writing code

A good deal of the code required is easy enough to write out while a Visual Studio extension, EF Power Tools (free) will do just about all the work for a developer. To get a tasted of EF Power Tools.

Summary

Basics have been presented to get started working with stored procedures using Entity Framework Core 5 which can provide a head start to working into intermediate like shown below or complex stored procedures with business logic and error handling.

CREATE PROCEDURE  [dbo].[CustomerInsertOrUpdate] 
  @Identifier int  = NULL  OUTPUT, 
  @CompanyName nvarchar(255), 
  @ContactName nvarchar(255), 
  @ContactTitle nvarchar(255) 
AS
BEGIN
  SET NOCOUNT ON; 
  IF @Identifier IS  NULL
  BEGIN
    INSERT INTO  Customer  
      (CompanyName,ContactName,ContactTitle) 
    VALUES 
      (@CompanyName, @ContactName, @ContactTitle) 
    SET @Identifier = SCOPE_IDENTITY() 
  END
  ELSE
  BEGIN
    UPDATE Customer 
    SET   CompanyName = @CompanyName, 
      ContactName = @ContactName, 
      ContactTitle = @ContactTitle 
  END  
END

Working with error handling, a little more logic but the basics still apply.

CREATE PROCEDURE  [HumanResources].[uspUpdateEmployeeHireInfo]
  @BusinessEntityID [int], 
  @JobTitle [nvarchar](50), 
  @HireDate [datetime], 
  @RateChangeDate [datetime], 
  @Rate [money], 
  @PayFrequency [tinyint], 
  @CurrentFlag [dbo].[Flag] 
WITH EXECUTE  AS CALLER
AS
BEGIN
  SET NOCOUNT ON;
 
  BEGIN TRY
    BEGIN TRANSACTION;
 
    UPDATE [HumanResources].[Employee] 
    SET [JobTitle] = @JobTitle 
      ,[HireDate] = @HireDate 
      ,[CurrentFlag] = @CurrentFlag 
    WHERE [BusinessEntityID] = @BusinessEntityID;
 
    INSERT INTO  [HumanResources].[EmployeePayHistory] 
      ([BusinessEntityID]
      ,[RateChangeDate]
      ,[Rate]
      ,[PayFrequency]) 
    VALUES (@BusinessEntityID, @RateChangeDate, @Rate, @PayFrequency);
 
    COMMIT TRANSACTION;
  END TRY
  BEGIN CATCH
    -- Rollback any active or uncommittable transactions before
    -- inserting information in the ErrorLog
    IF @@TRANCOUNT > 0
    BEGIN
      ROLLBACK TRANSACTION;
    END
 
    EXECUTE [dbo].[uspLogError];
  END CATCH;
END;

See also

Source code

Resides in the following GitHub repository which has other projects besides what is needed to this article to be cloned. To download only the needed projects, three, two for stored procedures and one for reading appsettings.json to get connection strings do the following.

  • Check that a current version of GIT is installed otherwise the script may fail.
  • Create a temp folder e.g. C:\GitDownloads
  • Create a batch file with the contents below.
  • Run the batch file, wait for the download to complete
  • Copy the projects to a Visual Studio solution and do a NuGet restore packages.
mkdir code
cd code
git init
git remote add -f origin https://github.com/karenpayneoregon/efcore5-getting-started
git sparse-checkout init --cone
git sparse-checkout add ConfigurationHelper
git sparse-checkout add EntityFrameworkCoreStoredProcedures
git sparse-checkout add StoredProcedureInsertNewCategory
git pull origin master
:clean-up
del .gitattributes
del .gitignore
del .yml
del .editorconfig
del *.md
del *.sln