How to call stored procedure with table valued parameter from .net core Entity Framework

Question

I have a stored procedure with table valued parameter. I have to call it from Entity framework in .net core.

I couldn't find any API on context object.

I have tried with ADO.net API's and it worked but now I have to call it from EF in .net core. Stored procedure which I have to call returning result which I have to catch.

My sample SP as below

CREATE PROCEDURE [dbo].[GetMyData] 
@MyRequest [dbo].[MyRequestType] Readonly
As 
BEGIN

--Its sample SP thats why returned request table as it is    
select * from @MyRequest


END

MyRequestType is User defined table type

its structure is as below

CREATE TYPE [dbo].[MyRequestType] AS TABLE(
    [Id] [numeric](22, 8) NULL,
    [Col1] [bigint] NULL,
    [Col2] [numeric](22, 8) NULL 
)

I am using Code first approach in EF core

Any help will be appreciated.

if i understand your query i think this post can help you : stackoverflow.com/questions/8157345/… — Sanpas
– Sanpas, Commented Feb 20, 2019 at 12:33
Possible duplicate of Entity Framework Stored Procedure Table Value Parameter — Sanpas
– Sanpas, Commented Feb 20, 2019 at 12:36

Rajiv · Accepted Answer · 2019-02-22 03:45:41Z

11

Finally I could able to call Table values parameter Stored procedure from my .net core service which uses EF core

I have created fake Response Domain Model

   public class MyResponse
    {
        public int Id { get; set; }
        public int Col1{ get; set; }
        public int Col2 { get; set; } 
    }

I called it Fake Domain Model because Domain model is generally table representation in CLR class but I dont have such Table in SQL Server.

Then I have Added property for this under Context class as below

public class MyQueryDbContext : DbContext
{
  public virtual DbSet<MyResponse> Data { get; set; }
    .
    .
    .
}

Then Need to create Data Table for Request parameter list, I have added like below

 DataTable table = new DataTable();
 table.Columns.Add("Id", typeof(int));
 table.Columns.Add("Col1", typeof(int));
 table.Columns.Add("Col2", typeof(int));

Then I have called Stored procedure from .net core API using EF and passed datatable as parameter like below

 var parameter = new SqlParameter("@MyRequest", SqlDbType.Structured);
 parameter.Value = table;
 parameter.TypeName = "[dbo].[MyRequestType]" // My Table valued user defined type
var response=_context.Data
                     .FromSql("EXEC [dbo].[GetMyData] @MyRequest", parameter)
                     .ToList()

You will get response in you response variable.

answered Feb 22, 2019 at 3:45

Rajiv

1,3051 gold badge15 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jerome2606 Over a year ago

I have one question, how do you prevent the migration to try to create the table every time you will modify the structure of the database and call Add-Migration ?

Subjective Reality Over a year ago

Add modelBuilder.Entity<T>().HasNoKey().ToView(null); to OnModelCreating method or [Keyless] decorator to the class, depending on the EF version you are running. learn.microsoft.com/en-us/ef/core/modeling/…

Andrey Borisko · Accepted Answer · 2020-10-12 19:30:27Z

4

Just an update for ef core 3+.

You can use either FromSqlRaw or FromSqlInterpolated.

var query = context.DnoFacts.FromSqlRaw("EXEC [dbo].[GetMyData] {0} ,{1}", otherParam, TableValueParameter) 


var query = context.DnoFacts.FromSqlInterpolated($"EXEC [dbo].[GetMyData] {otherParam}, {TableValueParameter}")

answered Oct 12, 2020 at 19:30

Andrey Borisko

4,6193 gold badges24 silver badges32 bronze badges

1 Comment

Muflix Over a year ago

EF Core 6: FromSqlInterpolated does not have an argument for TableValueParameter.

Collectives™ on Stack Overflow

How to call stored procedure with table valued parameter from .net core Entity Framework

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related