MSSQL with ContextSchemaPath using Scalar Function loses return value

[TheJayMann]

Describe the bug When making use of context schema with MSSQL, scalar functions with a return value are assumed to return unit, rather than a type with a property ReturnValue containing the function's return value.

To Reproduce Steps to reproduce the behavior:

1. Create a database with a scalar function returning a value
2. Create an F# console app with SQLProvider
3. Create a SqlDataProvider type referencing the database with a scalar value
4. Add a call to the scalar function
5. Use the design time command to save the schema
6. Build the project with the saved schema

Expected behavior The project should build the same as if no schema file were present

Desktop (please complete the following information):

• OS: Windows
• Version: 10

Additional context TestFunction.sql

CREATE FUNCTION [dbo].[TestFunction] () RETURNS VARCHAR(MAX) AS BEGIN
    RETURN 'Test Value'
END

Program.fs

open FSharp.Data.Sql
let [<Literal>] ConnectionString = """Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=TestDB;Integrated Security=True;"""
let [<Literal>] SchemaPath = __SOURCE_DIRECTORY__ + """\Test.schema"""
type TestDB = SqlDataProvider<DatabaseVendor=Common.DatabaseProviderTypes.MSSQLSERVER,ConnectionString=ConnectionString, ContextSchemaPath=SchemaPath>

let db = TestDB.GetDataContext()

//db.``Design Time Commands``.SaveContextSchema

db.Functions.TestFunction.Invoke().ReturnValue |> printfn "%s"

Test.schema

{
    "Columns@": [],
    "Individuals@": [],
    "IsOffline@": true,
    "Packages@": [],
    "PrimaryKeys@": [],
    "Relationships@": [],
    "SprocsParams@": [
        {
            "Key": "DboTestFunction",
            "Value": {
                "head": {
                    "Direction@": 6,
                    "Length@": {
                        "_tag": 1,
                        "item": -1
                    },
                    "Name@": "",
                    "Ordinal@": 0,
                    "TypeMapping@": {
                        "ClrType@": "System.String",
                        "DbType@": 0,
                        "ProviderTypeName@": {
                            "_tag": 1,
                            "item": "varchar"
                        },
                        "ProviderType@": {
                            "_tag": 1,
                            "item": 22
                        }
                    }
                },
                "tail": {
                    "head": {
                        "Direction@": 6,
                        "Length@": {
                            "_tag": 1,
                            "item": -1
                        },
                        "Name@": "ReturnValue",
                        "Ordinal@": 0,
                        "TypeMapping@": {
                            "ClrType@": "System.String",
                            "DbType@": 0,
                            "ProviderTypeName@": {
                                "_tag": 1,
                                "item": "varchar"
                            },
                            "ProviderType@": {
                                "_tag": 1,
                                "item": 22
                            }
                        }
                    },
                    "tail": {
                        "head": {
                            "Direction@": 0,
                            "Length@": {
                                "_tag": 0,
                                "item": 0
                            },
                            "Name@": null,
                            "Ordinal@": 0,
                            "TypeMapping@": {
                                "ClrType@": null,
                                "DbType@": 0,
                                "ProviderTypeName@": {
                                    "_tag": 0,
                                    "item": null
                                },
                                "ProviderType@": {
                                    "_tag": 0,
                                    "item": 0
                                }
                            }
                        },
                        "tail": null
                    }
                }
            }
        }
    ],
    "Sprocs@": [
        {
            "__type": "Sproc.Root:#FSharp.Data.Sql.Schema",
            "_tag": 0,
            "item1": "Functions",
            "item2": {
                "__type": "Sproc.Sproc:#FSharp.Data.Sql.Schema",
                "_tag": 2,
                "item": {
                    "Name@": {
                        "Owner@": "TestDB",
                        "PackageName@": "dbo",
                        "ProcName@": "TestFunction"
                    }
                }
            }
        }
    ],
    "Tables@": []
}

Error message

Program.fs(10,36): error FS0039: The type 'Unit' does not define the field, constructor or member 'ReturnValue'.

[TheJayMann]

I believe I have an idea as to why this is happening.

First, looking at .NET source, https://source.dot.net/#System.Data.Common/System/Data/ParameterDirection.cs, the values for Input, Output, InputOutput, and Return value are 1, 2, 3, and 6. In the serialized schema definition created by MSSQL provider, the direction is 6, or ReturnValue. In the getSprocReturnColumns function, if a connection is present, a list of return columns are generated from the provider, and added to the schema cache, and returned. However, without a connection, the list of columns saved to the schema cache is filtered based on the direction being Input. As the return values are given a direction of ReturnValue by the MSSQL provider, these get filtered out, and an empty list is returned instead, causing the return valuess to be lost. https://github.com/fsprojects/SQLProvider/blob/1fe76d3b7ddc81bb39a830e340a0f20d5a6a6cd7/src/SQLProvider.DesignTime/SqlDesignTime.fs#L126-L136

In further testing, I manually modified the schema cache file to be 2 instead of 6, I received an error that the type provider was attempting to create a member without a name. This is likely due to the MSSQL provider creating the return value parameter twice, one without a name, and one with the name ReturnValue. When I changed the direction of the unnamed column back to 6 (as that was easier than trying to remove the first entry of the linked list), then everything compiles fine using the context schema file.

I believe a fix for this would be that either MSSQL provider (and possibly other providers, though I have not checked) change the direction of the query parameter to be output so that it can be picked up by the getSprocReturnColumns function, or the getSprocReturnColumns function needs to allow directions other than just Output, such as InputOutput and ReturnValue, as it appears the MSSQL provider only provides parameters of these directions when it generates a list of return values and parameters. https://github.com/fsprojects/SQLProvider/blob/1fe76d3b7ddc81bb39a830e340a0f20d5a6a6cd7/src/SQLProvider.Runtime/Providers.MsSqlServer.fs#L187-L188

Also, either the MSSQL provider should not create parameters for the return value twice, one without a name, and the other with the name ReturnValue, or the getSprocReturnColumns should filter out any parameter without a name, since a member cannot be created without a name.