title: Bulk insert c# datatable to sql server - Efficient check for duplicate
body: <p>I wish to import many files into a database (with custom business logic preventing simple SSIS package use).</p>
  
  <p>High level description of problem:</p>
  
  <ul>
  <li>Pull existing sql data into DataTable (~1M rows)</li>
  <li>Read excel file into 2d array in one chunk</li>
  <li>validate fields row by row (custom logic)</li>
  <li>Check for duplicate in existing DataTable</li>
  <li>Insert into DataRow</li>
  <li>Bulk insert DataTable into SQL table</li>
  </ul>
  
  <p>Problem with my approach:
  Each row must be checked for duplicates, I thought a call to remote server to leverage SQL would be too slow, so I opted for LINQ. The query was simple, but the size of the dataset causes it to crawl (90% execution time spent in this query checking the fields).</p>
  
  <pre><code>var existingRows = from row in recordDataTable.AsEnumerable()
                                   where row.Field&lt;int&gt;("Entry") == entry
                                   &amp;&amp;    row.Field&lt;string&gt;("Device") == dev
                                   select row;
  bool update = existingRows.Count() &gt; 0;
  </code></pre>
  
  <p>What other ways might there be to more efficiently check for duplicates?</p>