SimpleLoad does not respect the target column property of ManyToOne relationships

[bartread]

For many to one relationships in SimpleSave it's possible to specify the target column for a foreign key relationship from the parent table. Unfortunately SimpleLoad doesn't respect this.

Here's an example query showing where it falls down:

2015-11-26 16:21:01,170 [6] INFO  Dapper.SimpleLoad.SimpleLoadExtensions [(null)] - Executing query:
SELECT [application].[MerchantGuid], 
    [application].[ApplicationQueue], 
    [application].[BankReference], 
    [application].[CreatedFrom], 
    [application].[AcquirerKey] AS [Acquirer], 
    [application].[ProvisionerKey], 
    [application].[BankUserKey], 
    [application].[ApplicationReference], 
    [application].[ApplicationSignedDate], 
    [application].[IsValidated], 
    [application].[QuicksignApplication], 
    [application].[ApplicationGuid], 
    [application].[UpdatedBy], 
    [application].[UpdateDate], 
    [application].[CreatedBy], 
    [application].[CreatedDate], 
    [merchant].[V1MerchantId], 
    [merchant].[LocatorId], 
    [merchant].[ThompsonCodeKey], 
    [merchant].[NumberEmployeesKey], 
    [merchant].[BusinessLegalTypeKey], 
    [merchant].[CurrentTradingBankKey], 
    [merchant].[CallRestrictedReasonKey], 
    [merchant].[EmailRestrictedReasonKey], 
    [merchant].[EmailAddressGUID], 
    [merchant].[WebsiteURL], 
    [merchant].[CreditPreScreenFlag], 
    [merchant].[ExperianBusinessURN], 
    [merchant].[ExperianLocationURN], 
    [merchant].[ExperianLastUpdate], 
    [merchant].[AnnualTurnover], 
    [merchant].[OwnershipUserKey], 
    [merchant].[OwnershipStartDate], 
    [merchant].[DocsInDate], 
    [merchant].[BusinessName], 
    [merchant].[IsVatExempt], 
    [merchant].[DoesEcommerce], 
    [merchant].[EntityType], 
    [merchant].[IsDoNotCall], 
    [merchant].[MerchantGuid], 
    [merchant].[UpdatedBy], 
    [merchant].[UpdateDate], 
    [merchant].[CreatedBy], 
    [merchant].[CreatedDate], 
    [seller].[UserKey], 
    [seller].[UserGUID], 
    [seller].[Username], 
    [seller].[EmailAddress], 
    [department].[DepartmentKey], 
    [department].[Name], 
    [department].[IsExternal], 
    [department].[IsSalesChannel], 
    [sellerNumber].[PhoneGuid], 
    [sellerNumber].[PhoneNumber], 
    [sellerNumber].[BadNumberCount], 
    [sellerNumber].[IsDoNotCall], 
    [sellerNumber].[PhoneNumberTypeKey] AS [PhoneNumberType], 
    [sellerNumber].[UpdatedBy], 
    [sellerNumber].[UpdateDate], 
    [sellerNumber].[CreatedBy], 
    [sellerNumber].[CreatedDate], 
    [sipAccount].[PhoneGuid], 
    [sipAccount].[SipUserId], 
    [sipAccount].[SipPassword], 
    [sipAccount].[SipProxyUserId], 
    [sipAccount].[SipProxyPassword], 
    [sipAccount].[SipAccountTypeKey], 
    [sipAccount].[RecStatusKey], 
    [sipAccount].[VoicemailPhoneGuid], 
    [sipAccount].[UserGuid], 
    [bankUser].[UserKey], 
    [bankUser].[UserGUID], 
    [bankUser].[Username], 
    [bankUser].[EmailAddress], 
    [bankNumber].[PhoneGuid], 
    [bankNumber].[PhoneNumber], 
    [bankNumber].[BadNumberCount], 
    [bankNumber].[IsDoNotCall], 
    [bankNumber].[PhoneNumberTypeKey] AS [PhoneNumberType], 
    [bankNumber].[UpdatedBy], 
    [bankNumber].[UpdateDate], 
    [bankNumber].[CreatedBy], 
    [bankNumber].[CreatedDate], 
    [provisioner].[UserKey], 
    [provisioner].[UserGUID], 
    [provisioner].[Username], 
    [provisioner].[EmailAddress], 
    [provisionerNumber].[PhoneGuid], 
    [provisionerNumber].[PhoneNumber], 
    [provisionerNumber].[BadNumberCount], 
    [provisionerNumber].[IsDoNotCall], 
    [provisionerNumber].[PhoneNumberTypeKey] AS [PhoneNumberType], 
    [provisionerNumber].[UpdatedBy], 
    [provisionerNumber].[UpdateDate], 
    [provisionerNumber].[CreatedBy], 
    [provisionerNumber].[CreatedDate]
