Limited SQL Computed column support when using SqlParameter.Structured

[PK-ByTE]

Describe the bug

When using udtt's with SqlParameter.Structured the passing of values into the underlying udtt works only if the udtt lacks any computed columns.
Like so;

But if we add any computed column into the udtt the passing of values into the underlying udtt fails on first computed column and library does not provide any means to skip computed columns or mitigate this issues atleast none that I know of (Tried multiple approaches). One of them listed below like so;

The issue came up when we tried to create an udtt that would calculate hash value based on the value fields in the udtt and store it in the udtt as varbinary field, thus would allow us to use the calculated hash value to determine if some data has changed when compared to the data in the db table.

To reproduce

Program.cs

 internal class Program
 {
     static async Task Main(string[] args)
     {
         var x = new TestAppSimple();
         await x.Test();
     }
 }

TestAppSimple.cs

 internal class TestAppSimple
{
    private SqlConnection Connection { get; set; }

    public TestAppSimple()
    {
        var connectionString = "Server=localhost;...";
       
        Connection = new SqlConnection(connectionString);

    }

    public async Task Test()
    {
        try
        {
            Connection.Open();

            //As Datatable
            await TryToPassUdttWithoutComputedAsStructuredDataTableParamSimple(); //Works!

            await TryToPassUdttWithComputedAsStructuredDataTableParamSimple1(); //Fails!
            await TryToPassUdttWithComputedAsStructuredDataTableParamSimple2(); //Fails!
            await TryToPassUdttWithComputedAsStructuredDataTableParamSimple3(); //Fails!
            await TryToPassUdttWithComputedAsStructuredDataTableParamSimple4(); //Fails!
            await TryToPassUdttWithComputedAsStructuredDataTableParamSimple5(); //Fails!
            await TryToPassUdttWithComputedAsStructuredDataTableParamSimple6(); //Fails!

            //As SqlDataRecord
            await TryToPassUdttWithoutComputedAsStructuredDataRecordParamSimple1(); //Works!

            await TryToPassUdttWithComputedAsStructuredDataRecordParamSimple1(); //Fails!
            await TryToPassUdttWithComputedAsStructuredDataRecordParamSimple2(); //Fails!
            await TryToPassUdttWithComputedAsStructuredDataRecordParamSimple3(); //Fails!
            await TryToPassUdttWithComputedAsStructuredDataRecordParamSimple4(); //Fails!
            await TryToPassUdttWithComputedAsStructuredDataRecordParamSimple5(); //Fails!
            //await TryToPassUdttWithComputedAsStructuredDataRecordParamSimple6(); //Fails!
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
        }
        finally
        {
            Connection?.Close();
            Connection?.Dispose();
        }
    }

    #region Sql tests

    #region As DataTable
    private async Task TryToPassUdttWithoutComputedAsStructuredDataTableParamSimple()
    {
        try
        {
            var command = Connection.CreateCommand();
            command.CommandText = "[dbo].[spTestUdttWithoutComputed]";
            command.CommandTimeout = 30;
            command.CommandType = CommandType.StoredProcedure;

            //init param data
            var nums = new List<(int Num1, int Num2)>() { (1, 1), (2, 2), (3, 3) };

            //populate dt parameter
            var dtNums = new DataTable();
            dtNums.Columns.Add(new DataColumn("Num1", typeof(int)));
            dtNums.Columns.Add(new DataColumn("Num2", typeof(int)));
            foreach (var num in nums)
            {
                var dataRow = dtNums.NewRow();
                dataRow["Num1"] = num.Num1;
                dataRow["Num2"] = num.Num2;
                dtNums.Rows.Add(dataRow);
            }

            //pass dt parameter to procedure
            command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@numsWithoutComputed",
                SqlDbType = SqlDbType.Structured,
                TypeName = "[dbo].[udttNumsWithoutCompute]",
                Value = dtNums
            });


