title: MongoDB: Combine Aggregation and Filter
body: <p>Please see the following post for some background: <a href="https://stackoverflow.com/questions/39723683/mongodb-c-sharp-driver-return-last-modified-rows-only">MongoDB C# Driver - Return last modified rows only</a></p>
  
  <p>After almost two years of running this code, we've been experiencing performance problems lately and as much as I keep on saying that the code is not the issue, Infrastructure are insisting it's because I'm doing full table scans. </p>
  
  <p>The thing is that the problem is environment specific. Our QA environment runs like a dream all the time but Dev and Prod are very slow at times and fine at other - it's very erratic. They have the same data and code on but Dev and Prod have another app that is also running on the database.</p>
  
  <p>My data has an Id as well as an _id (or AuditId) - I group the data by Id and then return the last _id for that record where it was not deleted. We have multiple historic records for the same ID and I would like to return the last one (see original post).</p>
  
  <p>So I have the following method:</p>
  
  <pre><code>private static FilterDefinition&lt;T&gt; ForLastAuditIds&lt;T&gt;(IMongoCollection&lt;T&gt; collection) where T : Auditable, IMongoAuditable
      {
          var pipeline = new[] { new BsonDocument { { "$group", new BsonDocument { { "_id", "$Id" }, { "LastAuditId", new BsonDocument { { "$max", "$_id" } } } } } } };
          var lastAuditIds = collection.Aggregate&lt;Audit&gt;(pipeline).ToListAsync().Result.ToList().Select(_ =&gt; _.LastAuditId);
  
          var forLastAuditIds = Builders&lt;T&gt;.Filter.Where(_ =&gt; lastAuditIds.Contains(_.AuditId) &amp;&amp; _.Status != "DELETE");
  
          return forLastAuditIds;
      }
  </code></pre>
  
  <p>This method is called by the one below, which accepts an Expression that it appends to the FilterDefinition created by ForLastAuditIds.</p>
  
  <pre><code>protected List&lt;T&gt; GetLatest&lt;T&gt;(IMongoCollection&lt;T&gt; collection,
                                       Expression&lt;Func&lt;T, bool&gt;&gt; filter, ProjectionDefinition&lt;T, T&gt; projection = null,
                                       bool disableRoleCheck = false) where T : Auditable, IMongoAuditable
      {
          var forLastAuditIds = ForLastAuditIds(collection);
  
          var limitedList = (
                  projection != null
                      ? collection.Find(forLastAuditIds &amp; filter, new FindOptions()).Project(projection)
                      : collection.Find(forLastAuditIds &amp; filter, new FindOptions())
              ).ToListAsync().Result.ToList();
  
          return limitedList;
      }
  </code></pre>
  
  <p>Now, all of this works really well and is re-used by all of my code that calls Collections, but this specific collection is a lot bigger than the others and we are getting slowdowns just on that one.</p>
  
  <p>My question is: Is there a way for me to take the aggregate and Filter Builder and combine them to return a single FilterDefinition that I could use without running the full table scan first? </p>
  
  <p>I really hope I am making sense.</p>