System.ArgumentNullException: 'Metadata for field 'xxxx' of record '2' did not match the original record's metadata

[LimeyChris]

Describe the bug

Attempting to call a stored procedure, passing a user defined table type that contains a SqlGeography as an input parameter causes an ArgumentNullException if the if the input parameter contains more than 1 record.

If you are seeing an exception, include the full exceptions details (message and stack trace).

Exception message: System.ArgumentNullException: Metadata for field 'geom' of record '2' did not match the original record's metadata.
Stack trace:
>	[Exception] Microsoft.Data.SqlClient.dll!Microsoft.Data.SqlClient.Server.ValueUtilsSmi.SetIEnumerableOfSqlDataRecord_Unchecked(Microsoft.Data.SqlClient.Server.SmiEventSink_Default sink, Microsoft.Data.SqlClient.Server.SmiTypedGetterSetter setters, int ordinal, Microsoft.Data.SqlClient.Server.SmiMetaData metaData, System.Collections.Generic.IEnumerable<Microsoft.Data.SqlClient.Server.SqlDataRecord> value, Microsoft.Data.SqlClient.ParameterPeekAheadValue peekAhead) Line 3823	C#
 	[Exception] Microsoft.Data.SqlClient.dll!Microsoft.Data.SqlClient.Server.ValueUtilsSmi.SetCompatibleValueV200(Microsoft.Data.SqlClient.Server.SmiEventSink_Default sink, Microsoft.Data.SqlClient.Server.SmiTypedGetterSetter setters, int ordinal, Microsoft.Data.SqlClient.Server.SmiMetaData metaData, object value, Microsoft.Data.SqlClient.Server.ExtendedClrTypeCode typeCode, int offset, Microsoft.Data.SqlClient.ParameterPeekAheadValue peekAhead) Line 1732	C#
 	[Exception] Microsoft.Data.SqlClient.dll!Microsoft.Data.SqlClient.TdsParser.WriteSmiParameter(Microsoft.Data.SqlClient.SqlParameter param, int paramIndex, bool sendDefault, Microsoft.Data.SqlClient.TdsParserStateObject stateObj, bool isAnonymous, bool advancedTraceIsOn) Line 10044	C#
 	[Exception] Microsoft.Data.SqlClient.dll!Microsoft.Data.SqlClient.TdsParser.TdsExecuteRPC(Microsoft.Data.SqlClient.SqlCommand cmd, System.Collections.Generic.IList<Microsoft.Data.SqlClient._SqlRPC> rpcArray, int timeout, bool inSchema, Microsoft.Data.Sql.SqlNotificationRequest notificationRequest, Microsoft.Data.SqlClient.TdsParserStateObject stateObj, bool isCommandProc, bool sync, System.Threading.Tasks.TaskCompletionSource<object> completion, int startRpc, int startParam) Line 9271	C#
 	[Exception] Microsoft.Data.SqlClient.dll!Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(System.Data.CommandBehavior cmdBehavior, Microsoft.Data.SqlClient.RunBehavior runBehavior, bool returnStream, bool isAsync, int timeout, out System.Threading.Tasks.Task task, bool asyncWrite, bool inRetry, Microsoft.Data.SqlClient.SqlDataReader ds, bool describeParameterEncryptionRequest) Line 5147	C#
 	[Exception] Microsoft.Data.SqlClient.dll!Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(System.Data.CommandBehavior cmdBehavior, Microsoft.Data.SqlClient.RunBehavior runBehavior, bool returnStream, System.Threading.Tasks.TaskCompletionSource<object> completion, int timeout, out System.Threading.Tasks.Task task, out bool usedCache, bool asyncWrite, bool inRetry, string method) Line 4846	C#
 	[Exception] Microsoft.Data.SqlClient.dll!Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(System.Data.CommandBehavior cmdBehavior, Microsoft.Data.SqlClient.RunBehavior runBehavior, bool returnStream, string method) Line 4727	C#
 	[Exception] Microsoft.Data.SqlClient.dll!Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(System.Data.CommandBehavior behavior) Line 2091	C#
 	[Exception] Microsoft.Data.SqlClient.dll!Microsoft.Data.SqlClient.SqlCommand.ExecuteReader() Line 2064	C#
 	[Exception] SqlGeographyTest.dll!SqlGeographyTest.Program.Main(string[] args) Line 51	C#
 	Microsoft.Data.SqlClient.dll!Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(System.Data.CommandBehavior behavior) Line 2103	C#
 	Microsoft.Data.SqlClient.dll!Microsoft.Data.SqlClient.SqlCommand.ExecuteReader() Line 2064	C#
 	SqlGeographyTest.dll!SqlGeographyTest.Program.Main(string[] args) Line 51	C#

