如何在实体框架中将过程与用户定义的表类型一起使用 [英] How to use the Procedure with User Defined Table Type in Entity Framework

问题描述

如何在Entity Framework中使用具有用户定义表类型的过程?

How to use a procedure with a user-defined table type in Entity Framework?

我拥有使用数据库优先方法的EF，当我添加带有用户定义表类型列的过程时，它不会反映在EF中，将更新模型.

I have the EF with database-first approach, when I add a procedure with a user-defined table type columns it will not reflected in the EF will update the model.

又如何通过过程在EF中传递用户定义的表参数?

And how can I pass the user-defined table parameter in EF with procedure?

我的程序:

Sample_Proce_Sp ( @TableTest @UserDefinedTable只读 ) 作为 开始 从* @ TableTest中选择* 结束

Sample_Proce_Sp ( @TableTest @UserDefinedTable Readonly ) AS BEgin Select * from @TableTest END

在EF中，我更新了模型，将添加显示错误的存储过程，如

In EF I have updated the model, will adding the stored procedure it shows the error like

The model was generated with warnings or errors.
Please see the Error List for more details. These issues must be fixed before running your application.
Loading metadata from the database took 00:00:03.1330217.
Generating the model took 00:00:01.9251464.
Successfully registered the assembly 'System.Data.Entity, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' in the Web.Config file. 

推荐答案

Github上的EntityFrameworkExtras 看起来很有希望.

从Git中心站点:

DbContext context = new DbContext("ConnectionString");

var proc = new AddMemberStoredWithAddressesProcedure()
    {
        FirstName = "Michael",
        LastName = "Bovis",
        Age = 26,
        Addresses = new List<Address>()
        {
            new Address() {Line1 = "16", Line2 = "The Lane", Postcode = "MA24WE"}
        }
    };

context.Database.ExecuteStoredProcedure(proc);

其中"AddMemberStoredWithAddressesProcedure"和"Address"都是用特殊属性定义的.

Where "AddMemberStoredWithAddressesProcedure" and "Address" are both defined with special attributes.

我有机会尝试一下.

    CREATE TYPE [dbo].[UdtGuidList] AS TABLE(
        [Guid] [uniqueidentifier] NULL
    )
GO

CREATE PROCEDURE [dbo].[MyUdtSproc] 
    -- Add the parameters for the stored procedure here
    (@GuidList  UdtGuidList READONLY)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT *
    from Blog b
    INNER JOIN @GuidList gl ON gl.Guid = b.BlogGuid
    --where b.BlogGuid in (SELECT gl.Guid from @GuidList gl)

END

它使用5行自定义类型创建了此动态SQL.

It created this dynamic SQL with 5 rows of the custom type..

declare @p3 dbo.UdtGuidList
insert into @p3 values(N'333f3916-c823-e311-84f2-0022198ef787')
insert into @p3 values(N'33327a17-c34e-e211-9a8c-0022198ef787')
insert into @p3 values(N'333ebc24-c44e-e211-9a8c-0022198ef787')
insert into @p3 values(N'3338d557-c44e-e211-9a8c-0022198ef787')
insert into @p3 values(N'333d7f92-c44e-e211-9a8c-0022198ef787')

exec sp_executesql N'EXEC [dbo].[MyUdtSproc] @GuidList = @GuidList ',N'@GuidList [UdtGuidList] READONLY',@GuidList=@p3

这可能会完成工作，但是sql可能会随着用户定义的表中的许多行而变大.我将把它与旧的ADO进行比较.

This might get the work done, but the sql could grow large with many rows in the user defined table. I'm going to compare this to old ADO.

我尝试了旧的DataTable ADO(如下)，并跟踪了SQL以查看它产生了什么.完全一样！

I tried the old DataTable ADO (below) and traced SQL to see what it produced. It was EXACTLY the same!

   var dt = new DataTable();

    dt.Columns.Add("Guid");

    foreach (var r in list)
    {
        var row = dt.NewRow();
        row["Guid"] = r.Guid;
        dt.Rows.Add(row);
    }

    using (var conn = new SqlConnection(@"Server=AComputer\DEVSQL;Database=Booyaa;Trusted_Connection=True"))
    {
        using (var sproc = new SqlCommand("[dbo].[MyUdtSproc]", conn))
        {
            var param = new SqlParameter("@GuidList", SqlDbType.Structured);
            param.TypeName = "[dbo].[UdtGuidList]";
            param.SqlValue = dt;
            sproc.Parameters.Add(param);

            if (conn.State != ConnectionState.Open) conn.Open();
            var reader = sproc.ExecuteReader();
        }
    }

酷！我说这个库是一个有效的选择.

Cool! I say this library is a valid option.

更新:

我博客中的更多信息

示例Visual Studio项目

这篇关于如何在实体框架中将过程与用户定义的表类型一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！