Invoke Stored Procedure from EF

Question

Can anyone explain Executing Stored Procedure from EF also that procedure should accept data in array list

Jignesh Kumar · Answer

Hello Kiran, You can create as below, using System.Data; using Microsoft.Data.SqlClient; var dictionary = new Dictionary { { 1, ("Name1", "Value1") }, { 2, ("Name2", "Value2") }, { 3, ("Name3", "Value3") } }; // Convert dictionary to DataTable var dataTable = new DataTable(); dataTable.Columns.Add("Id", typeof(int)); dataTable.Columns.Add("KeyName", typeof(string)); dataTable.Columns.Add("Value", typeof(string)); foreach (var kvp in dictionary) { dataTable.Rows.Add(kvp.Key, kvp.Value.KeyName, kvp.Value.Value); } Call Store procedure from EF var parameter = new SqlParameter("@KeyValueTable", dataTable) { SqlDbType = SqlDbType.Structured, TypeName = "dbo.KeyValueTableType" }; using (var context = new YourDbContext()) { context.Database.ExecuteSqlRaw("EXEC ProcessDictionary @KeyValueTable", parameter); } Create your store procedure, CREATE TYPE KeyValueTableType AS TABLE ( Id INT, KeyName NVARCHAR(MAX), Value NVARCHAR(MAX) ); CREATE PROCEDURE ProcessDictionary @KeyValueTable KeyValueTableType READONLY AS BEGIN SET NOCOUNT ON; -- You can create your select statement using where clause here SELECT * FROM @KeyValueTable; END;

Kiran Kumar · Answer

We need to configure the parameter data type as we do in SQLCommand , here, will it identify with not mentioning parameter type just with procedure name from ExecuteMyStoredProcedure function Also, my requirement is sending the details in dictionary I edit multiple rows in grid and click apply , it will send to procedure and update the data by Id Few times it may requires multiple data points for that same Id ex :

Rajeesh Menoth · Answer

Hi Kiran, To execute a stored procedure from Entity Framework that accepts a list (array) as a parameter, you can use the following article approach with the help of Table-Valued Parameters (TVP). https://learn.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-ver16 https://www.c-sharpcorner.com/article/user-defined-table-types-and-table-valued-parameters/ Try this steps. First you need create a User-Defined Table Type in your SQL Server database. Create the stored procedure that accepts the TVP. Create a corresponding class for the TVP in your C# project, Class with same property name. EF context class, add your respective TVP related stored procedure and execute. Finally, you can call this below method from your code, passing an array (or list) of integers. var myList = new List { 1, 2, 3, 4, 5 }; var result = dbContext.ExecuteMyStoredProcedure (myList);

Invoke Stored Procedure from EF

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (3)