EntityFramework-Reverse-POCO-Code-First-Generator icon indicating copy to clipboard operation
EntityFramework-Reverse-POCO-Code-First-Generator copied to clipboard

Published 20 hours ago • verified publisher icon sjh37

Optional params in stored procedures not producing methods with defaults, nullable, optional?

Open nealculiner opened this issue 8 months ago • 2 comments

Hello @sjh37,

I'm using the latest (3.10.0) and have a MS-SQL stored procedure such as below. Shouldn't params with default values in the sproc correspond to optional params in the C# method signature?

CREATE PROCEDURE [dbo].[Tokens_PrepareAuthRequest] 
	@UserId INT,
	@ClientId NVARCHAR(50),
	@StateObject NVARCHAR(50),
	@TokenProvider NVARCHAR(50) = 'FCV',
	@ReferringUrl NVARCHAR(max) = NULL,
	@ReferringSiteName NVARCHAR(50) = NULL,
	@CallbackUrl NVARCHAR(MAX) = NULL
AS

Generates:

int Tokens_PrepareAuthRequest(int? userId, string clientId, 
    string stateObject, string tokenProvider, string referringUrl,
    string referringSiteName, string callbackUrl);
Task<int> Tokens_PrepareAuthRequestAsync(int? userId, string clientId,
    string stateObject, string tokenProvider, string referringUrl,
    string referringSiteName, string callbackUrl,
    CancellationToken cancellationToken = default(CancellationToken));

Shouldn't it generate something like this?

Task<int> Tokens_PrepareAuthRequestAsync(int? userId, string clientId,
    string stateObject, string tokenProvider = "FCV", string? referringUrl,
    string? referringSiteName, string? callbackUrl,
    CancellationToken cancellationToken = default(CancellationToken));

nealculiner avatar Mar 22 '25 14:03 nealculiner

Hi @nealculiner, That is a good idea; thanks. I'll see if I can obtain this information using the queries I'm using.

sjh37 avatar Mar 24 '25 12:03 sjh37

It already does some of it:

Creating a procedure:

CREATE OR ALTER PROCEDURE dbo.StoredProcWithDefaults
    @UserId INT = 12,
    @UserIdNull INT = NULL,
    @ClientName NVARCHAR(50) = 'Hello',
    @ClientNameNull NVARCHAR(50) = NULL,
    @ClientNameMaxNull NVARCHAR(MAX) = NULL,
    @ClientDesc VARCHAR(50) = 'World',
    @ClientDescNull VARCHAR(50) = NULL,
    @DecimalValue DECIMAL = 1.234,
    @DecimalValueNull DECIMAL = NULL,
    @Money MONEY = 4.56,
    @MoneyNull MONEY = NULL,
    @SmallMoney SMALLMONEY = 7.89,
    @SmallMoneyNull SMALLMONEY = NULL,
    @RealValue REAL = 9.876,
    @RealValueNull REAL = NULL,
    @FloatValue FLOAT = 6.54,
    @FloatValueNull FLOAT = NULL
AS
BEGIN
    SELECT 1 AS SingleValue;
END;
GO

Generates:

public List<StoredProcWithDefaultsReturnModel> StoredProcWithDefaults(
    int? userId, int? userIdNull,
    string clientName, string clientNameNull,
    string clientNameMaxNull, string clientDesc, string clientDescNull,
    decimal? decimalValue = null, decimal? decimalValueNull = null,
    decimal? money = null, decimal? moneyNull = null,
    decimal? smallMoney = null, decimal? smallMoneyNull = null,
    float? realValue = null, float? realValueNull = null,
    double? floatValue = null, double? floatValueNull = null)
{
    int procResult;
    return StoredProcWithDefaults(userId, userIdNull, clientName, clientNameNull, clientNameMaxNull, clientDesc, clientDescNull, decimalValue, decimalValueNull, money, moneyNull, smallMoney, smallMoneyNull, realValue, realValueNull, floatValue, floatValueNull, out procResult);
}

