Getting error 'Insight.Database.FastExpando' does not contain a definition for 'Set1' from QueryResults<>()

[gshultz21]

The following code is giving the above error, and I cannot figure out why:

var x = _sqlConn.Connection().QueryResults<Results>("MyDb.dbo.get_records", new { id = theId }); int retVal = x.Outputs.Return_Value;

if (retVal == 0) // ...meaning result set was also returned...fine to this point. { var list = x.Outputs.Set1; // exception thrown here with above error var temp = list.FirstOrDefault();

I have been using other features of Insight.Database for a number of years, but have not had to retrieve a SQL RETURN value at the same time as a recordset. The SQL itself works correctly in SSMS, returning a result set and the RETURN value of 0, as expected. This is happening in VS2019, .NET 4 and .NET 4.5.2; Insight.Database 5.2.7 and 5.2.8, and SQL Server 2016.

I got this code from the following page: https://github.com/jonwagner/Insight.Database/wiki/Specifying-Result-Structures where it shows this:

var results = connection.QueryResults<Beer, Glass>("GetAllBeersAndAllGlasses"); IList<Beer> beers = results.Set1;

which I combined with the following code from here: https://github.com/jonwagner/Insight.Database/wiki/Output-Parameters

var results = connection.QueryResults<Results>("MyProc", inputParameters); var p = results.Outputs.p;

That part works. It's accessing .Set1 that is failing. Over at SO, Jon suggested that I try this:

var x = _sqlConn.Connection().QueryResults<MyType>("MyDb.dbo.get_records", new { id = theId });

but that fails to compile, with the following error:

The type 'MyType' cannot be used as type parameter 'T' in the generic type or method 'DBConnectionExtensions.QueryResults(IDbConnection, string, object, CommandType, CommandBehavior, int?, IDbTransaction, object)'. There is no implicit reference conversion from 'MyType' to 'Insight.Database.Results'.

[Jaxelr]

Here is a working sample, using the same QueryResults Api:

Given this Sql

CREATE OR ALTER PROC TestSelectReturn 
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    SELECT 1 Id, 'Name' Name

    RETURN 2;
END
GO

This code works as intended:

void Main()
{
    var connection = new SqlConnection(MyExtensions.SQLConnectionString);
    SqlInsightDbProvider.RegisterProvider();

    var result = connection.QueryResults<Sample, Return>("TestSelectReturn");

    if (result.Outputs.Return_Value == 2)
    {
        Console.WriteLine($"Returned value is: {result.Outputs.Return_Value}");

        Console.WriteLine(result.Set1.FirstOrDefault().Id);
        Console.WriteLine(result.Set1.FirstOrDefault().Name);
    }

}

public class Sample
{ 
    public int Id { get; set; }
    public string Name { get; set; }
}

public class Return
{ 
    public int Return_Value { get; set; }
}

Edit: forgot to add the Results\<T> option which should also work:

    var result2 = connection.QueryResults<Results<Sample>>("TestSelectReturn");

    if (result2.Outputs.Return_Value == 2)
    {
        Console.WriteLine($"Returned value is: {result2.Outputs.Return_Value}");

        Console.WriteLine(result2.Set1.FirstOrDefault().Id);
        Console.WriteLine(result2.Set1.FirstOrDefault().Name);
    }

I would also suggest you take a look at the following tests:

https://github.com/jonwagner/Insight.Database/blob/3caebb678db0ac5e02965ba00732b4526dbc9b36/Insight.Tests/OutputParameterTests.cs#L235-L257

https://github.com/jonwagner/Insight.Database/blob/3caebb678db0ac5e02965ba00732b4526dbc9b36/Insight.Tests/OutputParameterTests.cs#L190-L196

[gshultz21]

I will check those out. Thank you very much for the help.