Closed mstockbridge-degreed closed 2 years ago
Further testing revealed something unexpected; If you define the parameters in precisely one way it works:
var parameters = new { Tags = Parameters.EmptyListOf<Tags>(), ...}
In other words, the type you get from using the Parameters helper is important, and apparently not preserved if you use it as the specified "IEnumerable
Sorry about the delay. now I remember that there cases where an empty list best means null
instead of TVP with no rows. I think it is in the wiki somewhere or a prior issue. I'll try to look it up and get a good answer. it's been a while since we made that decision.
Could this be the information refer to in the wiki?
https://github.com/jonwagner/Insight.Database/wiki/Lists-of-Objects-as-Parameters#empty-lists
It is!
Empty TVP list not being serialized as a TVP list
We're migrating our code to use Insight for calling stored procedures in our database. We have one SP that takes a TVP list of "tags". The list is allowed to be empty. When we do this in the legacy entity framework code we get the following call to the database (unecessary params removed for clarity, and names obfuscated):
declare @p4 dbo.Tags
`
exec sp_executesql N'EXECUTE dbo.MyInputs_Add @Tags, ... N'@Tags [Tags] READONLY, @Tags=@p4, ....`Where the tags are provided as a parameter but contain no values. For the call we're using:
In contrast, Insight sets the value to "default" (similar obfuscation):
exec dbo.MyInputs_Add @Tags=default, ....
We have not been able to find a way to get Insight to serialize the empty TVP list properly.Steps to reproduce
The easiest way I can think of to do this is to create a stored procedure that can take a TVP list, then to pass it an empty list and verify that the list is absent in the call to the database or at the time the stored procedure is called. I'd suggest using this class: https://github.com/jonwagner/Insight.Database/blob/3caebb678db0ac5e02965ba00732b4526dbc9b36/Insight.Tests/ListTests.cs And creating a new test using the test
EnumerablePassedDirectlyToProcWithOneTVPParameterShouldMapListToParameter
but creating the array on line 139 asInsightTestData[] array = new InsightTestData[0];
and passing it in, then verifying that a list does not in fact get sent to the stored procedure.Expected behavior
An empty list of the specified type is sent to the stored procedure. Failing that, that there is a way to indicate to Insight that a value should be sent with a given TVP representation.