FROM [app].[APPLICATION_MST] AS application
LEFT OUTER JOIN [cust].MERCHANT_MST AS merchant
    ON merchant.[MerchantGuid] = application.[MerchantGuid]
LEFT OUTER JOIN [user].USER_MST AS seller
    ON seller.[UserKey] = application.[SellerKey]
LEFT OUTER JOIN [user].USER_DEPARTMENT_LNK AS ia0_uuserdepartmentlnk
    ON seller.[UserKey] = ia0_uuserdepartmentlnk.[UserKey]
LEFT OUTER JOIN [user].DEPARTMENT_ENUM AS department
    ON department.[DepartmentKey] = ia0_uuserdepartmentlnk.[DepartmentKey]
LEFT OUTER JOIN [gen].[PHONE_NUMBER_MST] AS sellerNumber
    ON sellerNumber.[PhoneGuid] = seller.[MobileNumberGuid]
LEFT OUTER JOIN dial.SIP_ACCOUNT_MST AS sipAccount
    ON sipAccount.[PhoneGuid] = seller.[UserGUID]
LEFT OUTER JOIN [user].USER_MST AS bankUser
    ON bankUser.[UserKey] = application.[BankUserKey]
LEFT OUTER JOIN [gen].[PHONE_NUMBER_MST] AS bankNumber
    ON bankNumber.[PhoneGuid] = bankUser.[MobileNumberGuid]
LEFT OUTER JOIN [user].USER_MST AS provisioner
    ON provisioner.[UserKey] = application.[ProvisionerKey]
LEFT OUTER JOIN [gen].[PHONE_NUMBER_MST] AS provisionerNumber
    ON provisionerNumber.[PhoneGuid] = provisioner.[MobileNumberGuid]
WHERE  application.ApplicationQueue = @queue AND application.CountryKey IN @countryKey;  
2015-11-26 16:21:01,171 [6] INFO  Dapper.SimpleLoad.SimpleLoadExtensions [(null)] - Split on:V1MerchantId, UserKey, DepartmentKey, PhoneGuid, PhoneGuid, UserKey, PhoneGuid, UserKey, PhoneGuid  
2015-11-26 16:21:01,207 [6] INFO  Dapper.SimpleLoad.SimpleLoadExtensions [(null)] - Received 1 rows in query result set in 35ms. 

Note the following erroneous JOIN:

LEFT OUTER JOIN dial.SIP_ACCOUNT_MST AS sipAccount
    ON sipAccount.[PhoneGuid] = seller.[UserGUID]

As the following shows, the column in both cases should be UserGUID:

[Table("[user].USER_MST")]
   [ReferenceData]
   public class UserDetailsDto
   {
       [PrimaryKey]
       public int? UserKey { get; set; }
       public Guid UserGUID { get; set; }
       public string Username { get; set; }
       public string EmailAddress { get; set; }
       [Column("MobileNumberGuid")]
       [OneToOne]
       [ForeignKeyReference(typeof(PhoneNumberDto))]
       [ReferenceData]
       [PhoneNumberValidation(PhoneNumberTypeEnum.Mobile)]
       public MobilePhoneNumberDto MobileNumber { get; set; }
       [Column("OfficeNumberGuid")]
       [OneToOne]
       [ForeignKeyReference(typeof(PhoneNumberDto))]
       [ReferenceData]
       [PhoneNumberValidation]
       public PhoneNumberDto OfficeNumber { get; set; }
       [ManyToMany("[user].USER_TEAM_LNK")]
       public IList<TeamDto> Team { get; set; }
       [ManyToMany("[user].USER_DEPARTMENT_LNK")]
       public IList<DepartmentDto> Department { get; set; }
       [ManyToMany("[user].USER_ROLES_LNK")]
       public IList<RoleDto> Roles { get; set; }

       [Column("UserGUID")]
       [ManyToOne("UserGuid")]
       [SimpleSaveIgnore]
       [JsonIgnore]
       public SipAccountMstDao SipAccount { get; set; } // Here, we're basing this on UserGUID

       public PhoneNumberDto DiallerPhoneNumber
       {
           get { return SipAccount != null ? SipAccount.DaillerNumber : null; } }
   }

