User Defined Types - TVP

[jdubsmith]

Question

Our team was wondering if EFCPT is planning on supporting Table UDTs as parameters in stored procedures and or functions. Our goal is to have all the objects in our database project scaffold-ed to reduce the chance of model drift and errors.

Thanks,

Jeff

EF Core Power Tools version

No response

EF Core Power Tools CLI version

10.1.1055

Database provider

sql

Target framework

No response

IDE

CLI / Visual Studio / Rider

Do you use T4 templates?

No

Do you use Handlebars templates?

None

Is a SQL Server .dacpac / Database project used?

dacpac

[ErikEJ]

Please show what this would look like using manual mapping/ raw ADO.NET.

And provide a runnable sample of something that is not working, and I will consider / investigate

[jdubsmith]

Erik,

Attached is a zip file that has a NorthWind database BAK file. It also has a C# solution with a console and database project. In the console application, take a look at the UserDefinedType folder for an example of a possible solution.

Thanks,

NorthWind.EFCPT.zip

Jeff

[ErikEJ]

@jdubsmith OK, so these are the moving parts below.

So in order to make this work, EF Core Power Tools would:

1: Discover the SQL shape of the user defined type - DONE
2: Create a class for the user defined type - DONE - by default, with option to disable
3: have the ability create a datatable from each UDT parameter
4: configure the SqlParameter and method signature as in your sample

Question: What is the additional effort regarding ordering about? Is that required?

create type [dbo].[UdtInteger] as table(
   Id INT NOT NULL ,
   PRIMARY KEY CLUSTERED (Id)
);

CREATE PROCEDURE CustOrdersDetailFromList @udtInteger dbo.UdtInteger READONLY
AS
SELECT ProductName,
       UnitPrice=Od.UnitPrice,
       Quantity
--        Discount=CONVERT(int, Discount * 100),
--        ExtendedPrice=ROUND(CONVERT(money, Quantity * (1 - Discount) * Od.UnitPrice), 2)
FROM Products P, [Order Details] Od
JOIN @udtInteger a on a.Id = Od.OrderID
WHERE Od.ProductID = P.ProductID

public virtual async Task<List<CustOrdersDetailFromListResult>?> CustOrdersDetailFromListAsync(IEnumerable<UdtInteger>? userDefinedType, OutputParameter<int>? returnValue = null, CancellationToken? cancellationToken = default)
        {
            var parameterreturnValue = new SqlParameter
            {
                ParameterName = "returnValue",
                Direction = System.Data.ParameterDirection.Output,
                SqlDbType = System.Data.SqlDbType.Int,
            };

            var dt = userDefinedType?.ToDataTable();
            var sqlParameters = new []
            {
                new SqlParameter
                {
                    ParameterName = "udtInteger",
                    Value = dt == null 
                        ? Convert.DBNull
                        : dt,
                    SqlDbType = System.Data.SqlDbType.Structured,
                    TypeName = "[dbo].[UdtInteger]",
                },
                parameterreturnValue,
            };
            var _ = await _context.SqlQueryAsync<CustOrdersDetailFromListResult>("EXEC @returnValue = [dbo].[CustOrdersDetailFromList] @udtInteger = @udtInteger", sqlParameters, cancellationToken);

            returnValue?.SetValue(parameterreturnValue.Value);

            return _;
        }

[ErikEJ]

@jdubsmith Just to be clear, TVP parameters are supported today, as DataTable parameter - agree?

public virtual async Task<List<CustOrdersDetailFromListResult>> CustOrdersDetailFromListAsync(DataTable udtInteger, 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 = "udtInteger",
            Value = udtInteger ?? Convert.DBNull,
            SqlDbType = System.Data.SqlDbType.Structured,
            TypeName = "[dbo].[UdtInteger]",
        },
        parameterreturnValue,
    };
    var _ = await _context.SqlQueryAsync<CustOrdersDetailFromListResult>("EXEC @returnValue = [dbo].[CustOrdersDetailFromList] @udtInteger = @udtInteger", sqlParameters, cancellationToken);

    returnValue?.SetValue(parameterreturnValue.Value);

    return _;
}

[jdubsmith]

Eric,

Yes I agree that TVP parameters are supported as DataTables. The main concern we have is wanting to make sure everything we use in in code is from generated types that 100% match the database project. It's fairly easy to generate a DataTable to pass, but we want to make sure the DataTable matches what the database stored procedure expects. It could be easy to miss a UDT change if they are not generated.

Regarding your question about ordering... you are probably ware the UDT property order and DataTable column order have to match exactly. So whatever technique you want to use to make sure they match is what we are asking.

Lastly, the summary of steps listed above looks correct to me.

Again the goal is to have all the types we use in code from the the database to be generate so that we will have a compile error if things drift.

Thanks,

Jeff

[ErikEJ]

Thanks Jeff.

Looks like I do not need to worry about Ordering as I use .NET 8!
https://learn.microsoft.com/en-us/dotnet/api/system.type.getproperties?view=net-7.0

[ErikEJ]

Getting metadata: https://stackoverflow.com/a/46079868/183934