            using (var reader = await command.ExecuteReaderAsync())
            {
                //if passes here, then it worked.
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
        }

    }

    private async Task TryToPassUdttWithComputedAsStructuredDataTableParamSimple1()
    {
        try
        {
            var command = Connection.CreateCommand();
            command.CommandText = "[dbo].[spTestUdttWithComputed]";
            command.CommandTimeout = 30;
            command.CommandType = CommandType.StoredProcedure;

            //init param data
            var nums = new List<(int Num1, int Num2)>() { (1, 1), (2, 2), (3, 3) };

            //populate dt parameter
            var dtNums = new DataTable();
            dtNums.Columns.Add(new DataColumn("Num1", typeof(int)));
            dtNums.Columns.Add(new DataColumn("Num2", typeof(int)));

            //Total column omitted from datatable, because is computed, we cannot pass the value to it.

            foreach (var num in nums)
            {
                var dataRow = dtNums.NewRow();
                dataRow["Num1"] = num.Num1;
                dataRow["Num2"] = num.Num2;
                dtNums.Rows.Add(dataRow);
            }

            //pass dt parameter to procedure
            command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@numsWithComputed",
                SqlDbType = SqlDbType.Structured,
                TypeName = "[dbo].[udttNumsWithCompute]",
                Value = dtNums
            });


            using (var reader = await command.ExecuteReaderAsync())
            {
                //if passes here, then it worked.
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
        }

    }

    private async Task TryToPassUdttWithComputedAsStructuredDataTableParamSimple2()
    {
        try
        {
            var command = Connection.CreateCommand();
            command.CommandText = "[dbo].[spTestUdttWithComputed]";
            command.CommandTimeout = 30;
            command.CommandType = CommandType.StoredProcedure;

            //init param data
            var nums = new List<(int Num1, int Num2)>() { (1, 1), (2, 2), (3, 3) };

            //populate dt parameter
            var dtNums = new DataTable();
            dtNums.Columns.Add(new DataColumn("Num1", typeof(int)));
            dtNums.Columns.Add(new DataColumn("Num2", typeof(int)));

            //Total column defined in datatable, but not populated on rows because is computed, we cannot pass the value to it.
            dtNums.Columns.Add(new DataColumn("Total", typeof(int)));

            foreach (var num in nums)
            {
                var dataRow = dtNums.NewRow();
                dataRow["Num1"] = num.Num1;
                dataRow["Num2"] = num.Num2;
                dtNums.Rows.Add(dataRow);
            }

            //pass dt parameter to procedure
            command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@numsWithComputed",
                SqlDbType = SqlDbType.Structured,
                TypeName = "[dbo].[udttNumsWithCompute]",
                Value = dtNums
            });


            using (var reader = await command.ExecuteReaderAsync())
            {
                //if passes here, then it worked.
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
        }

    }

    private async Task TryToPassUdttWithComputedAsStructuredDataTableParamSimple3()
    {
        try
        {
            var command = Connection.CreateCommand();
            command.CommandText = "[dbo].[spTestUdttWithComputed]";
            command.CommandTimeout = 30;
            command.CommandType = CommandType.StoredProcedure;

            //init param data
            var nums = new List<(int Num1, int Num2)>() { (1, 1), (2, 2), (3, 3) };

            //populate dt parameter
            var dtNums = new DataTable();
            dtNums.Columns.Add(new DataColumn("Num1", typeof(int)));
            dtNums.Columns.Add(new DataColumn("Num2", typeof(int)));

            //Total column defined in datatable and dummy value assigned on rows
            dtNums.Columns.Add(new DataColumn("Total", typeof(int)));

            foreach (var num in nums)
            {
                var dataRow = dtNums.NewRow();
                dataRow["Num1"] = num.Num1;
                dataRow["Num2"] = num.Num2;
                dataRow["Total"] = 0; //Dummy value of total!
                dtNums.Rows.Add(dataRow);
            }

            //pass dt parameter to procedure
            command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@numsWithComputed",
                SqlDbType = SqlDbType.Structured,
                TypeName = "[dbo].[udttNumsWithCompute]",
                Value = dtNums
            });


            using (var reader = await command.ExecuteReaderAsync())
            {
                //if passes here, then it worked.
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
        }

    }

    private async Task TryToPassUdttWithComputedAsStructuredDataTableParamSimple4()
    {
        try
        {
            var command = Connection.CreateCommand();
            command.CommandText = "[dbo].[spTestUdttWithComputed]";
            command.CommandTimeout = 30;
            command.CommandType = CommandType.StoredProcedure;

            //init param data
            var nums = new List<(int Num1, int Num2)>() { (1, 1), (2, 2), (3, 3) };

            //populate dt parameter
            var dtNums = new DataTable();
            dtNums.Columns.Add(new DataColumn("Num1", typeof(int)));
            dtNums.Columns.Add(new DataColumn("Num2", typeof(int)));

            //Total column defined in datatable as readonly, but not populated on rows because is computed, we cannot pass the value to it.
            var totalColumn = new DataColumn("Total", typeof(int));
            totalColumn.ReadOnly = true;

            dtNums.Columns.Add(totalColumn);

            foreach (var num in nums)
            {
                var dataRow = dtNums.NewRow();
                dataRow["Num1"] = num.Num1;
                dataRow["Num2"] = num.Num2;
                dtNums.Rows.Add(dataRow);
            }

            //pass dt parameter to procedure
            command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@numsWithComputed",
                SqlDbType = SqlDbType.Structured,
                TypeName = "[dbo].[udttNumsWithCompute]",
                Value = dtNums
            });


            using (var reader = await command.ExecuteReaderAsync())
            {
                //if passes here, then it worked.
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
        }

    }

    private async Task TryToPassUdttWithComputedAsStructuredDataTableParamSimple5()
    {
        try
        {
            var command = Connection.CreateCommand();
            command.CommandText = "[dbo].[spTestUdttWithComputed]";
            command.CommandTimeout = 30;
            command.CommandType = CommandType.StoredProcedure;

            //init param data
            var nums = new List<(int Num1, int Num2)>() { (1, 1), (2, 2), (3, 3) };

            //populate dt parameter
            var dtNums = new DataTable();
            dtNums.Columns.Add(new DataColumn("Num1", typeof(int)));
            dtNums.Columns.Add(new DataColumn("Num2", typeof(int)));

            //Total column defined in datatable as readonly and populated on rows
            var totalColumn = new DataColumn("Total", typeof(int));
            totalColumn.ReadOnly = true;

            dtNums.Columns.Add(totalColumn);

            foreach (var num in nums)
            {
                var dataRow = dtNums.NewRow();
                dataRow["Num1"] = num.Num1;
                dataRow["Num2"] = num.Num2;
                dataRow["Total"] = 0; //Dummy value of total!
                dtNums.Rows.Add(dataRow);
            }

            //pass dt parameter to procedure
            command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@numsWithComputed",
                SqlDbType = SqlDbType.Structured,
                TypeName = "[dbo].[udttNumsWithCompute]",
                Value = dtNums
            });


            using (var reader = await command.ExecuteReaderAsync())
            {
                //if passes here, then it worked.
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
        }

    }

    private async Task TryToPassUdttWithComputedAsStructuredDataTableParamSimple6()
    {
        try
        {
            var command = Connection.CreateCommand();
            command.CommandText = "[dbo].[spTestUdttWithComputed]";
            command.CommandTimeout = 30;
            command.CommandType = CommandType.StoredProcedure;

            //init param data
            var nums = new List<(int Num1, int Num2)>() { (1, 1), (2, 2), (3, 3) };

            //populate dt parameter
            var dtNums = new DataTable();
            dtNums.Columns.Add(new DataColumn("Num1", typeof(int)));
            dtNums.Columns.Add(new DataColumn("Num2", typeof(int)));

            //Total column defined in datatable as readonly and populated on rows with default of (int)
            var totalColumn = new DataColumn("Total", typeof(int));
            totalColumn.ReadOnly = true;

            dtNums.Columns.Add(totalColumn);

            foreach (var num in nums)
            {
                var dataRow = dtNums.NewRow();
                dataRow["Num1"] = num.Num1;
                dataRow["Num2"] = num.Num2;
                dataRow["Total"] = default(int);
                dtNums.Rows.Add(dataRow);
            }

            //pass dt parameter to procedure
            command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@numsWithComputed",
                SqlDbType = SqlDbType.Structured,
                TypeName = "[dbo].[udttNumsWithCompute]",
                Value = dtNums
            });


            using (var reader = await command.ExecuteReaderAsync())
            {
                //if passes here, then it worked.
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
        }

    }
    #endregion

    #region As SqlDataRecord
    private async Task TryToPassUdttWithoutComputedAsStructuredDataRecordParamSimple1()
    {
        try
        {
            var command = Connection.CreateCommand();
            command.CommandText = "[dbo].[spTestUdttWithoutComputed]";
            command.CommandTimeout = 30;
            command.CommandType = CommandType.StoredProcedure;

            //init param data
            var nums = new List<(int Num1, int Num2)>() { (1, 1), (2, 2), (3, 3) };

            //populate records parameter
            SqlMetaData[] metaData = new SqlMetaData[2];
            metaData[0] = new SqlMetaData("Num1", SqlDbType.Int);
            metaData[1] = new SqlMetaData("Num2", SqlDbType.Int);

            List<SqlDataRecord> records = new List<SqlDataRecord>();

            foreach (var num in nums)
            {
                SqlDataRecord record = new SqlDataRecord(metaData);
                record.SetInt32(0, num.Num1);
                record.SetInt32(1, num.Num2);
                records.Add(record);
            }

            //pass dt parameter to procedure
            command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@numsWithoutComputed",
                SqlDbType = SqlDbType.Structured,
                TypeName = "[dbo].[udttNumsWithoutCompute]",
                Value = records
            });


            using (var reader = await command.ExecuteReaderAsync())
            {
                //if passes here, then it worked.
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
        }

    }

    private async Task TryToPassUdttWithComputedAsStructuredDataRecordParamSimple1()
    {
        try
        {
            var command = Connection.CreateCommand();
            command.CommandText = "[dbo].[spTestUdttWithComputed]";
            command.CommandTimeout = 30;
            command.CommandType = CommandType.StoredProcedure;

            //init param data
            var nums = new List<(int Num1, int Num2)>() { (1, 1), (2, 2), (3, 3) };

            //populate records parameter
            SqlMetaData[] metaData = new SqlMetaData[2];
            metaData[0] = new SqlMetaData("Num1", SqlDbType.Int);
            metaData[1] = new SqlMetaData("Num2", SqlDbType.Int);

            //Total column omitted from metadata, because is computed, we cannot pass the value to it.

            List<SqlDataRecord> records = new List<SqlDataRecord>();

            foreach (var num in nums)
            {
                SqlDataRecord record = new SqlDataRecord(metaData);
                record.SetInt32(0, num.Num1);
                record.SetInt32(1, num.Num2);
                records.Add(record);
            }

            //pass dt parameter to procedure
            command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@numsWithComputed",
                SqlDbType = SqlDbType.Structured,
                TypeName = "[dbo].[udttNumsWithCompute]",
                Value = records
            });


            using (var reader = await command.ExecuteReaderAsync())
            {
                //if passes here, then it worked.
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
        }

    }

    private async Task TryToPassUdttWithComputedAsStructuredDataRecordParamSimple2()
    {
        try
        {
            var command = Connection.CreateCommand();
            command.CommandText = "[dbo].[spTestUdttWithComputed]";
            command.CommandTimeout = 30;
            command.CommandType = CommandType.StoredProcedure;

            //init param data
            var nums = new List<(int Num1, int Num2)>() { (1, 1), (2, 2), (3, 3) };

            //populate records parameter
            SqlMetaData[] metaData = new SqlMetaData[3];
            metaData[0] = new SqlMetaData("Num1", SqlDbType.Int);
            metaData[1] = new SqlMetaData("Num2", SqlDbType.Int);

            //Total column defined in metadata, but not populated on records because is computed, we cannot pass the value to it.
            metaData[2] = new SqlMetaData("Total", SqlDbType.Int);

            List<SqlDataRecord> records = new List<SqlDataRecord>();

            foreach (var num in nums)
            {
                SqlDataRecord record = new SqlDataRecord(metaData);
                record.SetInt32(0, num.Num1);
                record.SetInt32(1, num.Num2);
                records.Add(record);
            }

            //pass dt parameter to procedure
            command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@numsWithComputed",
                SqlDbType = SqlDbType.Structured,
                TypeName = "[dbo].[udttNumsWithCompute]",
                Value = records
            });


            using (var reader = await command.ExecuteReaderAsync())
            {
                //if passes here, then it worked.
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
        }

    }

    private async Task TryToPassUdttWithComputedAsStructuredDataRecordParamSimple3()
    {
        try
        {
            var command = Connection.CreateCommand();
            command.CommandText = "[dbo].[spTestUdttWithComputed]";
            command.CommandTimeout = 30;
            command.CommandType = CommandType.StoredProcedure;

            //init param data
            var nums = new List<(int Num1, int Num2)>() { (1, 1), (2, 2), (3, 3) };

            //populate records parameter
            SqlMetaData[] metaData = new SqlMetaData[3];
            metaData[0] = new SqlMetaData("Num1", SqlDbType.Int);
            metaData[1] = new SqlMetaData("Num2", SqlDbType.Int);

            //Total column defined in metadata and dummy value assigned on records
            metaData[2] = new SqlMetaData("Total", SqlDbType.Int);

            List<SqlDataRecord> records = new List<SqlDataRecord>();

            foreach (var num in nums)
            {
                SqlDataRecord record = new SqlDataRecord(metaData);
                record.SetInt32(0, num.Num1);
                record.SetInt32(1, num.Num2);
                record.SetInt32(2, 0); //Dummy value of total!
                records.Add(record);
            }

            //pass dt parameter to procedure
            command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@numsWithComputed",
                SqlDbType = SqlDbType.Structured,
                TypeName = "[dbo].[udttNumsWithCompute]",
                Value = records
            });


            using (var reader = await command.ExecuteReaderAsync())
            {
                //if passes here, then it worked.
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
        }

    }

    private async Task TryToPassUdttWithComputedAsStructuredDataRecordParamSimple4()
    {
        try
        {
            var command = Connection.CreateCommand();
            command.CommandText = "[dbo].[spTestUdttWithComputed]";
            command.CommandTimeout = 30;
            command.CommandType = CommandType.StoredProcedure;

            //init param data
            var nums = new List<(int Num1, int Num2)>() { (1, 1), (2, 2), (3, 3) };

            //populate records parameter
            SqlMetaData[] metaData = new SqlMetaData[3];
            metaData[0] = new SqlMetaData("Num1", SqlDbType.Int);
            metaData[1] = new SqlMetaData("Num2", SqlDbType.Int);

            //Total column defined in metadata as use server value, but not populated on records because is computed, we cannot pass the value to it. !Hox. Did not find a way to set useServerDefault to true without setting isUniqueKey and sorting!
            metaData[2] = new SqlMetaData("Total", SqlDbType.Int, useServerDefault:true, isUniqueKey:false, columnSortOrder: SortOrder.Ascending, sortOrdinal:0);

            List<SqlDataRecord> records = new List<SqlDataRecord>();

            foreach (var num in nums)
            {
                SqlDataRecord record = new SqlDataRecord(metaData);
                record.SetInt32(0, num.Num1);
                record.SetInt32(1, num.Num2);
                records.Add(record);
            }

            //pass dt parameter to procedure
            command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@numsWithComputed",
                SqlDbType = SqlDbType.Structured,
                TypeName = "[dbo].[udttNumsWithCompute]",
                Value = records
            });


            using (var reader = await command.ExecuteReaderAsync())
            {
                //if passes here, then it worked.
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
        }

    }

    private async Task TryToPassUdttWithComputedAsStructuredDataRecordParamSimple5()
    {
        try
        {
            var command = Connection.CreateCommand();
            command.CommandText = "[dbo].[spTestUdttWithComputed]";
            command.CommandTimeout = 30;
            command.CommandType = CommandType.StoredProcedure;

            //init param data
            var nums = new List<(int Num1, int Num2)>() { (1, 1), (2, 2), (3, 3) };

            //populate records parameter
            SqlMetaData[] metaData = new SqlMetaData[3];
            metaData[0] = new SqlMetaData("Num1", SqlDbType.Int);
            metaData[1] = new SqlMetaData("Num2", SqlDbType.Int);

            //Total column defined in metadata as use server value, but not populated on records because is computed, we cannot pass the value to it. !Hox. Did not find a way to set useServerDefault to true without setting isUniqueKey and sorting!
            metaData[2] = new SqlMetaData("Total", SqlDbType.Int, useServerDefault: true, isUniqueKey: false, columnSortOrder: SortOrder.Ascending, sortOrdinal: 0);

            List<SqlDataRecord> records = new List<SqlDataRecord>();

            foreach (var num in nums)
            {
                SqlDataRecord record = new SqlDataRecord(metaData);
                record.SetInt32(0, num.Num1);
                record.SetInt32(1, num.Num2);
                record.SetInt32(2, 0); //Dummy value of total!
                records.Add(record);
            }

            //pass dt parameter to procedure
            command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@numsWithComputed",
                SqlDbType = SqlDbType.Structured,
                TypeName = "[dbo].[udttNumsWithCompute]",
                Value = records
            });


            using (var reader = await command.ExecuteReaderAsync())
            {
                //if passes here, then it worked.
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
        }

    }
    #endregion

    #endregion

}

