title: Incremental ETL on code first many-to-many association table
body: <p>I'm setting up a data warehouse (in SQL Server) together with our engineers we got almost everything up and running. Our main application also uses SQL Server as backend, and aims to be code first while using the entity framework. In most tables we added a column like updatedAt to allow for incremental loading to our data warehouse, but there is a many-to-many association table created by the entity framework which we cannot modify. The table consists of two GUID columns with a composite key, so they are not iterable like an incrementing integer or dates. We are now basically figuring out the options on how to enable incremental load on this table, but there is little information to be found.</p>
  
  <p>After searching for a while I mostly came across posts which explained how it's not possible to manually add columns (such as updatedAt) to the association table, such as here <a href="https://stackoverflow.com/questions/7050404/create-code-first-many-to-many-with-additional-fields-in-association-table">Create code first, many to many, with additional fields in association table</a>. Suggestions are to split out the table into two one-to-many tables. We would like to prevent this if possible.</p>
  
  <p>Another potential option would be to turn on change data capture on the server, but that would potentially defeat the purpose of code first in the application.</p>
  
  <p>Another thought was to add a column in the database itself, not in code, with a default value of the current datetime. But that might also be impossible / non compatible with the entity framework, as well as defeating the code first principle.</p>
  
  <p>Are we missing anything? Are there other solutions for this? The ideal solution would be a code first solution, or a solution in the ETL process without affecting the base application, without changing too much. Any suggestions are appreciated.</p>