Comments (23)
I only recently implemented support for stored procedure discovery with a .dacpac, so I am hoping this is just a feature gap. If it is urgent for you I accept sponsorships! 😄
from efcorepowertools.
@ErikEJ Works like a charm 😄
Again, thank you!
from efcorepowertools.
Correct, that is not supported, but can you provide a proper repro, you should see a message like this in the outptu window, not an error like above:
Unable to scaffold FUNCTION 'dbo.FuncWithTvpParameter' as it has TVP parameters.
from efcorepowertools.
Thanks for quick reply!
I'll try to setup a repro soon and link it to you.
from efcorepowertools.
Out of curiosity, will TVP be implemented in the future?
from efcorepowertools.
Out of curiosity, will TVP be implemented in the future?
You can map a tvp returning function today.
But how would you manually code a call using a TVP parameter today (using ADO.NET fo example) ?
from efcorepowertools.
@HAK-Logos Sorry! Actually, the error above is from Functions with tvp parameters - as far as I can see, stored procedure tvp parameters should be supported (as DataTable), but there could be a regression and I am lacking a good sample for my regression test!
from efcorepowertools.
Good, then I'm not going crazy. I was wondering why you were referring to Functions :-)
So it is possible to include TVP in Stored Procedures, but I'm getting the above error.
Is there anything I can do to help you locate the issue?
Just for reference, this is the code where it throws the exception:
https://github.com/ErikEJ/EFCorePowerTools/blob/master/src/Core/RevEng.Core.60/Routines/Extensions/SqlServerSqlTypeExtensions.cs
from efcorepowertools.
I can see that you should NOT be using "legacyResultSetDiscovery" in order for the parameters to be discovered.
For help, please provide a runnable set of SQL scripts that creates the required objects and I can step through the code.
from efcorepowertools.
Here's a script for the user defined type:
CREATE TYPE [dbo].[IntegerList] AS TABLE(
[I] INT NOT NULL,
PRIMARY KEY CLUSTERED ([I] ASC)
);
And here's a sproc:
CREATE PROCEDURE [dbo].[SomeStoredProcedure]
@SomeParameter1 BIGINT,
@SomeParameterList1 [dbo].[IntegerList] READONLY
AS
BEGIN
SET XACT_ABORT ON
SELECT 1
END
Is that enough?
from efcorepowertools.
@HAK-Logos Yes! And does that cause the error you listed above?
from efcorepowertools.
Yes, it should
from efcorepowertools.
@HAK-Logos But it does not! Please provide a repro that actually causes the error you reported above.
public virtual async Task<List<SomeStoredProcedureResult>> SomeStoredProcedureAsync(long? SomeParameter1, DataTable SomeParameterList1, OutputParameter<int> returnValue = null, CancellationToken cancellationToken = default)
{
var parameterreturnValue = new SqlParameter
{
ParameterName = "returnValue",
Direction = System.Data.ParameterDirection.Output,
SqlDbType = System.Data.SqlDbType.Int,
};
var sqlParameters = new []
{
new SqlParameter
{
ParameterName = "SomeParameter1",
Value = SomeParameter1 ?? Convert.DBNull,
SqlDbType = System.Data.SqlDbType.BigInt,
},
new SqlParameter
{
ParameterName = "SomeParameterList1",
Value = SomeParameterList1 ?? Convert.DBNull,
SqlDbType = System.Data.SqlDbType.Structured,
TypeName = "[dbo].[IntegerList]",
},
parameterreturnValue,
};
var _ = await _context.SqlQueryAsync<SomeStoredProcedureResult>("EXEC @returnValue = [dbo].[SomeStoredProcedure] @SomeParameter1 = @SomeParameter1, @SomeParameterList1 = @SomeParameterList1", sqlParameters, cancellationToken);
returnValue?.SetValue(parameterreturnValue.Value);
return _;
}
from efcorepowertools.
Intriguing.. Let me setup a project that replicates the issue and link it to you. Unfortunately I can't link the current project to you. It might take some time, but I will get back to you.
from efcorepowertools.
Ah, I missed this "Is a SQL Server .dacpac used: yes" 😄
from efcorepowertools.
Potentially not supported (yet) with a .dacpac
from efcorepowertools.
Oh fair enough, that's alright 😄
Sorry, had to remove my previous comment. Uploaded the wrong .zip file.
Here's the right one.
from efcorepowertools.
Potentially not supported (yet) with a .dacpac
So only potentially, that's better than "not supported" 😄
I hope it's not that big of a deal to make it work, otherwise I have to put this branch on hold for now.
We are changing some "old" code from passing comma separated strings to SQL, to now use TVP. Converting that string to something edible in SQL is kind of performance heavy, when we pass around 10k values.. So it would be nice to have this working 🙂
from efcorepowertools.
Workaround would be to publish the .dacpac a generate code from the published database
from efcorepowertools.
Workaround would be to publish the .dacpac a generate code from the published database
Hmm.. That is a solution, but I just don't see it being practical in a live environment, fully integrated with CI/CD.
Do you already know, what is required for TVP to be compatible with .dacpac? Or if you're planning to implement it in the near future? 🙂
from efcorepowertools.
I understand 😉 I will get back to you sooner or later!
Anyways, thanks for the help for now 👍
from efcorepowertools.
I implemented a fix for this in the latest daily build, would be grateful if you could try it out.
If you like my free tools, I would be very grateful for a rating or review on Visual Studio Marketplace or even a one-time or monthly sponsorship
from efcorepowertools.
Well that was fast 😄
I appreciate it! And ofc. I'll as a minimum review your tool.
Have a great evening 🙂
from efcorepowertools.
Related Issues (20)
- Table valued parameter (TVP) throwing errors: `System.InvalidOperationException: The parameter for the DbFunction has an invalid type 'DataTable'. Ensure the parameter type can be mapped by the current provider.` HOT 2
- Add support for numeric RowVersions (EF8 only) HOT 4
- Warn on .dacpac reveng if CodeAnalysis is not enabled
- PostgreSQL procedures are not generated in code HOT 19
- ComplexType HOT 1
- Check for undocumented options
- Add modelling support for EF Core 9
- Add rev eng support for EF Core 9 HOT 3
- Reverse Engineer option not displaying HOT 4
- The file efpt.config.json keeps being overwritten. HOT 6
- Possible feature: automatic trimming of varchar fields in the model HOT 4
- NotNull varchar column being mapped to property with default value of Null HOT 3
- Mark file with <auto-generated> message HOT 2
- Microsoft SQL DB TimeOnly fields are recognized as TimeSpan HOT 2
- Add support for complex types HOT 4
- Publish new Dacpac nuget
- Add DAB command file creation option to CLI
- Add DataVerse reveng enhancements to EF Core 8+9 HOT 1
- Workaround for Bug in Default Scaffolding HOT 7
- Add option to run PowerShell script after generation HOT 7
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from efcorepowertools.