sql - udttNumsWithCompute

CREATE TYPE [dbo].[udttNumsWithCompute] AS TABLE(
    [Num1] INT NULL,
    [Num2] INT NULL,
    [Total] AS (ISNULL([Num1], 0) + ISNULL([Num2], 0)) --COMPUTE COLUMN
)
GO

sql - udttNumsWithoutCompute

CREATE TYPE [dbo].[udttNumsWithoutCompute] AS TABLE(
    [Num1] INT NULL,
    [Num2] INT NULL
)
GO

sql - spTestUdttWithComputed

/******************************************************************
 *
 *   PROCEDURE: [spTestUdttWithComputed] 
 *   This will try to pass udtt with computed column into a procedure as parameter.
 ******************************************************************/
CREATE OR ALTER PROC [dbo].[spTestUdttWithComputed]
(
  @numsWithComputed [dbo].[udttNumsWithCompute] READONLY
)
AS
    SET NOCOUNT ON;

    DECLARE @context NVARCHAR(255) = '[spTestUdttWithComputed]'

    SELECT * FROM @numsWithComputed;
	
GO

sql - spTestUdttWithoutComputed

/******************************************************************
 *
 *   PROCEDURE: [spTestUdttWithoutComputed] 
 *   This will try to pass udtt without computed column into a procedure as parameter.
 ******************************************************************/
