Dapper only returns Null values

Question

I am facing a problem using Dapper. I have two models:

public class ClientEventsModel
{
    public int Id { get; set; }
    public int ClientId { get; set; }
    public ClientEventTypeLog EventType {get; set;}
    public int Value { get; set; }
    public DateTime Date { get; set; }
    public string? Doer { get; set; }
}

[Serializable]
public class ExtentedClientEventsModel : ClientEventsModel
{
    public List<string> Values { get; set; } = new List<string>();

}

One is inherited from the other. And a request in the form:

var sqlStr = $@"SELECT ce.Id, ce.ClientId, ce.EventType, ce.Value, ce.Date, a.UserName AS Doer, cevn.`Values` FROM clients.client_events ce 
                                    LEFT JOIN `z-price`.aspnetusers a ON ce.Doer = a.Id_num 
                                        LEFT JOIN clients.clients_events_value_name cevn ON ce.Id = cevn.ClientEventsId 
                                where ClientId = {clientId} and Date BETWEEN '{from.ToMysql()}' and '{to.AddDays(1).ToMysql()}'";
                var result = DefaultConnection.Query<ExtentedClientEventsModel>(sqlStr);

When I execute the query in the client it returns 16 records. But when I use Dapper it returns 4 records. And only those with the Doer == null field. I tried replacing the model with a dynamic type, but it didn't help.

Answer 1

Dapper doesn't get to invent the number of rows coming back. If Dapper says there were 4 rows, then one of two things:

1. you've found a niche edge case that hasn't been seen in the many years of Dapper
2. your query really does only return 4 rows

Now, I'm open to "1", but in reality "2" is much more likely. I suspect the real problem here is the parameters - or lack there-of. Never ever use concatenation to build SQL from values. Try instead:

const string sqlStr = @"
SELECT ce.Id, ce.ClientId, ce.EventType, ce.Value, ce.Date, a.UserName AS Doer, cevn.`Values` FROM clients.client_events ce 
LEFT JOIN `z-price`.aspnetusers a ON ce.Doer = a.Id_num 
LEFT JOIN clients.clients_events_value_name cevn ON ce.Id = cevn.ClientEventsId 
where ClientId = @clientId and Date BETWEEN @from and @to";

var result = DefaultConnection.Query<ExtentedClientEventsModel>(
    sqlStr, new { clientId, from, to = to.AddDays(1) }).AsList();

(note: different databases have different syntax for parameters; if @clientId etc doesn't work, try :clientId, $clientId, etc)