How to pass table valued parameters to stored procedure in entity framework core

[AVin9]

I asked the same question with issue #7820. Divega responded for that issue and that issue was closed. That's why I'm creating the issue again. Thanks, Divega, for the response. But the response was showing the sample of passing table valued parameter to an inline sql. But I have to pass the table valued parameter to an existing stored procedure in entity framework 7. Is it possible to do that?

[divega]

But I have to pass the table valued parameter to an existing stored procedure in entity framework 7.

@AVin9 I am not really sure I understand what you mean by that. This is why my answer described how to do it with inline SQL:

1. Stored procedures are only supported in raw/inline SQL in Entity Framework Core.
2. Table Valued Parameters are only supported in raw/inline SQL in Entity Framework Core because we just pass any SqlParameter through to the underlying ADO.NET provider.
3. We initially used "Entity Framework 7" as the name for EF Core, but there is no released product called "Entity Framework 7" so I am not 100% which product you are referring to.

I will leave this issue open so that we can look at what to do with it in triage. I am almost sure we have existing issues covering adding support for stored procedure mapping and TVPs.

Also for future reference, you can just re-open a closed issue if you are the original poster.

[AVin9]

Currently we are using the frameworks Dnx451 and EntityFramework.MicrosoftSqlServer 7.0.0-rc1-final. I use the following syntax for querying the database with stored procedure.

Context.Person.FromSql ("exec insert person @name1='{0}'",name);

[AVin9]

Currently the syntax I use for querying the database is the following.

Context.Person.FromSql ("exec GetPerson @name1='{0}'",name);

[divega]

@AVin9 please upgrade to a released version of EF Core, e.g. you can use Microsoft.EntityFrameworkCore.SqlServer version 1.1.1.

The prerelease version you are using does not contain the feature that allows using instances of SqlParameter with FromSql(), which is required to work with TVPs. Issue #3115 represented that feature and was fixed in RC2.

Re the calling syntax in the SQL, I don't remember if {0} works with SqlParameter. The @p syntax I used in the sample at https://gist.github.com/divega/f0f88bf16f35641239cfd9bc534e8d7c does work.

[AVin9]

Currently in my company, we are using DNX 451 and not Core. When I just changed the Microsoft.EntityFrameworkCore.SqlServer version 1.1.1, i'm getting several errors, eg: Services collection in Startup.cs. Where can I get a sample, to see what all changes I have to make in order to upgrade it to Microsoft.EntityFrameworkCore.SqlServer version 1.1.1.

Thank you for your help.

[ajcvickers]

@divega to follow up with upgrade information.

[divega]

@AVin9 there have been several changes since DNX RC1. One of the main breaking changes was the rename of many of our packages, which happened in RC2. Here you will find some guidance on how to upgrade from RC1 to RC2 (most of it should still apply):

https://docs.microsoft.com/en-us/ef/core/miscellaneous/rc1-rc2-upgrade

We have also recently reached RTM for the .NET Core tooling. The main change there is the move from using project.json for the definition of projects to an MSBuild-based csproj files. We encourage everyone doing .NET Core development to migrate to the new tooling and from project.json to csproj files. Assuming you were using Visual Studio 2015 for your DNX development, you should now use Visual Studio 2017 for .NET Core development.

[divega]

By the way, if you find any problems migrating from RC1 to RC2 and the RTM and the final version of the tooling, it might be easier to create a new project using the default templates and the copy the code files.

I am closing this issue based on the belief that I have answered your questions. But feel free to reopen if you still have problems using TVPs after upgrading or creating a new issue if you hit any blocking problems unrelated to TVPs.

[smalik03]

Hello @divega I tried using the TVP example you have given in the gist, but I keep getting the following error-

No mapping to a relational type can be found for the CLR type 'TableValuedParameterBuilder'.