CREATE OR ALTER PROC [dbo].[spTestUdttWithoutComputed]
(
  @numsWithoutComputed [dbo].[udttNumsWithoutCompute] READONLY
)
AS
    SET NOCOUNT ON;

    DECLARE @context NVARCHAR(255) = '[spTestUdttWithoutComputed]'

    SELECT * FROM @numsWithoutComputed;
	
GO

Expected behavior

The library should provide a way to configure the functionality of SqlParameter.Structured so that it would skip computed columns and let the server resolve the value in the udtt, once the rest of the fields are populated that the computed column depends upon.

Further technical details

Microsoft.Data.SqlClient version: 6.0.1
.NET target: .NET 8, .NET 9, .NET 10
SQL Server version: SQL Server 2022 (16.0.1135.2)
Operating system: Microsoft Windows 11 Business (10.0.22631)

[mdaigle]

Hi @PK-ByTE, I don't believe SQL Server itself supports computed columns within UDTs. So it wouldn't be possible for us to do anything on the client side to support your request. If you see documentation indicating otherwise, please share it with me!

The closest I can see to what you're looking for is a computed columns over a UDT expression, which is already supported.

[PK-ByTE]

Hi @mdaigle ,

Well I have used this functionality in sql server/sql scripts before and was wondering why the .NET SqlClient lacks the support for this.