public List<StoredProcWithDefaultsReturnModel> StoredProcWithDefaults(
    int? userId, int? userIdNull,
    string clientName, string clientNameNull, string clientNameMaxNull,
    string clientDesc, string clientDescNull,
    decimal? decimalValue, decimal? decimalValueNull
    decimal? money, decimal? moneyNull,
    decimal? smallMoney, decimal? smallMoneyNull,
    float? realValue, float? realValueNull,
    double? floatValue, double? floatValueNull,
    out int procResult)
{
    var userIdParam = new SqlParameter { ParameterName = "@UserId", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Input, Value = userId.GetValueOrDefault(), Precision = 10, Scale = 0 };
    if (!userId.HasValue)
        userIdParam.Value = DBNull.Value;

    var userIdNullParam = new SqlParameter { ParameterName = "@UserIdNull", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Input, Value = userIdNull.GetValueOrDefault(), Precision = 10, Scale = 0 };
    if (!userIdNull.HasValue)
        userIdNullParam.Value = DBNull.Value;

    var clientNameParam = new SqlParameter { ParameterName = "@ClientName", SqlDbType = SqlDbType.NVarChar, Direction = ParameterDirection.Input, Value = clientName, Size = 50 };
    if (clientNameParam.Value == null)
        clientNameParam.Value = DBNull.Value;

    var clientNameNullParam = new SqlParameter { ParameterName = "@ClientNameNull", SqlDbType = SqlDbType.NVarChar, Direction = ParameterDirection.Input, Value = clientNameNull, Size = 50 };
    if (clientNameNullParam.Value == null)
        clientNameNullParam.Value = DBNull.Value;

    var clientNameMaxNullParam = new SqlParameter { ParameterName = "@ClientNameMaxNull", SqlDbType = SqlDbType.NVarChar, Direction = ParameterDirection.Input, Value = clientNameMaxNull, Size = -1 };
    if (clientNameMaxNullParam.Value == null)
        clientNameMaxNullParam.Value = DBNull.Value;

    var clientDescParam = new SqlParameter { ParameterName = "@ClientDesc", SqlDbType = SqlDbType.VarChar, Direction = ParameterDirection.Input, Value = clientDesc, Size = 50 };
    if (clientDescParam.Value == null)
        clientDescParam.Value = DBNull.Value;

    var clientDescNullParam = new SqlParameter { ParameterName = "@ClientDescNull", SqlDbType = SqlDbType.VarChar, Direction = ParameterDirection.Input, Value = clientDescNull, Size = 50 };
    if (clientDescNullParam.Value == null)
        clientDescNullParam.Value = DBNull.Value;

    var decimalValueParam = new SqlParameter { ParameterName = "@DecimalValue", SqlDbType = SqlDbType.Decimal, Direction = ParameterDirection.Input, Value = decimalValue.GetValueOrDefault(), Precision = 18, Scale = 0 };
    if (!decimalValue.HasValue)
        decimalValueParam.Value = DBNull.Value;

    var decimalValueNullParam = new SqlParameter { ParameterName = "@DecimalValueNull", SqlDbType = SqlDbType.Decimal, Direction = ParameterDirection.Input, Value = decimalValueNull.GetValueOrDefault(), Precision = 18, Scale = 0 };
    if (!decimalValueNull.HasValue)
        decimalValueNullParam.Value = DBNull.Value;

    var moneyParam = new SqlParameter { ParameterName = "@Money", SqlDbType = SqlDbType.Money, Direction = ParameterDirection.Input, Value = money.GetValueOrDefault(), Precision = 19, Scale = 4 };
    if (!money.HasValue)
        moneyParam.Value = DBNull.Value;

    var moneyNullParam = new SqlParameter { ParameterName = "@MoneyNull", SqlDbType = SqlDbType.Money, Direction = ParameterDirection.Input, Value = moneyNull.GetValueOrDefault(), Precision = 19, Scale = 4 };
    if (!moneyNull.HasValue)
        moneyNullParam.Value = DBNull.Value;

    var smallMoneyParam = new SqlParameter { ParameterName = "@SmallMoney", SqlDbType = SqlDbType.SmallMoney, Direction = ParameterDirection.Input, Value = smallMoney.GetValueOrDefault(), Precision = 10, Scale = 4 };
    if (!smallMoney.HasValue)
        smallMoneyParam.Value = DBNull.Value;

    var smallMoneyNullParam = new SqlParameter { ParameterName = "@SmallMoneyNull", SqlDbType = SqlDbType.SmallMoney, Direction = ParameterDirection.Input, Value = smallMoneyNull.GetValueOrDefault(), Precision = 10, Scale = 4 };
    if (!smallMoneyNull.HasValue)
        smallMoneyNullParam.Value = DBNull.Value;

    var realValueParam = new SqlParameter { ParameterName = "@RealValue", SqlDbType = SqlDbType.Real, Direction = ParameterDirection.Input, Value = realValue.GetValueOrDefault(), Precision = 24, Scale = 0 };
    if (!realValue.HasValue)
        realValueParam.Value = DBNull.Value;

    var realValueNullParam = new SqlParameter { ParameterName = "@RealValueNull", SqlDbType = SqlDbType.Real, Direction = ParameterDirection.Input, Value = realValueNull.GetValueOrDefault(), Precision = 24, Scale = 0 };
    if (!realValueNull.HasValue)
        realValueNullParam.Value = DBNull.Value;

    var floatValueParam = new SqlParameter { ParameterName = "@FloatValue", SqlDbType = SqlDbType.Float, Direction = ParameterDirection.Input, Value = floatValue.GetValueOrDefault(), Precision = 53, Scale = 0 };
    if (!floatValue.HasValue)
        floatValueParam.Value = DBNull.Value;

    var floatValueNullParam = new SqlParameter { ParameterName = "@FloatValueNull", SqlDbType = SqlDbType.Float, Direction = ParameterDirection.Input, Value = floatValueNull.GetValueOrDefault(), Precision = 53, Scale = 0 };
    if (!floatValueNull.HasValue)
        floatValueNullParam.Value = DBNull.Value;

    var procResultParam = new SqlParameter { ParameterName = "@procResult", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Output };
    const string sqlCommand = "EXEC @procResult = [dbo].[StoredProcWithDefaults] @UserId, @UserIdNull, @ClientName, @ClientNameNull, @ClientNameMaxNull, @ClientDesc, @ClientDescNull, @DecimalValue, @DecimalValueNull, @Money, @MoneyNull, @SmallMoney, @SmallMoneyNull, @RealValue, @RealValueNull, @FloatValue, @FloatValueNull";
    var procResultData = Set<StoredProcWithDefaultsReturnModel>()
        .FromSqlRaw(sqlCommand, userIdParam, userIdNullParam, clientNameParam, clientNameNullParam, clientNameMaxNullParam, clientDescParam, clientDescNullParam, decimalValueParam, decimalValueNullParam, moneyParam, moneyNullParam, smallMoneyParam, smallMoneyNullParam, realValueParam, realValueNullParam, floatValueParam, floatValueNullParam, procResultParam)
        .ToList();

    procResult = (int) procResultParam.Value;
    return procResultData;
}

