I have a program downloading data and saving into my SQL Server 2019 database. The program uses Entity Framework 5, and saves hundreds of rows in a lot of tables.
When the downloaded data causes an error (for example a string is longer than the database column), I get an exception and can't save the row. And this is ok.
The bad thing is that if the saving of the row #100 fails, every subsequent row also throws the same error. Currently I create a DbContext at the beginning of the program, and I don't use transactions.
I tried with TransactionScope, with the same results: the following rows aren't getting saved.
Can someone help me understand WHY after an error I can't save any more rows?