Invoke Stored Procedure from EF

Kiran Kumar

1y956Entity Framework

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

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Kumar1y ago

Accepted 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 Kumar1y ago
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 :
+1
Rajeesh Menoth1y ago
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).
1. https://learn.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-ver16
2. https://www.c-sharpcorner.com/article/user-defined-table-types-and-table-valued-parameters/
Try this steps.
1. First you need create a User-Defined Table Type in your SQL Server database.
2. Create the stored procedure that accepts the TVP.
3. Create a corresponding class for the TVP in your C# project, Class with same property name.
4. EF context class, add your respective TVP related stored procedure and execute.
5. 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);  
```
+1