Showing posts from August, 2020

Entity Framework Core, SQL Server, and Deadlock victims

The application I am currently working on loads text files, transforms them, and then saves them as relational data. I use Entity Framework Core with SQL Server for my persistence. Unfortunately, when inserting so much data I keep experiencing exceptions because SQL Server is throwing deadlock exceptions and preventing my data from being saved. The insert involves data for 4 related tables, every access to the DB is an insert, which is why I was so surprised to see SQL Server couldn't cope with it - especially as Firebird SQL didn't struggle (nor did PostgreSQL).  It seems that adding OPTION (LOOP JOIN) to the end of SQL Statements prevents this problem, so I wrote an extension to ensure it is appended to Entity Framework Core generated SQL. It is used like so: protected override void OnConfiguring(DbContextOptionsBuilder options) { base.OnConfiguring(options); options.UseLoopJoinQueries(); } and the extension code you need is: public static class UseLoopJoinQueriesExte