Filter, merge, sort and page data from multiple sources
<p>At the moment I'm retrieving data from the DB through a method that retrieves an <code>IQueryable&lt;T1&gt;</code>, filtering, sorting and then paging it (all these on the DB basically), before returning the result to the UI for display in a paged table.</p>

<p>I need to integrate results from another DB, and paging seems to be the main issue.</p>

<ul>
<li>models are similar but not identical (same fields, different names, will need to map to a generic domain model before returning);</li>
<li>joining at the DB level is not possible;</li>
<li>there are ~1000 records at the moment between both DBs (added during
the past 18 months), and likely to grow at mostly the same (slow)
pace;</li>
<li>results always need to be sorted by 1-2 fields (date-wise).</li>
</ul>

<p>I'm currently torn between these 2 solutions:</p>

<ol>
<li>Retrieve all data from both sources, merge, sort and then cache them; then simply filter and page on said cache when receiving requests - but I need to invalidate the cache when the collection is modified (which I can);</li>
<li>Filter data on each source (again, at the DB level), then retrieve, merge, sort &amp; page them, before returning.</li>
</ol>

<p>I'm looking to find a decent algorithm performance-wise. The ideal solution would probably be a combination between them (caching + filtering at the DB level), but I haven't wrapped my head around that at the moment.</p>