I will provide an example below;

Given that we have the Num1 and Num2 and [Computed] Total available for us in the udttNumsWithCompute udtt.

CREATE TYPE [dbo].[udttNumsWithCompute] AS TABLE(
    [Num1] INT NULL,
    [Num2] INT NULL,
    [Total] AS (ISNULL([Num1], 0) + ISNULL([Num2], 0)) --COMPUTE COLUMN
)
GO

Then we can execute following script, which will just populate the Num1 and Num2 fields and the Total field will get computed automatically based on values inputted into Num1 and Num2.

DECLARE @numsWithCompute [dbo].[udttNumsWithCompute];

INSERT INTO @numsWithCompute
VALUES (1,1),(2,2),(3,3) 

SELECT * FROM @numsWithCompute

/* OUTPUT of above select

Num1	Num2	Total
1	1	2
2	2	4
3	3	6

*/

And you can actually specify for the insert part which columns we are inserting and specify only the Num1 and Num2.

DECLARE @numsWithCompute [dbo].[udttNumsWithCompute];

INSERT INTO @numsWithCompute ([Num1],[Num2])
VALUES (1,1),(2,2),(3,3) 

SELECT * FROM @numsWithCompute

/* OUTPUT of above select

Num1	Num2	Total
1	1	2
2	2	4
3	3	6

*/

