Avoiding Duplicate Inserts with Entity Framework

Question

Hello everyone and thanks for the help in advance. I am developing an Asp.Net Core application where a user inputs various items within an order I'm trying to prohibit duplicate inserts. The record being added has approximately 17 columns. Many of the columns may have the same data, for example CustomerID, so I guess one solution would be to do a select, testing for each column, and if that record exists, don't insert. But this seems rather inefficicient and I ma wondering if there is an easier way.. Any help would be appreciated.

Answer 1

So if I understand correctly, the only way to avoid duplicates with first querying is to have a pre-defined unique index value.

Creating a unique constraint is a very common approach to stop duplicate data from entering the database at the table level. This is not a new concept and the documentation is openly published. It is totally up to you if you want to take advantage of a unique constraint.

Create unique constraints

So assuming the CustomerID and ItemNo may repeat, there is really no way to accomplish this without first querying the database. Am I understanding you correctly

You have not explained what constitutes a duplicate record in your application. More importantly, there is nothing stopping you from querying a table to figure out if the data already exists.

I would at the very least create a unique constraint because doing so stops duplicates at the table level. That way if someone writes an ad-hoc insert/update or another application has access to the table the unique constraint will stop duplicate entries.

Checking for duplicates at the application level is perfectly fine as well, especially if a unique constraint exists. A unique constraint violation will cause an exception in the application. You have the option of handling the exception and/or checking for duplicates.

Your original question is concerned with efficiency. Do you have efficiency specifications and if so what are the specs? If you are worried about moving data between the web and DB servers, perhaps crafting a stored procedure that does the duplicate check then insert/update if the duplicate check passes.

Answer 2

Hi @Kmcnet , Welcome to Microsoft Q&A,

You can add a unique constraint to a table in your database to ensure that the combination of columns you want to be unique must be unique. I am using sqlServer, now I have a column named DependOn in actionTable, I want to add DependOn unique index, use the following sql statement:

ALTER TABLE actionTable ADD unique(DependOn); 

In this way, once I add a set of data that contains the same DependOn, the insertion will fail.

The code is as follows:

try
{
     var log1 = new ActionTable
     {
         FirstName = "John",
         LastName = "Doe",
         DependOn = 0
     };

     context.Add(log1);

     context.SaveChanges();
}
catch (DbUpdateException ex)
{
     var sqlException = ex.GetBaseException() as SqlException;
     if (sqlException.Number == 2627)
     {
          Console.WriteLine("Data duplication");
     }
     else
     {
         Console.WriteLine("Other errors");
     }
}

Another method is to query whether the same data exists in the database table before adding a set of data. If it does not exist, insert it again, but this will waste a lot of performance.

The code is as follows:

                string firstName = "John";

                string lastName = "Doe";

                int dependOn = 2;

                var existingRecord = context.actionTable
                                          .FirstOrDefault(a => a.FirstName == firstName
                                                             && a.LastName == lastName
                                                             && a.DependOn == dependOn);

                if (existingRecord == null)
                {
                    var newRecord = new ActionTable
                    {
                        FirstName = firstName,
                        LastName = lastName,
                        DependOn = dependOn
                    };

                    context.actionTable.Add(newRecord);

                    context.SaveChanges();
                }

                else if(existingRecord != null) 
                {
                    Console.WriteLine("Data duplication");
                }

Best Regards,

Wenbin

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.