Dapper isn't mapping correctly

[ianlee74]

We're about ready to rip Dapper out of everything... Everything worked great when we did the initial development but over the past year we have had numerous times when for no apparent reason it just starts incorrectly mapping SQL columns to the POCO properties. It seems to most often be when there is an "Id" column and another column that contains the word "Id" (i.e. "SurveyId"). It also seems to be related to the query having a JOIN despite that no columns from the join are SELECTed. I really hope someone can help us figure out what we're doing wrong because I'd hate to stop using Dapper but this unpredictable behavior is unacceptable. Here's the current example...

[DataContract]
public class ClientSurvey
{
 [DataMember(EmitDefaultValue = false)]
 public int Id { get; set; }

 [DataMember]
 public int ClientId { get; set; }

 [DataMember(EmitDefaultValue = false)]
 public int SurveyId { get; set; }

 [DataMember]
 public string Name { get; set; }

 [DataMember]
 public DateTime StartDate { get; set; }

 [DataMember]
 public DateTime? EndDate { get; set; }

 [DataMember]
 public bool IsPatientDefault { get; set; }

 [DataMember]
 public bool IsLocked { get; set; }

 [DataMember]
 public Survey Survey { get; set; }

 [DataMember]
 public string IntroHtml { get; set; }

 [DataMember]
 public string OutroHtml { get; set; }

 [DataMember]
 public int MaxResponses { get; set; }
}

...

public async Task<ClientSurvey> GetDefaultAsync(int clientId)
{
 // NOTE: This was actually "select cs.*" but has been spelled out for better understanding and to see if it would help with the mapping. It did not.
 const string sql =
 @"select cs.[Id], cs.[ClientId], cs.[SurveyId], cs.[Name], cs.[StartDate], cs.[EndDate], cs.[IsPatientDefault], cs.[ClientSurveyBundleId], cs.[IsLocked], cs.[IntroHtml], cs.[OutroHtml], cs.[MaxResponses], cs.[Description]
 from dbo.ClientSurveys cs
 inner join dbo.Surveys s on s.Id = cs.SurveyId
 where cs.ClientId = @clientId
 and cs.IsPatientDefault = 1
 and isnull(cs.EndDate, '9999-1-1') > getdate() 
 and isnull(cs.StartDate, '2000-1-1') < getdate() 
 and s.IsActive = 1;";

 using (var cnn = await OpenConnectionAsync())
 {
 var surveys = (await cnn.QueryAsync<ClientSurvey>(sql, new {ClientId = clientId})).ToList();
 if (!surveys.Any())
 {
 return null;
 }
 if (surveys.Count() > 1)
 {
 throw new Exception("Multiple default surveys for client " + clientId);
 }
 var clientSurvey = surveys.First();
 clientSurvey.Survey = await SurveysData.Instance.GetAsync(clientSurvey.SurveyId, clientSurvey.ClientId);
 clientSurvey.SurveyId = 0; // Clear this so it doesn't serialize out and duplicate the data.
 await ReplaceVariables(clientSurvey);
 return clientSurvey;
 }
}

If this SQL is called in SSMS, I get back (among other things...): Id = 3, SurveyId = 2

However, if I call the function above then it assigns: clientSurvey.Id = 2, clientSurvey.SurveyId = 2
(note that there is not a record in the database where ClientSurvey.Id = 2)

So far we've managed to do weird things like changing or removing JOINs in exchange for less desirable queries just to force Dapper to get the mapping correct but this now seems to be an almost daily problem. Is anyone else experiencing this?

Our databases are Azure Database (v12) and our code is running in an Azure Website running .NET 4.5.2.

Thanks for any help you can offer. This is an urgent problem for us.

Ian

[ianlee74]

FYI, the "fix" to make this particular example work again was to change the query to this...

select *
 from dbo.ClientSurveys cs
where cs.ClientId = @clientId
 and cs.IsPatientDefault = 1
 and isnull(cs.EndDate, '9999-1-1') > getdate() 
 and isnull(cs.StartDate, '2000-1-1') < getdate()
 and exists (select * from dbo.Surveys s where s.Id = cs.SurveyId and s.IsActive = 1);

[mgravell]

That is ... very odd. I have quite simply never seen the scenario you
describe. More: it actually does column order checks so that even if your
columns somehow changed mid-application (someone removing and re-adding a
column, for example), it should still work. I'm not doubting that you have
seen it do oddness, but... yeah, that's odd. And very hard to investigate
without a reliable minimal repro, since I've never seen anything like that
in multiple years.

I wonder if there isn't a simpler explanation for crazy behaviour - for
example, is it possible you have the new .NET 4.6 RyuJIT enabled?
On 6 Aug 2015 8:55 pm, "R. Ian Lee, Sr." notifications@github.com wrote:

FYI, the "fix" to make this particular example work again was to change
the query to this...

 const string sql =
 @"select *
 from dbo.ClientSurveys cs
 where cs.ClientId = @clientId
 and cs.IsPatientDefault = 1
 and isnull(cs.EndDate, '9999-1-1') > getdate()
 and isnull(cs.StartDate, '2000-1-1') < getdate()
 and exists (select * from dbo.Surveys s where s.Id = cs.SurveyId and s.IsActive = 1);";

