Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
AVin9 opened this issue Mar 10, 2017 · 13 comments

Comments

Projects
None yet
6 participants
@AVin9
Copy link

commented Mar 10, 2017

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

This comment has been minimized.

Copy link
Member

commented Mar 10, 2017

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

This comment has been minimized.

Copy link
Author

commented Mar 10, 2017

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

This comment has been minimized.

Copy link
Author

commented Mar 10, 2017

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

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

@divega

This comment has been minimized.

Copy link
Member

commented Mar 11, 2017

@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

This comment has been minimized.

Copy link
Author

commented Mar 11, 2017

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

This comment has been minimized.

Copy link
Member

commented Mar 13, 2017

@divega to follow up with upgrade information.

@divega

This comment has been minimized.

Copy link
Member

commented Mar 13, 2017

@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

This comment has been minimized.

Copy link
Member

commented Mar 13, 2017

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

This comment has been minimized.

Copy link

commented Mar 30, 2017

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'.

@divega divega modified the milestones: 2.0.0, 2.0.0-preview1 May 8, 2017

@divega

This comment has been minimized.

Copy link
Member

commented May 8, 2017

Reopening so that I can follow up.

@divega divega reopened this May 8, 2017

@divega divega modified the milestones: Backlog, 2.0.0 Jun 13, 2017

@Liero

This comment has been minimized.

Copy link

commented Oct 31, 2017

Any progress on this?

I've tried this with EF-Core 2.0 without success:

SqlParameter parameter = new SqlParameter("@myParameter", SqlDbType.Structured) { Value = DataTable };
dbContext.Database.ExecuteSqlCommandAsync("MyStoredProcedure @myParameter", parameter);

System.InvalidOperationException: Source sequence doesn't contain any elements.

When I use the TableValueParameterBuilder I get:

System.Data.SqlClient.SqlException: Error converting data type nvarchar to numeric.

var parameter = new TableValuedParameterBuilder("dbo.DenialReasonsType", new SqlMetaData("[Denial Reason ID]", SqlDbType.NVarChar, 8))
                    .AddRow("REASON1")
                    .AddRow("REASON2")
                    .CreateParameter("@denialReasons")

[EDIT]: I take it back. My stored procedure was called, but resutled in error not specific to ef-core

@smitpatel smitpatel changed the title How to pass table valued parameters to stored procedure in entity framework 7 How to pass table valued parameters to stored procedure in entity framework core Oct 31, 2017

@tbirgi

This comment has been minimized.

Copy link

commented Feb 12, 2019

guys, Anyone has a working sample?
i am also facing somewhat similar issue. My stored proc is getting executed but this table Value is not getting passed. I am using EF core to execute a stored proc and from logs it seems Data Table is getting passed as empty. here is what i am doing.

`var tbl = new DataTable();
tbl.Columns.Add("id", typeof(string));
foreach (var item in stringList)
{ tbl.Rows.Add(item);
}
SqlParameter Parameter = new SqlParameter();
Parameter.ParameterName = "@UDT";
Parameter.SqlDbType = SqlDbType.Structured;
Parameter.Value = tbl;
Parameter.TypeName = "dbo.StringList"; //This is sqlParameter getting passed below.

_dbContext.Set().FromSql(sql,sqlParameter);``

Here is what i have in EF log.
Executing DbCommand [Parameters=[@udt='' (Nullable = false) (DbType = Object)]

@tbirgi

This comment has been minimized.

Copy link

commented Feb 12, 2019

Looks i figured. It works if we embed @param .
ie sql="EXEC dbo.FindChunks @UDT"

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.