V3 support for SQL Server input/output parameters

[mchallenjha]

Our project has a number of stored procs that declare an output parameter and pass a value in. The EF.Reverse.POCO.v3.ttinclude T4 template includes an enum StoredProcedureParameterMode { In, InOut, Out}, but InOut is not referenced. As a result, the application fails because the passed value is nullified when the procedure is called.

eg:

CREATE  PROCEDURE [config].[P_XXX]
    @groupId		INT OUTPUT, _-- a value is also passed in_
    @clientId		INT,
    @displayName	VARCHAR(30),

EF.Reverse.POCO.v3.ttinclude.txt

@description    VARCHAR(100),
@result			INT OUTPUT,
@failureReason	VARCHAR(4000) OUTPUT
AS

generated code:

public int P_XXX(out int? groupId, int? clientId, string displayName,
                 string description, out int? result, out string failureReason)
{
    var groupIdParam = new SqlParameter { ParameterName = "@groupId",
                 SqlDbType = SqlDbType.Int,
                 Direction = ParameterDirection.Output, 
                 Precision = 10,
                 Scale = 0 }; _// Direction should be InputOutput; missing Value = argument_

I modified the T4 (see attachment) to accommodate InputOutput parameters, which are now generated in code as "ref" instead of "out". This works, but I'd like to be able to override the stored proc parameter mode in my .tt file.

[sjh37]

Thanks for the detailed report and the patched .ttinclude - you correctly identified both the root cause and the fix.

Root cause

SQL Server's INFORMATION_SCHEMA.PARAMETERS view always reports PARAMETER_MODE = 'INOUT' for any OUTPUT parameter - it has no way to distinguish "pure OUT" from "IN OUT". The reader correctly mapped this to StoredProcedureParameterMode.InOut, but StoredProcedure.cs then had five code paths that only checked Mode == In vs "everything else", silently treating InOut as if it were Out. That produced:

• out int? groupId in the C# signature (caller's input value is unreachable)
• Direction = ParameterDirection.Output on the SqlParameter (SQL Server ignores any incoming value)
• No Value = groupId.GetValueOrDefault() assignment
• No if (!groupId.HasValue) ... = DBNull.Value null guard

Fix

Fixed in Generator/StoredProcedure.cs (five edits across WriteStoredProcFunctionParams, WriteStoredProcFunctionOverloadCall, and WriteStoredProcFunctionDeclareSqlParameter). For InOut parameters the generator now emits:

public int P_XXX(ref int? groupId, int? clientId, string displayName,
                 string description, ref int? result, ref string failureReason)
{
    var groupIdParam = new SqlParameter {
        ParameterName = "@groupId",
        SqlDbType = SqlDbType.Int,
        Direction = ParameterDirection.InputOutput,
        Value = groupId.GetValueOrDefault(),
        Precision = 10, Scale = 0 };
    if (!groupId.HasValue)
        groupIdParam.Value = DBNull.Value;
    ...
}

Added four unit tests covering the new behaviour, including one that reproduces your exact procedure signature. Updated the existing tests that had encoded the buggy out behaviour.

On overriding the mode in <database>.tt

You also asked for a way to override the mode per-parameter from your .tt file. Since SQL Server's metadata genuinely cannot tell us whether an OUTPUT parameter is "true InOut" or "OUT only", ref is the safe default - it is a strict superset of out semantics. If you would like an opt-out filter (e.g. mark a parameter as Out only, generating out instead of ref), please open a separate enhancement issue, and I will add it as a filter hook.

Note for users upgrading

This is a small breaking change for callers of generated stored-procedure methods that have OUTPUT parameters.

Release notes entry (breaking change)

Breaking change: OUTPUT parameters on stored procedures now generate ref instead of out (#868)

Previously, any SQL Server stored-procedure parameter declared with OUTPUT was generated as a C# out parameter with ParameterDirection.Output. This was incorrect: SQL Server's OUTPUT semantics are bidirectional, and any value the caller assigned to the C# argument was silently discarded before the procedure ran (Value = ... was never set on the SqlParameter).

The generator now emits:

• ref T instead of out T in the method signature and interface
• ParameterDirection.InputOutput instead of ParameterDirection.Output
• Value = argument.GetValueOrDefault() (so the caller's input value is actually sent)
• A DBNull.Value null guard, matching how input parameters are handled

Why this is breaking: SQL Server's metadata (INFORMATION_SCHEMA.PARAMETERS) cannot distinguish "pure OUT" from "IN OUT" parameters - all OUTPUT parameters are reported as INOUT. The generator, therefore, treats every OUTPUT parameter as bidirectional. ref is a strict superset of out, so this is the safe default, but it does change the call site.

Upgrade action for callers of generated SP methods:

// Before
int? groupId;                           // uninitialized is fine for `out`
ctx.P_XXX(out groupId, clientId, ...);

// After
int? groupId = null;                    // `ref` requires definite assignment
ctx.P_XXX(ref groupId, clientId, ...);  // pass the value in AND read it back

If you previously worked around this bug by manually editing the generated code, you can remove that workaround.

No action needed if your project does not call any stored-procedure methods that have OUTPUT parameters.

[sjh37]

Try the latest EF.Reverse.POCO.v3.ttincludefile in your project and let me know if this does not solve the problem and I can re-open the ticket.