How to call stored procedure procedure using entity framework core?

Question

I work on asp.net core entity framework I have csharp function done using ado dotnet stored proceure 

i need to convert it using entity framwork core

with another meaning how to call stored proceure using entity framework core 6

and i don't need to use ado dotnet stored procedure 

so How to do it please 

DataTable dtprinters = _IAdcSupportService.GetAvailablePrinters(branchcode);
 if (dtprinters.Rows.Count > 0)
{
}
public interface IAdcSupportService
    {
public DataTable GetAvailablePrinters(string BranchId);
}
public class AdcSupportService:IAdcSupportService
    {
private readonly ADCContext _context;
        public AdcSupportService(ADCContext context)
        {
_context=context;
        }
        public DataTable GetAvailablePrinters(string BranchCode, string DisplayName = null, string PrinterType = null)
        {
            string response = string.Empty;
            SqlCommand cmd = new SqlCommand();
            DataTable dt = new DataTable();
            try
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandText = "ADC_GetAvailablePrinters";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 50000;

                cmd.Parameters.AddWithValue("@BranchCode", BranchCode);
                cmd.Parameters.AddWithValue("@DisplayName", DisplayName);
                cmd.Parameters.AddWithValue("@PrinterType", PrinterType);

                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                sda.Fill(dt);
            }
            catch (Exception ex)
            {
                response = ex.Message;
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
            }
            return dt;
        }
 ALTER PROCEDURE [dbo].[ADC_GetAvailablePrinters]
	(
		@BranchCode VARCHAR(50),
		@DisplayName VARCHAR(100) = NULL,
		@PrinterType VARCHAR(50) = NULL
	)
AS
BEGIN
	IF @PrinterType IS NOT NULL AND @PrinterType = 'SUB'
	BEGIN
		SELECT PrinterName, PrinterIP, Category, IsActive FROM ZebraSubPrinters WITH (NOLOCK) WHERE DisplayName = @DisplayName AND BranchCode = @BranchCode AND IsActive = 1
	END
	ELSE
	BEGIN
		SELECT DisplayName + ',' + PrinterName as PrinterName,UserPC + ',' + ArabicConfig as UserPC FROM ZebraPrinters WITH (NOLOCK) WHERE  BranchCode = @BranchCode AND Status = 'Y'
	END
END

ADCContext represent context of entity framework that collect all models

Answer 1

Hi @Ahmed Abd El Aziz

How to call stored procedure procedure using entity framework core?

From your code and description, it seems that you have already create the stored procedure on the database (follow the database-first approach). If that is the case, you can refer to the following sample and steps:

1.Create a stored procedure sp-GetBookAuthors: the Books and Authors are configured many-to many relationships using EF core:

2.Based on the stored procedure return data to create a view model:

    public class BookAuthorViewModel
    {
        public int BookId { get; set; }
        public string BookName { get; set; }
        public string AuthorName { get; set; }
    }

3.Add the DbSet in the ApplicationDbContext:

    public class ApplicationDbContext : IdentityDbContext
    {
        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
            : base(options)
        {
        }
        public DbSet<Book> Books { get; set; }
        public DbSet<Author> Authors { get; set; }
        public DbSet<BookAuthor> BookAuthors { get; set; }
       
        public DbSet<BookAuthorViewModel> BookAuthorViewModel { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.Entity<BookAuthor>()
                .HasKey(bc => new { bc.BookId, bc.AuthorId });

            modelBuilder.Entity<BookAuthor>()
                .HasOne(bc => bc.Book)
                .WithMany(b => b.BookAuthors)
                .HasForeignKey(bc => bc.BookId);

            modelBuilder.Entity<BookAuthor>()
                .HasOne(bc => bc.Author)
                .WithMany(c => c.BookAuthors)
                .HasForeignKey(bc => bc.AuthorId);
            modelBuilder.Entity<BookAuthorViewModel>().HasNoKey();
        } 
    }

4.Enable migration for the BookAuthorViewModel, it will generate a BookAuthorViewModel in the database

add-migration addbookauthorviewmodel
update-database

5.Then, in the Controller, we can call the stored procedure using the FromSql method:

    public class BookController : Controller
    {
        private readonly ApplicationDbContext _dbContext;

        public BookController(ApplicationDbContext applicationDbContext) {
            _dbContext=applicationDbContext;
        }
        public IActionResult Index()
        {
            var result = _dbContext.BookAuthorViewModel.FromSql($"execute sp_GetBookAuthors @bookname = 'Book A'").ToList();
            return View();
        }
    }

The result as below:

Reference: Working with Stored Procedure in Entity Framework Core and CRUD Operation Using Entity Framework Core And Stored Procedure In .NET Core 6 Web API

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best regards,

Dillion

Answer 2

You execute a stored proc with the FromSql() and use the “exec spname” query syntax. What is not clear is the the entity used must match the sp results.

In you case if you changed the proc to return all the columns from ZebraSubPrinters, you could use that DataSet to execute the query.

if you want to handle a custom column list, then be sure all sp paths return the same columns. Then you define a new DbSet<sptype> in the DbContext and use this entity to make the FromSql() call.

Answer 3

EF Power Tools, a free Visual Studio extension can reverse engineer tables and stored procedures.

Once reversed engineered your stored procedure is available from the DbContext.

See my code sample done in a console project but works with web projects too.

using Experiment1.Data;
using Experiment1.Models;

namespace Experiment1;

internal partial class Program
{
    static async Task Main(string[] args)
    {
        int categoryId = 1;
        int supplierId = 16;
        await using var context = new Context();
        List<uspProductsByCategoryAndSupplierResult> products = 
            await context.GetProcedures()
                .uspProductsByCategoryAndSupplierAsync(categoryId, supplierId);

        AnsiConsole.MarkupLine($"[yellow]Products: category {categoryId} supplier {supplierId}[/]");

        foreach (var product in products)
        {
            Console.WriteLine($"Id {product.ProductID,-3} Name: {product.ProductName}");
        }

        Console.ReadLine();
    }
}