public async Task<List<StoredProcWithDefaultsReturnModel>> StoredProcWithDefaultsAsync(
    int? userId, int? userIdNull,
    string clientName, string clientNameNull, string clientNameMaxNull,
    string clientDesc, string clientDescNull,
    decimal? decimalValue = null, decimal? decimalValueNull = null,
    decimal? money = null, decimal? moneyNull = null,
    decimal? smallMoney = null, decimal? smallMoneyNull = null,
    float? realValue = null, float? realValueNull = null,
    double? floatValue = null, double? floatValueNull = null,
    CancellationToken cancellationToken = default(CancellationToken))
{
    var userIdParam = new SqlParameter { ParameterName = "@UserId", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Input, Value = userId.GetValueOrDefault(), Precision = 10, Scale = 0 };
    if (!userId.HasValue)
        userIdParam.Value = DBNull.Value;

    var userIdNullParam = new SqlParameter { ParameterName = "@UserIdNull", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Input, Value = userIdNull.GetValueOrDefault(), Precision = 10, Scale = 0 };
    if (!userIdNull.HasValue)
        userIdNullParam.Value = DBNull.Value;

    var clientNameParam = new SqlParameter { ParameterName = "@ClientName", SqlDbType = SqlDbType.NVarChar, Direction = ParameterDirection.Input, Value = clientName, Size = 50 };
    if (clientNameParam.Value == null)
        clientNameParam.Value = DBNull.Value;

    var clientNameNullParam = new SqlParameter { ParameterName = "@ClientNameNull", SqlDbType = SqlDbType.NVarChar, Direction = ParameterDirection.Input, Value = clientNameNull, Size = 50 };
    if (clientNameNullParam.Value == null)
        clientNameNullParam.Value = DBNull.Value;

    var clientNameMaxNullParam = new SqlParameter { ParameterName = "@ClientNameMaxNull", SqlDbType = SqlDbType.NVarChar, Direction = ParameterDirection.Input, Value = clientNameMaxNull, Size = -1 };
    if (clientNameMaxNullParam.Value == null)
        clientNameMaxNullParam.Value = DBNull.Value;

    var clientDescParam = new SqlParameter { ParameterName = "@ClientDesc", SqlDbType = SqlDbType.VarChar, Direction = ParameterDirection.Input, Value = clientDesc, Size = 50 };
    if (clientDescParam.Value == null)
        clientDescParam.Value = DBNull.Value;

    var clientDescNullParam = new SqlParameter { ParameterName = "@ClientDescNull", SqlDbType = SqlDbType.VarChar, Direction = ParameterDirection.Input, Value = clientDescNull, Size = 50 };
    if (clientDescNullParam.Value == null)
        clientDescNullParam.Value = DBNull.Value;

    var decimalValueParam = new SqlParameter { ParameterName = "@DecimalValue", SqlDbType = SqlDbType.Decimal, Direction = ParameterDirection.Input, Value = decimalValue.GetValueOrDefault(), Precision = 18, Scale = 0 };
    if (!decimalValue.HasValue)
        decimalValueParam.Value = DBNull.Value;

    var decimalValueNullParam = new SqlParameter { ParameterName = "@DecimalValueNull", SqlDbType = SqlDbType.Decimal, Direction = ParameterDirection.Input, Value = decimalValueNull.GetValueOrDefault(), Precision = 18, Scale = 0 };
    if (!decimalValueNull.HasValue)
        decimalValueNullParam.Value = DBNull.Value;

    var moneyParam = new SqlParameter { ParameterName = "@Money", SqlDbType = SqlDbType.Money, Direction = ParameterDirection.Input, Value = money.GetValueOrDefault(), Precision = 19, Scale = 4 };
    if (!money.HasValue)
        moneyParam.Value = DBNull.Value;

    var moneyNullParam = new SqlParameter { ParameterName = "@MoneyNull", SqlDbType = SqlDbType.Money, Direction = ParameterDirection.Input, Value = moneyNull.GetValueOrDefault(), Precision = 19, Scale = 4 };
    if (!moneyNull.HasValue)
        moneyNullParam.Value = DBNull.Value;

    var smallMoneyParam = new SqlParameter { ParameterName = "@SmallMoney", SqlDbType = SqlDbType.SmallMoney, Direction = ParameterDirection.Input, Value = smallMoney.GetValueOrDefault(), Precision = 10, Scale = 4 };
    if (!smallMoney.HasValue)
        smallMoneyParam.Value = DBNull.Value;

    var smallMoneyNullParam = new SqlParameter { ParameterName = "@SmallMoneyNull", SqlDbType = SqlDbType.SmallMoney, Direction = ParameterDirection.Input, Value = smallMoneyNull.GetValueOrDefault(), Precision = 10, Scale = 4 };
    if (!smallMoneyNull.HasValue)
        smallMoneyNullParam.Value = DBNull.Value;

    var realValueParam = new SqlParameter { ParameterName = "@RealValue", SqlDbType = SqlDbType.Real, Direction = ParameterDirection.Input, Value = realValue.GetValueOrDefault(), Precision = 24, Scale = 0 };
    if (!realValue.HasValue)
        realValueParam.Value = DBNull.Value;

    var realValueNullParam = new SqlParameter { ParameterName = "@RealValueNull", SqlDbType = SqlDbType.Real, Direction = ParameterDirection.Input, Value = realValueNull.GetValueOrDefault(), Precision = 24, Scale = 0 };
    if (!realValueNull.HasValue)
        realValueNullParam.Value = DBNull.Value;

    var floatValueParam = new SqlParameter { ParameterName = "@FloatValue", SqlDbType = SqlDbType.Float, Direction = ParameterDirection.Input, Value = floatValue.GetValueOrDefault(), Precision = 53, Scale = 0 };
    if (!floatValue.HasValue)
        floatValueParam.Value = DBNull.Value;

    var floatValueNullParam = new SqlParameter { ParameterName = "@FloatValueNull", SqlDbType = SqlDbType.Float, Direction = ParameterDirection.Input, Value = floatValueNull.GetValueOrDefault(), Precision = 53, Scale = 0 };
    if (!floatValueNull.HasValue)
        floatValueNullParam.Value = DBNull.Value;

    const string sqlCommand = "EXEC [dbo].[StoredProcWithDefaults] @UserId, @UserIdNull, @ClientName, @ClientNameNull, @ClientNameMaxNull, @ClientDesc, @ClientDescNull, @DecimalValue, @DecimalValueNull, @Money, @MoneyNull, @SmallMoney, @SmallMoneyNull, @RealValue, @RealValueNull, @FloatValue, @FloatValueNull";
    var procResultData = await Set<StoredProcWithDefaultsReturnModel>()
        .FromSqlRaw(sqlCommand, userIdParam, userIdNullParam, clientNameParam, clientNameNullParam, clientNameMaxNullParam, clientDescParam, clientDescNullParam, decimalValueParam, decimalValueNullParam, moneyParam, moneyNullParam, smallMoneyParam, smallMoneyNullParam, realValueParam, realValueNullParam, floatValueParam, floatValueNullParam)
        .ToListAsync(cancellationToken);

    return procResultData;
}

sjh37 avatar Mar 24 '25 12:03 sjh37