Ids not coming back after first BulkInsert

[geremysremitter]

Description

Following your examples for one-to-one and one-to-many bulk inserts, we are unable to get beyond first bulkinsert. It appears that that the entity model should be receiving the new ids created during insert but after watering down the logic (for diagnostics) we are not seeing the actual SQL identity values manifesting in the C# entity model and believe this is the reason we get one bulkinsert to work (which is beautiful) but not the child tables -

Exception

Microsoft.Data.SqlClient.SqlException: 'The INSERT statement conflicted with the FOREIGN KEY constraint "FK_AccountContact_Contact". The conflict occurred in database "{db}", table "Core.Contact", column 'ContactId'. The statement has been terminated.'

Microsoft.Data.SqlClient.SqlConnection.OnError(Microsoft.Data.SqlClient.SqlException, bool, System.Action<System.Action>)
Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(Microsoft.Data.SqlClient.TdsParserStateObject, bool, bool)
Microsoft.Data.SqlClient.TdsParser.TryRun(Microsoft.Data.SqlClient.RunBehavior, Microsoft.Data.SqlClient.SqlCommand, Microsoft.Data.SqlClient.SqlDataReader, Microsoft.Data.SqlClient.BulkCopySimpleResultSet, Microsoft.Data.SqlClient.TdsParserStateObject, out bool)
Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(Microsoft.Data.SqlClient.SqlDataReader, Microsoft.Data.SqlClient.RunBehavior, string, bool, bool, bool)
Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(System.Data.CommandBehavior, Microsoft.Data.SqlClient.RunBehavior, bool, bool, int, out System.Threading.Tasks.Task, bool, bool, Microsoft.Data.SqlClient.SqlDataReader, bool)
Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(System.Data.CommandBehavior, Microsoft.Data.SqlClient.RunBehavior, bool, System.Threading.Tasks.TaskCompletionSource<object>, int, out System.Threading.Tasks.Task, out bool, bool, bool, string)
Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(System.Threading.Tasks.TaskCompletionSource<object>, bool, int, out bool, bool, bool, string)
Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
.(System.Data.Common.DbCommand, Z.BulkOperations.BulkOperation, int)
.(, System.Data.Common.DbCommand)

Fiddle or Project (Optional)

Code looks like this

Table("Core.Contact")]
    public class Contact
    {
        [Key]
        public int ContactId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string CreatedBy { get; set; }
        public DateTime CreatedOn { get; set; }
        // etc omitting most properties for brevity
        public AccountContact AccountContact { get; set; }
    }

    [Table("Core.AccountContact")]
    public class AccountContact
    {
        [Key]
        public int AccountContactId { get; set; }
        public int ContactId { get; set; }
        public string ContactNumber { get; set; }
       // etc omitting most properties for brevity 
        public DateTime CreatedOn { get; set; }
    }

            DapperPlusManager.Entity<SqlCoreModels.AccountInvoice>().Identity(i => i.AccountInvoiceId, true);
            DapperPlusManager.Entity<SqlCoreModels.AccountContact>().Identity(i => i.AccountContactId, true);
            DapperPlusManager.Entity<SqlCoreModels.Contact>().Identity(i => i.ContactId, true);

            context.BulkInsert(contacts).ThenForEach(i => i.AccountContact.ContactId = i.ContactId);

            // this is what we want to do but trying the above line only (to avoid error) inserts contacts but leaves the ContactId as 0
            //context.BulkInsert(contacts).ThenForEach(i => i.AccountContact.ContactId = i.ContactId).ThenBulkInsert(i => i.AccountContact);

Additionally, SQL table defs look like the following

CREATE TABLE [Core].[Contact](
    [ContactId] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [varchar](100) NOT NULL,
    [LastName] [varchar](100) NOT NULL,
    [CreatedBy] [varchar](100) NOT NULL,
    [CreatedOn] [datetime] NOT NULL,
        -- etc 
    CONSTRAINT [PK_ContactContact] PRIMARY KEY CLUSTERED (
        [ContactId] ASC
    )
)

Further technical details

• Dapper version: 2.0.123
• Dapper Plus version: 6.0.0
• Dapper Contrib: 2.0.78
• Database Provider: SQL Server (Azure)

[JonathanMagnan]

Hello @geremysremitter ,

We are probably missing a piece of the puzzle, but so far, everything seems to work: https://dotnetfiddle.net/bUQjIb

Could you edit this fiddle or provide a project that reproduces this issue?

Best Regards,

Jon

[geremysremitter]

Hi and thanks for the super fast response. We have some real wizardry going on here as I am able to make the Fiddle work in a simple console project (thanks for that) but as soon as I port it to our big project (line for line) I continue to get the foreign key complaint. It's a mystery at present and still digging.

Thanks and I can update here if anything comes of the investigation that might be worth noting.

[geremysremitter]

This is resolved by starting with the working .Net fiddle then bringing into a console app and verifying the schemas references by the entity attributes. One thing we found was that the entity name should match the table name as we had tweeked these due to the various attempts and replicating some tables in different schemas so the whole mess of evaluating seemed to be the issue. Otherwise this is a beautiful library and looking forward to working with the merge features. Thanks again for the quick response !

[JonathanMagnan]

Awesome, I'm happy that you solved your issue.

Thank you as well for your good word, always feel great to hear those ;)

Have a good day,

Best Regards,

Jon