Dapper.Query<KeyValueType<T1,T2>> Return a collection of Nulls

Simple Issue (EDITED: To show easy reproducible example first, then the detailed scenario)

Having the following Classes:

public class SalesMetrics {
   public decimal SalesDollars { get; set; }
   public decimal SalesUnits { get; set; }
}

public class ProductGroup {
   public string ProductId { get; set; }
   public string ProductName { get; set; }
}

using the Following Dapper Query, my result equals [{Key = null, Value = null}]:

IEnumerable<KeyValuePair<ProductGroup, SalesMetrics>> result = sqlConnection
   .Query<KeyValuePair<ProductGroup, SalesMetrics>>(
       sql: @"SELECT 
                  1 As ProductId,
                  'Test' AS ProductName, 
                  1.00 As SalesDollars, 
                  1 As SalesUnits");

I'm wondering If Dapper could handle a KeyValuePair as output type, is so : how the query need to be?

Full Scenario (Why I'm needing this)

I am creating a Sales Query builder function that can group my sales results by different grouping predicate and should return different result type based on that predicate type.

I am using Dapper nuget package to get my result from SQL-Server.I am using Dapper.Query<T>() extension method on IDbConnection

Basically, no matter the grouping type I want to return a sum of SalesDollars & SalesUnits. For this part of the output, I've created the following class SalesMetrics

I want my Sales Query function to accept the Group class (ProductGroup, or any other class ...) as generic parameter named TGroup, The function should return a Collection of KeyValuePair<TGroup,SalesMetric>

Data Source

Here is the layout of my sales Table FlatSales

CREATE TABLE dbo.FlatSales (
   SalesDate DATE NOT NULL,
   ProductId INT NOT NULL,
   ProductName VARCHAR(100) NOT NULL,
   ProductCategoryId INT NOT NULL,
   ProductCategoryName VARCHAR(100) NOT NULL,
   CustomerGroupId INT NOT NULL,
   CustomerGroupName VARCHAR(100) NOT NULL,
   CustomerId INT NOT NULL,
   CustomerName VARCHAR(100) NOT NULL,
   SalesUnits INT NOT NULL,
   SalesDollars INT NOT NULL
)

Where I'm having an Issue

I have the following function for querying the DB.

public static IEnumerable<KeyValuePair<TGroup,SalesMetrics>> SalesTotalsCompute<TGroup>(System.Data.IDbConnection connection)
{
    string[] groupByColumnNames = typeof(TGroup)
        .GetProperties(bindingAttr: System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance)
        .Select(x => x.Name)
        .ToArray();
    string joinedGroupByColumnsNames = string.Join(",", groupByColumnNames);

    return connection.Query<KeyValuePair<TGroup, SalesMetrics>>(sql: $@"
        SELECT SUM(SalesDollars) AS SalesDollars,
            SUM(SalesUnits) AS SalesUnits,
            {joinedGroupByColumnsNames}
        FROM dbo.FlatSales
        GROUP BY {joinedGroupByColumnsNames}
    ");
}

Collection Of NULL

The code does not fail but it return a list of KeyValuePair that both Key and Value are NULL.

I've tried to Alias my columns like ProductName as [Key.ProductName] but then It does not change anything (Not failing also) ...

Generated Sql queries for ProductGroup are as follow (both returns empty KeyValuePair):

        SELECT SUM(SalesDollars) AS SalesDollars,
            SUM(SalesUnits) AS SalesUnits,
            ProductId,ProductName
        FROM dbo.FlatSales
        GROUP BY ProductId,ProductName

OR
        SELECT SUM(SalesDollars) AS SalesDollars as [Value.SalesDollars],
            SUM(SalesUnits) AS SalesUnits as [Value.SalesUnits],
            ProductId As [Key.ProductId],ProductName As [Key.ProductName]
        FROM dbo.FlatSales
        GROUP BY ProductId,ProductName

Any Ideas?

Solution

I doubt Dapper supports complex objects like that out-of-the-box.

Perhaps you can benefit from Dapper's multi-mapping feature:

public static IEnumerable<KeyValuePair<TGroup, SalesMetrics>> SalesTotalsCompute<TGroup>(System.Data.IDbConnection connection)
{
    string joinedGroupByColumnsNames = string.Join(",", GetCachedColumnNamesFor<TGroup>());

    return connection.Query<TGroup, SalesMetrics, KeyValuePair<TGroup, SalesMetrics>>(
        sql: $@"SELECT {joinedGroupByColumnsNames},
                       SUM(SalesDollars) AS SalesDollars,
                       SUM(SalesUnits) AS SalesUnits
                  FROM dbo.FlatSales
              GROUP BY {joinedGroupByColumnsNames}",
        map: (groupData, salesMetricsData) => new KeyValuePair<TGroup, SalesMetrics>(groupData, salesMetricsData),
        splitOn: "SalesDollars");
}

Remarks

I've reordered the columns, because splitOn needs the name of the column where the two objects split up, otherwise you'd have to pass the first item from the joinedGroupByColumnsNames-array which is a bit more random
If you're on .NET Standard, consider returning ValueTuple's instead of KeyValuePair's
Don't use reflection for every call, I suggest to add a method GetCachedColumnNamesFor that does the reflection only once, using a static ConcurrentDictionary, calling the ConcurrentDictionary.GetOrAdd method.

Other approach

You could also let ProductGroup inherit from SalesMetrics (or make an ISalesMetrics interface and let ProductGroup implement that interface) and do Query<ProductGroup>(...). An additional benefit would be that duplicate fields in both models would be blocked by the compiler.

The resulting method would then look like this:

public static IEnumerable<TSalesData> SalesTotalsCompute<TSalesData>(System.Data.IDbConnection connection)
    where TSalesData : ISalesMetric
{
    string joinedGroupByColumnsNames = string.Join(",", GetCachedNonSalesMetricColumnNamesFor<TSalesData>());

    return connection.Query<TSalesData>(sql: $@"
        SELECT SUM(SalesDollars) AS SalesDollars,
            SUM(SalesUnits) AS SalesUnits,
            {joinedGroupByColumnsNames}
        FROM dbo.FlatSales
        GROUP BY {joinedGroupByColumnsNames}
    ");
}

Here the GetCachedNonSalesMetricColumnNamesFor-method reflects the properties from TSalesData excluding those from the ISalesMetric interface, again, caching the result.