—
Reply to this email directly or view it on GitHub
#326 (comment)
.

[ianlee74]

It's definitely doing it. I can swap those two queries back and forth at will to reproduce the problem.

I've confirmed that all the projects in the solution are using .NET 4.5.2 and not 4.6. For kicks, I tried adding the following to the web.config.

<useLegacyJit enabled="1" />

I am currently using VS2015 but this all started long before I had it or .NET 4.6 on this machine. In fact, it seems to have started about the time that 4.5.2 came out. I haven't tried going back to an earlier .NET version and I can't go up to 4.6 yet since Azure Websites doesn't yet support it. I'll try to temporarily change to 4.6 and see if that makes any improvements. If I have some time this evening, I'll try to create a new minimal app and database and try to reproduce there then post it somewhere you can test to see if it works for you.

[ianlee74]

Moving all the projects to .NET 4.6 didn't change anything.

[mgravell]

If it is one of the JIT bugs: it doesn't actually matter whether you target
4.5.* vs 4.6: once you install 4.6 the JIT affects all, and the config file
change doesn't work in all cases. There is a registry key that disables it

• it is worth a try - see the bottom of:
  http://nickcraver.com/blog/2015/07/27/why-you-should-wait-on-dotnet-46/

I will, however, review the code and see if I can dream up any other ways
this could happen.

On Thu, 6 Aug 2015 21:42 R. Ian Lee, Sr. notifications@github.com wrote:

Moving all the projects to .NET 4.6 didn't change anything.

—
Reply to this email directly or view it on GitHub
#326 (comment)
.

[ianlee74]

I'll try that but I really don't think that's the problem given that we see this problem even after deployment to our Azure Websites which do not have 4.6 available.

[ianlee74]

I've confirmed that adding the useLegacyJit registry entry did not change our problem.

[ianlee74]

I'm attempting to create a minimal project & database that you can use to reproduce the problem. Unfortunately, what our results so far have shown is that this problem is unique to some configuration of our database. Creating a new local database or even a new Azure Database instance so far has not been successful in reproducing the problem. Using the same app to connect to our database does cause the problem. I'm working through the various Azure-specific options we are using now to see if any of those are the root.

The test project is here:
https://github.com/ianlee74/DapperProblem

[ianlee74]

I have isolated this to a problem caused by the Azure Database - Dynamic Data Masking (preview) feature. I've so far been unable to reproduce it with a new database. So, it may be something Microsoft has already resolved but the problem still exists in databases that have had the feature for some time. This was never enabled on our production database which explains why it has not been effected. I'm going to continue to experiment but given this is a preview feature of Azure Database I suppose it isn't something Dapper needs to worry about just yet. If I am able to create steps to reproduce the problem, I'll post here for you to test Dapper against.

[ianlee74]

I've updated my test project with instructions and scripts that reproduces the problem 100% of the time in case you want to go ahead and take a look at this. I'll also be forwarding a ticket to Microsoft to be sure they are aware of the issue and will update here with any response I receive.

https://github.com/ianlee74/DapperProblem

Follow the instructions here to create an Azure Database that will reproduce the problem:
https://github.com/ianlee74/DapperProblem/blob/master/DapperProblem/CreateDatabase.sql

[ianlee74]

@mgravell - Update - I spent some time on the phone with the Azure Database product team yesterday discussing this. It appears there is definitely a bug on their end when DDM is enabled. It's either in the core .NET Framework code or in Azure Database. I'm leaning toward the .NET Framework since this problem does not appear when using SSMS to execute the same queries. All the code is updated in my repo with tests that clearly show these problems.

In short, the problem is that when DDM is enabled and there's a JOIN involved then the .NET SQL client (I only tested ExecuteReader()) is adding the Id column of the joined table to the end of the column list regardless of whether it was requested or not. You can see this in the test results below if you look at the "Native" calls. You will see that the first Id column (the correct one) is returned and then there is an extra Id column also returned at the end.

Unfortunately, it seems that Dapper is mapping to the last Id instead of the first. So, this may be a problem in Dapper, also, since I would expect Dapper to map to the first Id rather than the last. In the last test you can see that I also duplicated this issue with Dapper by eliminating the JOIN and manually adding a second Id column to the end of the SELECT. This may be a known issue with Dapper. I haven't investigated but the mapping strategy seems wrong.

[NickCraver]

@ianlee74 Dapper will map each column over - so if it encounters anything n times, the last one will win. You'll find that to be true of almost every (de)serializer, from SQL to JSON, etc. That Id behavior is pretty bad - I hope they're in the process of fixing that. I'm not really sure there's a good fix for this insider Dapper - Azure really needs to change that very undesirable behavior. I'd be very surprised if you're the only person hitting oddities with it.

If you aren't getting anywhere on the Microsoft side, let me know - we have some contacts I can get involved on the issue.

[NickCraver]

Closing this out due to inactivity, I'm really hoping the Azure guys and gals fixed this very bad behavior on their end.