DBNull/nullable parameters not supported with FromSqlInterpolated

[zebslc]

I have a sql function that accepts null values as parameters. Unfortunately the test always fails with specified method is not supported.

Taking out the null values completely allows the sql test to run so it is just a null issue. I have tried changing it to just NULL but that has the same issue and won't work anyway with the interpolated query as it requires dbnull.

In order to help track this down I have put together a rough pseudo copy of the code we are using with all identifiable info taken out.

We are running the latest version 2.2.0 in a .netCore3 function app with NSubstitute 4.2.1.
Let me know if you need any more help with reproducing this.

public class ClassThatShouldSendNulls
{
    private readonly MyDataContext _context;

    public ClassThatShouldSendNulls(MyDataContext context)
    {
        _context = context;
    }
    
    private MyQueryResult SendQueryWithNulls(DateTime? checkDate, int numberToNull)
    {
        var dateParam = checkDate != null ? (IConvertible) checkDate : DBNull.Value;
        var intParam = numberToNull > 0 ? (IConvertible) numberToNull : DBNull.Value;
        return _context.Set<MyQueryResult>()
            .FromSqlInterpolated(
                $"SELECT * FROM [SqlFunctionWithNullableParameters]({dateParam}, {intParam})") 
            .Single();
    }
}

using NSubstitute;
using Xunit;
public class TestTheClassThatShouldSendNulls{

    private void MockDatabaseSendQueryWithNulls(MyDataContext context, bool checkDate, DateTime? checkDate, int numberToNull)
    {
        context.Set<MyQueryResult>().AddFromSqlInterpolatedResult($"SELECT * FROM [SqlFunctionWithNullableParameters]({(checkDate != null ? (IConvertible) checkDate : DBNull.Value)}, {(numberToNull > 0 ? (IConvertible) numberToNull : DBNull.Value)})", new[]
                {new MyQueryResult {Outcome = "an outcome"}});
    }

    [Fact]
    public async Task ShouldSendANullValueToTheDbFunction()  {
        var context = Create.MockedDbContextFor<MyDataContext>(
            new DbContextOptionsBuilder<MyDataContext>()
                .UseInMemoryDatabase(databaseName: Guid.NewGuid().ToString()));
        MockDatabaseSendQueryWithNulls(context, null, 0);         
        
        var testClass = new ClassThatShouldSendNulls(context);

        var results = testClass.SendQueryWithNulls(null, 0);
        results.Should().BeOfType<MyQueryResult>();
    }
}

/*
System.NotSupportedException : Specified method is not supported.
   at NSubstitute.ExceptionExtensions.ExceptionExtensions.<>c__DisplayClass2_0.<Throws>b__0(CallInfo ci)
   at NSubstitute.Core.ReturnValueFromFunc`1.ReturnFor(CallInfo info)
   at NSubstitute.Core.CallResults.ResultForCallSpec.GetResult(ICall call, ICallInfoFactory callInfoFactory)
   at NSubstitute.Core.CallResults.TryGetResult(ICall call, Object& result)
   at NSubstitute.Routing.Handlers.ReturnConfiguredResultHandler.Handle(ICall call)
   at NSubstitute.Routing.Route.Handle(ICall call)
   at NSubstitute.Core.CallRouter.Route(ICall call)
   at NSubstitute.Proxies.CastleDynamicProxy.CastleForwardingInterceptor.Intercept(IInvocation invocation)
   at Castle.DynamicProxy.AbstractInvocation.Proceed()
   at NSubstitute.Proxies.CastleDynamicProxy.ProxyIdInterceptor.Intercept(IInvocation invocation)
   at Castle.DynamicProxy.AbstractInvocation.Proceed()
   at Castle.Proxies.AsyncQueryProvider`1Proxy_54.CreateQuery[TElement](Expression expression)
   at Microsoft.EntityFrameworkCore.RelationalQueryableExtensions.FromSqlInterpolated[TEntity](DbSet`1 source, FormattableString sql)

*/

[rgvlee]

Hey zebslc, thanks for the detail, I believe I have been able to reproduce the issue. It looks like there is an issue with the logic that I'm using to compare the setup and invocation parameters. As you've alluded to it's the null that's causing the issue, I'm using a dictionary to capture matches and that's barfing on a null key.

I think I can improve it as a whole, it probably doesn't need to use such a construct. If you're happy to wait a few days for a release I think it'll be a better result.

[zebslc]

That's brilliant thank you. :-)

[rgvlee]

I have deployed a patch release with a fix for this issue (2.2.1 for EFCore3, 1.1.1 for EFCore2). Please let me know if this resolves your issue.

[zebslc]

Thank you! That was a lot quicker than expected. Love that my example made it into the test :-D
Will un-refactor my now virtual method back to where it was.