title: How to work with multiple databases in Entity Framework in an ASP.NET MVC application?
body: <h2>Starting point</h2>
  <p><em>(changing some real naming, just to simplify)</em></p>
  <p>The project consists of an ASP.NET MVC 4.7.2 application, with a SQL Server database. The MVC app uses Entity Framework 6, with an entity data model (initially based on database-first model) for current database that generate the classes to manage the database entities.</p>
  <p>The objective of the application is to create and digitally sign document instances based on different kinds of documents. The generated instances are simply PDF files.</p>
  <p>This database has a table (<em>Instance</em>) that stores each instance file (PDF) as a <code>varbinary</code> (column <em>File</em>), among other instance metadata (creator, date, name, department...), with a unique ID as primary key named <em>InstanceId</em>.</p>
  <p>Each instance is created and modified a few times, depending on document type and digital signature requirements. These iterations must take from a day to a year. After finished and completed the digital signature process, the instance would be kept in database for a long time, and the file won't be modified anymore.</p>
  <p>After some time (casuistry could vary from 2 to 5 years, depending on base document type), files could be deleted, as some are already stored in a document repository and other are discarded as obsolete.</p>
  <h2>Problem detected</h2>
  <p>The amount of files is massive nowadays, and keeps growing up by leaps and bounds. That makes the database hard to manage (backups, synchronization with testing development environments...)</p>
  <h2>Potential solution</h2>
  <p>The mainly accepted approach is to keep current database (<strong>MYAPP</strong>) with same table <em>Instance</em> storing all metadata for each instance created, and create a new database (<strong>MYAPP_FILES</strong>) with a unique table (also called <em>Instance</em>) that stores only the files (PDF in <code>varbinary</code> column).</p>
  <p>The link between tables should be using the same ID in both tables, something like <code>MYAPP.Instance.InstanceId = MYAPP_FILES.Instance.InstanceId</code>.</p>
  <p>Creating a view (also proposed in other posts, like <a href="https://stackoverflow.com/questions/21562081/entity-framework-with-multiple-databases">Entity Framework with multiple databases</a>) based on <em>Instance</em> table. Using original table to work with it for transactions (create, update), and take the view for queries (depending on date, this could retrieve <em>File</em> column from <strong>MYAPP</strong> or <strong>MYAPP_FILES</strong> using the <em>InstanceId</em> to relate both databases/tables).</p>
  <p>This requires creating a new entry in the <strong>MYAPP_FILES</strong> <em>Instance</em> table, based on date column, for some instances. This could be achieved using a SQL Server Agent job scheduled to execute periodically.</p>
  <h2>Questions</h2>
  <ul>
  <li>Creating a new EDM to refer and work with <strong>MYAPP_FILES</strong> database means a lot of effort but, does it have other benefits?</li>
  <li>Is there a better solution in terms of efficiency/best practices?</li>
  <li>Once created in current EDM, the view does not contain, of course, navigation properties. Is there a way to generate them using EF?</li>
  <li>Once created both classes to work with (let's suppose, <code>Instance.cs</code> and <code>ViewInstance.cs</code>) is it possible to use the first for creating / updating actions and the second for queries using repository pattern?</li>
  </ul>