Deserialize Dapper Query into Model

Question

I have two models:

 public class RuleValidation
{
    public int RuleId { get; set; }
    public string RuleName { get; set; }
    public int ClientId { get; set; }
    public string ClientName { get; set; }
    public List<RuleException> RuleExcpetions { get; set; }
}

and:

 public class RuleException
{
    public int ExceptionId { get; set; }
    public int RuleId { get; set; }
    public string ExceptionValue { get; set; }
    public string ExcpetionResult { get; set; }
}

I am using the following query to load up the models:

 public async Task<RuleValidation> GetRulesByClient(string clientId)
    {
        using (IDbConnection conn = Connection)
        {
            string sQuery = @"SELECT 
                                    Client.Id as 'ClientName',
                                    Client.ClientName, 
                                    Rules.Id,
                                    Rules.RuleName,
                                    Rules.Code, 
                                    RuleException.ExceptionValue, 
                                    RuleException.ExceptionResult
                              FROM 
                                    Rules 
                              LEFT OUTER JOIN
                                    RuleException ON Rules.Id = RuleException.RuleId 
                              RIGHT OUTER JOIN
                                    Client ON Rules.Id = Client.RuleId
                              WHERE
                                    Client.ClientName = @CLIENTID";
            conn.Open();
            var result = await conn.QueryAsync<RuleValidation>(sQuery, new { CLIENTID = clientId });
            return (RuleValidation)result;
        }
    }

This seems to deserialize the query into a RuleValidation Object, but the RuleException object is not loading for those records that have exceptions. So I tried to modify the return like this:

 var return= conn.QueryAsync<RuleValidation, RuleException>(
                sQuery,
                (ruleValidation, ruleException) =>
                {
                    ruleValidion.RuleException = ruleException;
                    return return;
                },
                splitOn: "RuleId");

But I get an error:

IDBCOnnection does not contain a definition for QueryAsync and no accessible extension method accepting a first argument of type 'IDBConnection' could not be found

Here is how I am setting up my connection (it does work for the initial query)

 public IDbConnection Connection
    {
        get
        {
            return new SqlConnection(_configuration.GetConnectionString("ValidationRulesConnectionString"));
        }
    }

I am new to dapper and have no problem using it, but this is the first problem I've had that I can't seem to find a solution for in the dapper tutorials or looking online.

Answer 1

You QueryAsync call is missing the return type. It takes 3 parameters - the two objects to map and then the return type. You also need to add the exception to the list:

var return= conn.QueryAsync<RuleValidation, RuleException, RuleValidation>(
            sQuery,
            (ruleValidation, ruleException) =>
            {
                if(ruleException != null)
                {
                    ruleValidation.RuleExceptions.Add(ruleException);
                }

                return ruleValidation;
            },
            splitOn: "RuleId");

And make sure your RuleValidation class initializes the list property

public class RuleValidation
{
    public int RuleId { get; set; }
    public string RuleName { get; set; }
    public int ClientId { get; set; }
    public string ClientName { get; set; }
    public List<RuleException> RuleExcpetions { get; set; } = new List<RuleException>();
}

There is an example here