[Table("dial.SIP_ACCOUNT_MST")]
   public class SipAccountMstDao
   {
       [PrimaryKey]
       public Guid? PhoneGuid { get; set; }
       public string SipUserId { get; set; }
       public string SipPassword { get; set; }
       public string SipProxyUserId { get; set; }
       public string SipProxyPassword { get; set; }
       public int SipAccountTypeKey { get; set; }
       public int RecStatusKey { get; set; }
       public Guid VoicemailPhoneGuid { get; set; }
       public Guid UserGuid { get; set; }

       [SimpleSaveIgnore,SimpleLoadIgnore]
       public string PhoneNumber { get; set; }

       [Column("PhoneGuid")]
       [OneToOne]
       [ForeignKeyReference(typeof(PhoneNumberDto))]
       public PhoneNumberDto DaillerNumber { get; set; }
   }

[Table("[gen].[PHONE_NUMBER_MST]")]
   public class PhoneNumberDto : BaseAuditableMst
   {

       // N.B. We DON'T want to send this back to the client because the user MIGHT update the number,
       // in which case it needs to be treated like a new number and assigned a new GUID. If they don't
       // update the number the existing record for the number will be reattached to the UserDto when they're
       // saved.
       [JsonIgnore]
       [XmlIgnore]
       [PrimaryKey]
       public Guid? PhoneGuid { get; set; }

       [ManyToOne]
       [Column("CountryKey")]
       public CountryDto Country { get; set; }

       public string PhoneNumber { get; set; }

       public int BadNumberCount { get; set; }

       public bool IsDoNotCall { get; set; }

       [Column("PhoneNumberTypeKey")]
       public PhoneNumberTypeEnum PhoneNumberType { get; set; }

       // HACK - this is minging and might be avoidable - revisit
       // Is used in Dapper Repository Queries
       // ReSharper disable UnusedMember.Local
       private int DapperEnumHack_PhoneNumberType { set { PhoneNumberType = (PhoneNumberTypeEnum) value; } }

       public override string ToString()
       {
           return Country == null || Country.CountryEnum == GenCountryEnum.None || string.IsNullOrEmpty(PhoneNumber)
               ? PhoneNumber
               : string.Format("+{0} {1}", Country.TelephoneCountryCode, PhoneNumber.TrimStart('0'));
       }
   }

[bartread]

Boom! Just added fix, along with a test case that generates this:

SELECT [ta0_suwsd].[UserKey], 
    [ta0_suwsd].[UserGUID], 
    [ta0_suwsd].[Username], 
    [ta0_suwsd].[EmailAddress], 
    [ta1_samd].[PhoneGuid], 
    [ta1_samd].[SipUserId], 
    [ta1_samd].[SipPassword], 
    [ta1_samd].[SipProxyUserId], 
    [ta1_samd].[SipProxyPassword], 
    [ta1_samd].[SipAccountTypeKey], 
    [ta1_samd].[RecStatusKey], 
    [ta1_samd].[VoicemailPhoneGuid], 
    [ta1_samd].[UserGuid]
FROM [user].USER_MST AS ta0_suwsd
LEFT OUTER JOIN dial.SIP_ACCOUNT_MST AS ta1_samd
    ON ta1_samd.[UserGuid] = ta0_suwsd.[UserGUID]
WHERE ta0_suwsd.[UserKey] = @UserKey
;

I really need to wire in some logging and automated verification for these generated scripts. Will do today but for now we need this fix in.