Insert into database from an Excel file only rows that don't exist, using C#, EF, and SQL Server

Question

I read an Excel file and insert that data into a database table, but every time I do this, it adds the existing rows plus the new data, I only want to insert the rows that aren't already in the table, my unique ID is the current time stamp.

For example, this is what happens currently when I do the first insert:

ExcelFile                           Database Table

a | b | date                        a | b | date
-----------                        ---------------
1 | 1 | 2018/02/12                  1 | 1 | 2018/02/12  
2 | 2 | 2018 /03/12                 2 | 2 | 2018 /03/12 

This happens when I do the second insert:

ExcelFile                           Database Table

a | b | date                        a | b | date
-----------                        ---------------
1 | 1 | 2018/02/12                  1 | 1 | 2018/02/12  
2 | 2 | 2018 /03/12                 2 | 2 | 2018 /03/12 
3 | 3 | 2018 /04/12                 1 | 1 | 2018/02/12
                                    2 | 2 | 2018 /03/12 
                                    3 | 3 | 2018 /04/12

I use Entity Framework to perform this and the ExcelDataReader package:

var result = reader.AsDataSet();

DataTable dt = new DataTable();
dt = result.Tables[0];      // here I store the data from the Excel file

foreach (DataRow row in dt.Rows)
{
    using (AppContext context = new AppContext())
    {
        Data data = new Data();
        string date = row.ItemArray[4].ToString();
        DateTime parseDate = DateTime.Parse(date);
        Data datos = new Data
                            {
                                a = row.ItemArray[0].ToString(),
                                b = row.ItemArray[1].ToString(),
                                c = row.ItemArray[2].ToString(),
                                d = row.ItemArray[3].ToString(),
                                e = parseDate
                            };
        context.Data.Add(datos);
        context.SaveChanges();
    }
}

Is there a way to filter the excel file or compare them?

I'm all ears.

Answer 1

Check for an existing row before adding it. The below should be inserted below where you calculate parseDate.

var existingRow = context.Data.FirstOrDefault(d=>d.e == parseDate); //Note that the ".e" should refer to your "date" field
if (existingRow != null)
{
  //This row already exists
}
else
{
  //It doesn't exist, go ahead and add it
}