title: consolidating records from multiple tables to a single table and removing text field duplicates
body: <p>I'm working on an application where we have entities such as agency, person, customer, job, etc represented in their own tables.  The original developers also created a notes table for each entity in the format of agencynotes, personnotes, customernotes, jobnotes, etc.  Eventually, on the note entry page, functionality was added so that when you create a note on a person you can select an option to write the same note to any related agency, customer or job note.  Obviously this has resulted in a ton of duplicated notes across all the entity types.</p>
  <p>We want to consolidate all of the notes into one collection of notes that are tagged with the different related records with a single instance of the note.  We then want to get it into elasticsearch for searching, so ultimately we'll be exporting to a json format.</p>
  <p>The problem is that we're dealing with a total of 1.4 million notes and the note body is a text field in sql server.  Here's some code I have so far.</p>
  <pre><code>using (var cn = new DbContext(DataSource))
  {
      foreach (var agencynote in cn.AgenciesNotes.Where(x =&gt; !x.Processed).Take(100).ToList())
      {
          decimal customerid, peopleid;
          customerid = peopleid = 0;
  
          var custnote = cn.CustomerNotes.FirstOrDefault(x =&gt; x.Notes == agencynote.Notes);
          if (custnote != null)
          {
              customerid = custnote.CustomerID;
              custnote.Processed = true;
          }
  
          var peoplenote = cn.PeopleNotes.FirstOrDefault(x =&gt; x.Notes == agencynote.Notes);
          if (peoplenote != null)
          {
              peopleid = peoplenote.PeopleID;
              peoplenote.Processed = true;
          }
          
          var newNote = new NotesAll()
          {
              AgencyID = agencynote.AgencyID,
              CustomerID = customerid,
              EnteredDate = agencynote.EnteredDate,
              Notes = agencynote.Notes,
              NotesTypeID = agencynote.NotesTypeID,
              PeopleId = peopleid
          };
  
          cn.NotesAlls.Add(newNote);
          cn.SaveChanges();
      }
  }
  </code></pre>
  <p>When I run this it breaks on this line.</p>
  <pre><code>var custnote = cn.CustomerNotes.FirstOrDefault(x =&gt; x.Notes == agencynote.Notes);
  </code></pre>
  <p>The error is that you can't compare varchar and text fields.  First off, both fields are defined in the database as text and the data annotations on the EF models also specify [Column(TypeName = &quot;text&quot;)].  So, any idea why it's thinking one is varchar and the other is text?</p>
  <p>Also, is there a better way to do this - especially knowing the final goal of producing a json file for elasticsearch?  I know this is going to take ages to complete but not sure of another way to remove duplicates.  Thanks.</p>