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