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.

Note: Dapper isn't going to do anything interesting with that `Values` property; when you say that there are 16 rows; is it possible that there's actually 4 rows, and you're talking about the `.Values` inner explosion? — Marc Gravell, Aug 06 '20 at 13:29

Marc Gravell · Accepted Answer · 2020-08-06T13:32:50.257

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

you've found a niche edge case that hasn't been seen in the many years of Dapper
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)

score 0 · Answer 2 · answered Aug 07 '20 at 07:27

0

This is what the request gives in the DbForge client

And this is what Dapper returns:

at that

answered Aug 07 '20 at 07:27

Lendim

13
3

Dapper only returns Null values

2 Answers2