Hope this helps.

[mdaigle]

Gotcha, thanks for the additional info. The difference I see in these new examples is that you're persisting the table-valued type before interacting with it. In the original examples, because you can only pass table-valued types to stored procedures as readonly, the database has no chance to compute the computed column and it can't modify the input. Therefore, there's no way to make the input match the UDT schema. This is all enforced server-side. I suspect your examples above will work when run over persisted values.

You can read more about the limitations here: https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters#limitations-of-table-valued-parameters.

[PK-ByTE]

I still think that this is a lack of library support, because if I create the whole sql command manually it executes just fine, but I'm unable to pass the udtt via command parameter to the query inside the command (because of the udtt validation, that requires that all columns are included even though some of them might be computed).

This uses the same codebase that I have provided in this issue earlier.

Add following into the codebase, these are just some helper methods to create & populate the datatable parameter.

private DataTable GetNumsWithoutConputedColumnDatatable()
{
    //init param data
    var nums = new List<(int Num1, int Num2)>() { (1, 1), (2, 2), (3, 3) };

    //populate dt parameter
    var dtNums = new DataTable();
    dtNums.Columns.Add(new DataColumn("Num1", typeof(int)));
    dtNums.Columns.Add(new DataColumn("Num2", typeof(int)));
    foreach (var num in nums)
    {
        var dataRow = dtNums.NewRow();
        dataRow["Num1"] = num.Num1;
        dataRow["Num2"] = num.Num2;
        dtNums.Rows.Add(dataRow);
    }

    return dtNums;
}

