如何从.net核心实体框架使用表值参数调用存储过程 [英] How to call stored procedure with table valued parameter from .net core Entity Framework
问题描述
我有一个带有表值参数的存储过程。
我必须从.net核心中的实体框架中调用它。
I have a stored procedure with table valued parameter. I have to call it from Entity framework in .net core.
我在上下文对象上找不到任何API。
I couldn't find any API on context object.
我已经尝试过ADO.net API,它可以工作,但是现在我必须从.net核心中的EF调用它。我必须调用的存储过程必须返回返回的结果。
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.
我的样本SP如下
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
是用户定义的表类型
其结构如下
CREATE TYPE [dbo].[MyRequestType] AS TABLE(
[Id] [numeric](22, 8) NULL,
[Col1] [bigint] NULL,
[Col2] [numeric](22, 8) NULL
)
我在EF内核中使用代码优先方法
I am using Code first approach in EF core
任何
推荐答案
最后,我可以从使用EF的.net核心服务中调用表值参数存储过程。核心
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; }
}
我称它为Fake Domain Model,因为Domain模型通常是CLR中的表表示形式类,但我在SQL Server中没有这样的表。
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.
然后我在Context类下为此添加了属性,如下所示:
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));
然后我使用EF从.net核心API调用存储过程,并将数据表作为参数传递,如下所示
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.
这篇关于如何从.net核心实体框架使用表值参数调用存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!