SqlServer: Cannot use `getId` against a `UNIQUEIDENTIFIER` column

[MangelMaxime]

I have the following table

CREATE TABLE [dbo].[SqlHydraRepro]
(
    [Id] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT NEWID(),
    -- descriptive data
    [EmailAddress] NCHAR(50) NOT NULL
)
GO

If I try to insert a new value with auto generation of the Id column I get an error:

insertAsync (Create DbContext.create) {
    for user in Tables.sqlHydraRepro do

        entity
            {
                dbo.SqlHydraRepro.Id = Guid.NewGuid()
                dbo.SqlHydraRepro.EmailAddress =
                    "requestValues.EmailAddress"
            }

        getId user.Id
}

Result:

System.InvalidCastException: Invalid cast from 'System.DBNull' to 'System.Guid'.
   at System.Convert.DefaultToType(IConvertible value, Type targetType, IFormatProvider provider)
   at System.DBNull.System.IConvertible.ToType(Type type, IFormatProvider provider)
   at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
   at <StartupCode$SqlHydra-Query>.$QueryContext.InsertAsyncWithOptions@247-6.Invoke(Object identity)
   at Microsoft.FSharp.Control.AsyncPrimitives.CallThenInvokeNoHijackCheck[a,b](AsyncActivation`1 ctxt, b result1, FSharpFunc`2 userCode) in D:\a\_work\1\s\src\fsharp\FSharp.Core\async.fs:line 465
   at Microsoft.FSharp.Control.Trampoline.Execute(FSharpFunc`2 firstAction) in D:\a\_work\1\s\src\fsharp\FSharp.Core\async.fs:line 104

[JordanMarr]

It seems strange that ExecuteScalarAsync is returning a DBNull.Value. Shouldn't it always return a value?

[JordanMarr]

It appears that in SQL Server, uniqueidentifier ids are returned differently than int ids, (using an OUTPUT clause in the query). So this will require a patch to the library.

[JordanMarr]

FYI, I have added your repro case to the mssql script and have reproduced the fail scenario. So it should be resolved quickly.

[MangelMaxime]

Thank you for the quick update

[JordanMarr]

Released v1.0.4: https://github.com/JordanMarr/SqlHydra/releases/tag/issue38-fix

[MangelMaxime]

Thank you