2

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.

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 Create code first, many to many, with additional fields in association table. Suggestions are to split out the table into two one-to-many tables. We would like to prevent this if possible.

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.

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.

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.

dherre65
  • 343
  • 1
  • 2
  • 8
  • I don't think this problem has a clean solution. Default current DateTime value directly in the Database seems a good but twisted option. Using a DB stored procedure launched by EF code is another one. The only true Code First solution is in your link. You shall be mad. – Antoine Pelletier Jan 14 '19 at 21:01
  • Thanks for your response. The default value, what do you mean by twisted? Do you expect problems with such an approach? – dherre65 Jan 14 '19 at 21:17
  • No, but your EF code won't see anything, I hope you don't have to show this value in your app – Antoine Pelletier Jan 14 '19 at 21:23
  • No it would purely function as a pointer for the ETL process, so I guess that would work then, as long as it wouldn't cause any weird errors in the EF. – dherre65 Jan 14 '19 at 21:26
  • Testing it before would be great, usually EF still works even if the SQL table has more columns than what EF can see, I did this often. But I never did this in such a sensitive case, I mean in a join table. – Antoine Pelletier Jan 14 '19 at 21:30
  • Alright, I'll talk to the engineers and we might test this approach. Thanks for your elaboration. – dherre65 Jan 14 '19 at 21:33

0 Answers0