Consider adding support for referencing non-primary key columns to foreign keys in Dapper.SimpleSave

[bartread]

Something to consider...

Currently for Dapper.SimpleSave's foreign key support to work, it assumes that you're always referencing the PK value of the target table. This might not necessarily be so.

We should consider whether or not this is a problem and whether we need to add support for FKs referencing other non-PK columns (they'd still have to be ints or GUIDs, because these are the only key types we support).

I'd broadly disfavour implementing this for a couple of reasons:

(1) Why have an FK column that doesn't reference a PK column? What would be the purpose? It sounds needlessly complex.

(2) Dapper.SimpleSave is supposed to help with the main and the plain, not every possible edge/corner case, and this definitely feels like one of the latter.

It certainly could be done, but it feels like it would be encouraging perhaps a lack of discipline in the way we use foreign keys in the database.

[bartread]

We've just run into this with Apollo. If you try to reference a table using a non-PK column you get the following error:

2015-08-18 18:01:29,973 [Runner thread] DEBUG Dapper.SimpleSave.Impl.BasicSimpleSaveLogger [(null)] - { message = Built script, sql = INSERT INTO [cust].CONTACT_MST (
    [UpdateDate], [UpdateSessionGuid], [SalutationKey], [FirstName], [MiddleInitial], [Surname], [EmailAddressGuid], [MainPhoneGuid], [MobilePhoneGuid], [IsPrimaryContact], [PreferredContactType]
)
OUTPUT inserted.[ContactGuid]
 VALUES (
    @p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10
);
, parameters = System.Dynamic.ExpandoObject }  
2015-08-18 18:01:30,052 [Runner thread] INFO  Dapper.SimpleSave.Impl.BasicSimpleSaveLogger [(null)] - { message = Executing script, sql = INSERT INTO [cust].CONTACT_MST (
    [UpdateDate], [UpdateSessionGuid], [SalutationKey], [FirstName], [MiddleInitial], [Surname], [EmailAddressGuid], [MainPhoneGuid], [MobilePhoneGuid], [IsPrimaryContact], [PreferredContactType]
)
OUTPUT inserted.[ContactGuid]
 VALUES (
    @p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10
);
, parameters = System.Dynamic.ExpandoObject }  
2015-08-18 18:01:30,071 [Runner thread] ERROR Dapper.SimpleSave.Impl.BasicSimpleSaveLogger [(null)] - { message = Error executing internal transaction, exception = System.Data.SqlClient.SqlException (0x80131904): Operand type clash: int is incompatible with uniqueidentifier
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteScalar()
   at Dapper.SqlMapper.ExecuteScalarImpl[T](IDbConnection cnn, CommandDefinition& command) in D:\Dev\dapper-dot-net\Dapper NET40\SqlMapper.cs:line 3425
   at Dapper.SqlMapper.ExecuteScalar(IDbConnection cnn, CommandDefinition command) in D:\Dev\dapper-dot-net\Dapper NET40\SqlMapper.cs:line 1271
   at Dapper.SimpleSave.SimpleSaveExtensions.ExecuteCommandForScript[T](IDbConnection connection, T oldRootObject, T newRootObject, Boolean softDelete, IDbTransaction transaction, Script script)
   at Dapper.SimpleSave.SimpleSaveExtensions.ExecuteScripts[T](IDbConnection connection, IList`1 scripts, T oldRootObject, T newRootObject, Boolean softDelete, IDbTransaction transaction)
   at Dapper.SimpleSave.SimpleSaveExtensions.ExecuteScriptsForTuples[T](IDbConnection connection, IEnumerable`1 oldAndNewObjects, IDictionary`2 scripts, Boolean softDelete, IDbTransaction transaction)
   at Dapper.SimpleSave.SimpleSaveExtensions.UpdateInternal[T](IDbConnection connection, IEnumerable`1 oldAndNewObjects, Boolean softDelete, IDbTransaction transaction)
ClientConnectionId:cf1d765c-9acc-424a-8598-2da3ed1ac08a
Error Number:206,State:2,Class:16, scripts = System.Collections.Generic.Dictionary`2[System.Tuple`2[PS.Mothership.Core.Common.Dto.MerchantMaster.ContactMasterDto,PS.Mothership.Core.Common.Dto.MerchantMaster.ContactMasterDto],System.Collections.Generic.IList`1[Dapper.SimpleSave.Impl.Script]] }  
2015-08-18 18:01:30,795 [Runner thread] DEBUG Dapper.SimpleSave.Impl.BasicSimpleSaveLogger [(null)] - { message = Built script, sql = INSERT INTO [cust].CONTACT_MST (
    [UpdateDate], [UpdateSessionGuid], [SalutationKey], [FirstName], [MiddleInitial], [Surname], [EmailAddressGuid], [MainPhoneGuid], [MobilePhoneGuid], [IsPrimaryContact], [PreferredContactType]
)
OUTPUT inserted.[ContactGuid]
 VALUES (
    @p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10
);
, parameters = System.Dynamic.ExpandoObject }  
2015-08-18 18:01:30,795 [Runner thread] INFO  Dapper.SimpleSave.Impl.BasicSimpleSaveLogger [(null)] - { message = Executing script, sql = INSERT INTO [cust].CONTACT_MST (
    [UpdateDate], [UpdateSessionGuid], [SalutationKey], [FirstName], [MiddleInitial], [Surname], [EmailAddressGuid], [MainPhoneGuid], [MobilePhoneGuid], [IsPrimaryContact], [PreferredContactType]
)
OUTPUT inserted.[ContactGuid]
 VALUES (
    @p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10
);
, parameters = System.Dynamic.ExpandoObject }  
2015-08-18 18:01:30,796 [Runner thread] ERROR Dapper.SimpleSave.Impl.BasicSimpleSaveLogger [(null)] - { message = Error executing internal transaction, exception = System.Data.SqlClient.SqlException (0x80131904): Operand type clash: int is incompatible with uniqueidentifier
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteScalar()
   at Dapper.SqlMapper.ExecuteScalarImpl[T](IDbConnection cnn, CommandDefinition& command) in D:\Dev\dapper-dot-net\Dapper NET40\SqlMapper.cs:line 3425
   at Dapper.SqlMapper.ExecuteScalar(IDbConnection cnn, CommandDefinition command) in D:\Dev\dapper-dot-net\Dapper NET40\SqlMapper.cs:line 1271
   at Dapper.SimpleSave.SimpleSaveExtensions.ExecuteCommandForScript[T](IDbConnection connection, T oldRootObject, T newRootObject, Boolean softDelete, IDbTransaction transaction, Script script)
   at Dapper.SimpleSave.SimpleSaveExtensions.ExecuteScripts[T](IDbConnection connection, IList`1 scripts, T oldRootObject, T newRootObject, Boolean softDelete, IDbTransaction transaction)
   at Dapper.SimpleSave.SimpleSaveExtensions.ExecuteScriptsForTuples[T](IDbConnection connection, IEnumerable`1 oldAndNewObjects, IDictionary`2 scripts, Boolean softDelete, IDbTransaction transaction)
   at Dapper.SimpleSave.SimpleSaveExtensions.UpdateInternal[T](IDbConnection connection, IEnumerable`1 oldAndNewObjects, Boolean softDelete, IDbTransaction transaction)
ClientConnectionId:cf1d765c-9acc-424a-8598-2da3ed1ac08a
Error Number:206,State:2,Class:16, scripts = System.Collections.Generic.Dictionary`2[System.Tuple`2[PS.Mothership.Core.Common.Dto.MerchantMaster.ContactMasterDto,PS.Mothership.Core.Common.Dto.MerchantMaster.ContactMasterDto],System.Collections.Generic.IList`1[Dapper.SimpleSave.Impl.Script]] }  

The issue is that we have ContactMasterDto referencing PhoneNumberDto using PhoneGuid as opposed to PhoneNumberKey. PhoneGuid isn't the PK column, which leads to the above slightly cryptic error when it tries to set the INT value from PhoneNumberKey against MainPhoneGuid, or MobilePhoneGuid, which are both UNIQUEIDENTIFIERs.

    [Table("[cust].CONTACT_MST")]
    public class ContactMasterDto
    {
        [PrimaryKey]
        public Guid? ContactGuid { get; set; }

        public DateTimeOffset UpdateDate { get; set; }
        public Guid UpdateSessionGuid { get; set; }

        public int SalutationKey { get; set; }
        [SimpleSaveIgnore]
        public SalutationDto Salutation { get; set; }

        public string FirstName { get; set; }
        public string MiddleInitial { get; set; }
        public string Surname { get; set; }

        [ManyToOne, Column("EmailAddressGuid")]
        public EmailAddressMasterDto EmailAddress { get; set; }

        [ManyToOne, Column("MainPhoneGuid")]
        public PhoneNumberDto MainPhone { get; set; }

        [ManyToOne, Column("MobilePhoneGuid")]
        public PhoneNumberDto MobilePhone { get; set; }

        public bool IsPrimaryContact { get; set; }

        public int? PreferredContactType { get; set; }
    }

    public enum PreferredContactType : byte 
    {
        Phone,
        Mobile,
        Email
    }

...
...

    [Table("[gen].[PHONE_NUMBER_MST]")]
    public class PhoneNumberDto
    {
        [PrimaryKey]
        public int? PhoneNumberKey { get; set; }

        //  TODO: column name!
        [SimpleSaveIgnore]
        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; }

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

        private int DapperEnumHack_PhoneNumberType { set { PhoneNumberType = (PhoneNumberTypeEnum) value; } }

        public override string ToString()
        {
            return string.Format("+{0} {1}", Country.TelephoneCountryCode, PhoneNumber);
        }
    }

A couple of points here:

• It obviously seems like the need for supporting non PK columns is greater than I'd anticipated.
• Really this should fail with an error from Dapper.SimpleSave before it gets to the point of trying to generate SQL.

[bartread]

I've implemented some partial support: for properties marked with [ManyToOne] you can now specify a foreign key target property. I'll need to do the other cardinalities at some point but this is the minimum amount of work required to get us going again now.

Also, tbh, the code could do with a bit of a tidy. Again, OK for now, but when we come back to this we'll need to look at it.