How to unit test SQL Server User-Defined Table Types against C# classes to ensure property order and compatibility?

I'm working on a .NET 8 application that uses SQL Server User-Defined Table Types (UDTT) to pass data to stored procedures. I have a situation where I need to ensure that my C# classes remain compatible with the database UDTTs, especially regarding property order and existence

In SQL Server, I have a UDTT that looks like this (simplified example):

CREATE TYPE [dbo].[DefinedType] AS TABLE ( [Property1] INT NOT NULL, [Property2] VARCHAR (100) NOT NULL, [Property3] VARCHAR (50) NULL, [Property4] DATETIME NOT NULL -- More properties... )

And in my C# code, I have a class that maps to this UDTT:

public class Type { public int Property1 { get; set; } public string Property2 { get; set; } public string Property3 { get; set; } public DateTime Property4 { get; set; } // More properties... }

I use this class to populate a DataTable that is passed as a parameter to a stored procedure.

The Problem

The issue is that SQL Server UDTTs are order-sensitive. When I pass a DataTable to the stored procedure, the columns must match the UDTT definition in both name AND order.

If someone adds a new property to the UDTT (e.g., Property5), and I update my C# class accordingly but place the new property in the wrong position (not matching the UDTT column order), the stored procedure will fail at runtime.

For example, if the UDTT now has:

Property1, Property2, Property3, Property5, Property4

But my C# class has:

public class Type { public int Property1 { get; set; } public string Property2 { get; set; } public string Property3 { get; set; } public DateTime Property4 { get; set; } public string Property5 { get; set; } // Added at the end }

The DataTable will have columns in the order of the C# properties, which won't match the UDTT order, causing a runtime error.

What I've Considered

I'm thinking about creating a unit test that:

Reads the .sql file containing the UDTT definition

Uses regex to extract the column names in the order they appear in the CREATE TYPE statement

Uses reflection to get the C# class properties in the order they are declared

Compares both lists to ensure:

All column names match (case-insensitive)

The order is identical

Something like this:

[Fact] public void PedidoType_Should_Match_UDTT_Definition() { // Read SQL file string sqlContent = File.ReadAllText("PathTo/DefinedType.sql"); // Extract column names using regex var columnNames = ExtractColumnNamesFromSql(sqlContent); // Get C# properties in declaration order var properties = typeof(Type) .GetProperties() .Select(p => p.Name) .ToList(); // Assert they match in name and order Assert.Equal(columnNames, properties); }

My Questions

Is this approach reliable? Are there any edge cases I should consider (like computed columns, column constraints, etc.)?

Are there better alternatives to ensure UDTT and C# class compatibility?

Has anyone implemented a more robust solution for this problem? Maybe using attributes, source generators, or database metadata queries instead of parsing SQL files?

Would it be better to query the database metadata directly to get the UDTT definition instead of parsing the SQL file?

I'm looking for a maintainable solution that can be part of our CI/CD pipeline to catch these issues early, before they reach production.

Any suggestions or experiences would be greatly appreciated!