To reproduce

Create the following stored procedure and user defined table in SQL Server.

CREATE TYPE [dbo].[SqlGeogTestTable] AS TABLE(
	[Id] [uniqueidentifier] NULL,
	[StreetName] [NVarChar](100) NULL,
	[geom] [geography] NULL
)
GO

CREATE PROCEDURE [dbo].[SqlGeogTestProc]
    @newRoads as [dbo].[SqlGeogTestTable] READONLY
AS
BEGIN
 SELECT * FROM @newRoads
END
GO

Create a console app to call the stored procedure.

using Microsoft.Data.SqlClient;
using Microsoft.Data.SqlClient.Server;
using Microsoft.SqlServer.Types;

using System.Data;

namespace SqlGeographyTest
{
	class Program
	{
		static void Main(string[] args)
		{
			SqlGeography geog = SqlGeography.Point(43, -81, 4326);
			
			SqlMetaData[] metadata = new SqlMetaData[] 
			{
				new SqlMetaData("Id", SqlDbType.UniqueIdentifier),
				new SqlMetaData("StreetName", SqlDbType.Text),
				new SqlMetaData("geom", SqlDbType.Udt, typeof(SqlGeography), "Geography") 
			};

			SqlDataRecord record1 = new SqlDataRecord(metadata);

			record1.SetValues(Guid.NewGuid(), "ELM", geog);

			SqlDataRecord record2 = new SqlDataRecord(metadata);

			record2.SetValues(Guid.NewGuid(), "MAIN", geog);

			IList<SqlDataRecord> featureInserts = new List<SqlDataRecord>();

			featureInserts.Add(record1);
			featureInserts.Add(record2); // If you remove this line it works fine (with only one record).

			using (SqlConnection conn = new SqlConnection("Data Source=xxxx;Initial Catalog=xxxx;Integrated Security=SSPI;Encrypt=false"))
			{
				conn.Open();

				{
					SqlCommand cmd = new SqlCommand("SqlGeogTestProc");
					cmd.CommandType = CommandType.StoredProcedure;

					SqlParameter param = new SqlParameter("@newRoads", SqlDbType.Structured);
					param.Value = featureInserts;
					param.TypeName = "[dbo].[SqlGeogTestTable]";

					cmd.Parameters.Add(param);

					cmd.Connection = conn;
					
					var reader = cmd.ExecuteReader();

					while (reader.Read())
					{
						Console.WriteLine("Id = " + reader[0]);
						Console.WriteLine("StreetName = " + reader[1]);
						Console.WriteLine("geom = " + reader[2]);
					}
				}
			}
		}
	 }
}

The issue appears to be metadata related. Specifically, the error comes out of the MetaDataUtilsSmi.IsCompatible in Microsoft.Data.SqlClient.Server. IsCompatible returns false on the second record (for the first record stored in peekAhead doesn't seem to call IsCompatible).

 internal static bool IsCompatible(SmiMetaData firstMd, SqlMetaData secondMd)
 {
     return firstMd.SqlDbType == secondMd.SqlDbType &&
             firstMd.MaxLength == secondMd.MaxLength &&
             firstMd.Precision == secondMd.Precision &&
             firstMd.Scale == secondMd.Scale &&
             firstMd.CompareOptions == secondMd.CompareOptions &&
             firstMd.LocaleId == secondMd.LocaleId &&
             firstMd.Type == secondMd.Type &&
             firstMd.SqlDbType != SqlDbType.Structured &&  // SqlMetaData doesn't support Structured types
             !firstMd.IsMultiValued;  // SqlMetaData doesn't have a "multivalued" option
 }

When the above method is called for the second record, firstMd.Type is null (and should not be) causing IsCompatible to return false.

Expected behavior

Metadata should be correctly set so that the call to the stored procedure succeeds.

Further technical details

Microsoft.Data.SqlClient version: 5.2.0
Microsoft.SqlServer.Server version: 1.0.0
Microsoft.SqlServer.Types version: 160.1000.6
.NET target: .NET 8
SQL Server version: SQL Server 2019
Operating system: Windows 11

Additional context
Possibly related to case 2445.

[ErikEJ]

@LimeyChris Looks like this was fixed in #2445 - PR here: #2457

[DavoudEshtehari]

Addressed in #2445.