entity framework calling store procedures with UDT

Question

I have a problem when trying to create store procedures and calling it by .NET entity framework.

Firstly, in the SQL, a store procedure is created with inputs

ALTER PROCEDURE [dbo].[AddNotice_OC]
    @OperatorName VARCHAR(32) = NULL,
    @ModuleCode VARCHAR(10) = NULL,
    @NoticeInfo udt_NoticeInfo READONLY,
    @Msg NVARCHAR(MAX),
    @NoticeId_oc BIGINT,
    @ReturnCode INT OUTPUT
AS

note that udt_NoticeInfo is user defined table of SQL

Then, in the .NET entity framework, i have an existing .edmx, i click in the Model Browser and perform Update Model from Database.

The store procedure can be found and added in the .edmx file, however the parameter of the udt_NoticeInfo is missing, the process simply the udt_NoticeInfo.

Thus, I want to know how can I correct the .edmx so that the udt_noticeInfo can be included in the model?

Thanks.

score 1 · Accepted Answer · edited May 23 '17 at 12:25

You cannot pass table valued parameters directly using Entity Framework.

Check this link and this link and this question for how to execute stored procs accepting Table Valued parameter in EF.

It basically involves

creating a DataTable to hold the rows
adding datatable as SqlParameter with DbType as SqlDbType.Structured
executing the stored proc using ObjectContext.ExecuteStoreCommand() by passing the sql command and the parameters

entity framework calling store procedures with UDT

1 Answers1