private DataTable GetNumsWithConputedColumnDatatable()
{
    //init param data
    var nums = new List<(int Num1, int Num2)>() { (1, 1), (2, 2), (3, 3) };

    //populate dt parameter
    var dtNums = new DataTable();
    dtNums.Columns.Add(new DataColumn("Num1", typeof(int)));
    dtNums.Columns.Add(new DataColumn("Num2", typeof(int)));
    dtNums.Columns.Add(new DataColumn("Total", typeof(int)));
    foreach (var num in nums)
    {
        var dataRow = dtNums.NewRow();
        dataRow["Num1"] = num.Num1;
        dataRow["Num2"] = num.Num2;
        dtNums.Rows.Add(dataRow);
    }

    return dtNums;
}

Let me demonstrate the problem;

If we create the whole command as plain string, it works.

//This works!
private async Task TryToPassUdttWithComputedManually()
{
    try
    {
        // Define the exec statement with the udtt.
        string sqlExec = @"

            DECLARE @numsWithCompute [dbo].[udttNumsWithCompute];
            INSERT INTO @numsWithCompute
            VALUES (1,1),(2,2),(3,3) 

            EXEC [dbo].[spTestUdttWithComputed] @numsWithCompute;

        ";

        // Create command
        SqlCommand execCommand = new SqlCommand(sqlExec, Connection);

        // Exec command
        using (var reader = await execCommand.ExecuteReaderAsync())
        {
            //if passes here, then it worked.
        }
    }
    catch (Exception e)
    {
        Console.WriteLine(e.Message);
    }
}

