keep entity framework from terminating insert statment if one entity fails constraint

Question

when i want to import multiple users into my DB i use a suggested method here:

Improving bulk insert performance in Entity framework

here is my code:

                    int countOfAddedUsers = 1;
                    int commitChunkSize = i_ChunkSize;
                    int numOfChunksCommited = 0;
                    foreach (var user in i_Users)
                    {
                        i_Progress.Report(m_SW.Elapsed.ToString(@"mm\:ss") + " (" + countOfAddedUsers.ToString("D5") + ") Adding user to chunk: " + user.Username + "..." + Environment.NewLine);
                        user.Roles.Add(UserRole);
                        context.Users.Add(user);
                        countOfAddedUsers++;

                        if (countOfAddedUsers % commitChunkSize == 0)
                        {
                            var chunkNumber = countOfAddedUsers / commitChunkSize;
                            writeUsersToDB(i_Progress, context, chunkNumber);
                            numOfChunksCommited++;
                        }
                    }

                    if (numOfChunksCommited == 0)
                    {
                        writeUsersToDB(i_Progress, context, 0);
                        numOfChunksCommited++;
                    }

Now my problem is that during import one of the users may already exists in the DB or have the same username.

in that case i get a DBValidationException and the entire "Chunk" insert is terminated because of that one user.

how can i force separate insert statements for every user or at least tell EF to retry without the problematic user ?

Would this work for you? context.Configuration.ValidateOnSaveEnabled = false; — Dylan, Aug 25 '15 at 18:25
nope .. this only checks for data annotation validation not for db validation of foreign keys or unique constraints — Mortalus, Aug 25 '15 at 18:48

score 0 · Answer 1 · edited May 23 '17 at 12:22

What you're looking for is an equivalent to the TSQL merge statement. Insert when value doesn't exist, update (or do nothing) when the value already does exist. Unfortunately for you, EF doesn't have a similar statement.

This existing question on stackoverflow pretty much answers that.

How can I use use Entity Framework to do a MERGE when I don't know if the record exists?

Don't forget however, that you can always use a stage table, and then actually use a merge statement with explicit TSQL from within Entity Framework.

You can also vote up the feature request (support for EF Merge) here:

http://data.uservoice.com/forums/72025-entity-framework-feature-suggestions/suggestions/3883322-support-for-merge-upsert-statement

keep entity framework from terminating insert statment if one entity fails constraint

1 Answers1