But if we try to provide the @numsWithCompute values via command parameter it fails.

/*
This fails;
Trying to pass a table-valued parameter with 2 column(s) where the corresponding user-defined table type requires //3 column(s).
*/

private async Task TryToPassUdttWithComputedManuallyWithParamWithoutTotalColumn()
{
    try
    {
        // Define the exec statement with the udtt.
        string sqlExec = @"

            EXEC [dbo].[spTestUdttWithComputed] @numsWithCompute;

        ";

        // Create command
        SqlCommand execCommand = new SqlCommand(sqlExec, Connection);

        // Configure the command and parameter.  
        SqlParameter numsParam = execCommand.Parameters.AddWithValue("@numsWithCompute", GetNumsWithoutComputedColumnDatatable());
        numsParam.SqlDbType = SqlDbType.Structured;
        numsParam.TypeName = "[dbo].[udttNumsWithCompute]";

        // Exec command
        using (var reader = await execCommand.ExecuteReaderAsync())
        {
            //if passes here, then it worked.
        }
    }
    catch (Exception e)
    {
        Console.WriteLine(e.Message);
    }
}

Even if we try to mimic the udtt with extra total column, it still fails.

/*
This fails;
The column "Total" cannot be modified because it is either a computed column or is the result of a UNION operator.
*/

private async Task TryToPassUdttWithComputedManuallyWithParamWithTotalColumn()
{
    try
    {
        // Define the exec statement with the udtt.
        string sqlExec = @"

            EXEC [dbo].[spTestUdttWithComputed] @numsWithCompute;

        ";

        // Create command
        SqlCommand execCommand = new SqlCommand(sqlExec, Connection);

        // Configure the command and parameter.  
        SqlParameter numsParam = execCommand.Parameters.AddWithValue("@numsWithCompute", GetNumsWithConputedColumnDatatable());
        numsParam.SqlDbType = SqlDbType.Structured;
        numsParam.TypeName = "[dbo].[udttNumsWithCompute]";

        // Exec command
        using (var reader = await execCommand.ExecuteReaderAsync())
        {
            //if passes here, then it worked.
        }
    }
    catch (Exception e)
    {
        Console.WriteLine(e.Message);
    }
}

Can the library provide support for udtt that have computed column in them defined?
You could use the datatable's column's isReadOnly flag or some similar flag that would tell the library's internal logic that some column might be read only and should not be required?

[mdaigle]

Your stored procedure works when you first insert data to the table-types UDT. You need to insert first so that the computed column can have a value set when the UDT data is in a writeable state.

For example:

DECLARE @UdttNums AS [dbo].[udttNumsWithCompute]
INSERT INTO @UdttNums VALUES(1,2)
EXECUTE [dbo].[spTestUdttWithComputed] @UdttNums
GO

Returns:

Num1	Num2	Total
1	2	3

[PK-ByTE]

Yeah, As I have stated earlier my example works if you write the whole sql script manually, but how come we cannot use the library's functionality;

SqlParameter numsParam = execCommand.Parameters.Add
or
SqlParameter numsParam = execCommand.Parameters.AddWithValue

To make the same thing work?

Do I have to create my inhouse string concat logic for udtt's with computed column, that it would generate sql script such as in your or mine example?

I was hoping that library would support atleast this kind of functionality;

[mdaigle]

Thanks for bearing with me. The issue I'm trying to highlight is that the behavior you're looking for in the API can't be one-to-one with the underlying SQL (I think we're in agreement on this). To make this type of API possible, we would need to send additional commands or rewrite your commands under the hood, both of which are atypical behavior. We try to avoid layering behavior on top of SQL because it makes it hard to anticipate how the driver will behave.

That said, I understand where you're coming from and agree that it seems more ergonomic for the API to work in the way you suggested. Let me give it a bit more thought to see if I have any ideas.