MongoDB C# Driver - Return last modified rows only
<p>The data:
The collection contains a list of audit records and I want to return the last modified items from the collection.</p>

<p>For example:</p>

<p><a href="https://i.stack.imgur.com/NefHd.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/NefHd.png" alt="Audit Records"></a></p>

<p>So the query needs to return Audit 1235 and 1237 Only.</p>

<p>The following statement works in Mongo Shell and returns the data sub-millisecond, I just need to also figure out how to return the entire Collection item instead of just the Id.</p>

<pre><code>db.Forms.aggregate(
{ $group: { _id: "$Id", lastModifiedId: { $last: "$_id" } } }
)
</code></pre>

<p>However, I need to convert this to the C# Driver's syntax. </p>

<p>I have the following at the moment but it's not working and returns (for lack of a better term) weird data (see screencap under the statement).</p>

<pre><code>var results = collection.Aggregate()
.Group(new BsonDocument { { "_id", "$Id" }, { "lastModifiedId", new BsonDocument("$last", "_id") } })
.ToListAsync().Result.ToList();
</code></pre>

<p><a href="https://i.stack.imgur.com/E8gv3.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/E8gv3.png" alt="Results"></a></p>

<p>My current solution gets the full collection back and then runs it through an extension method to get the latest records (where list is the full collection):</p>

<pre><code>var lastModifiedOnlyList =
from listItem in list.OrderByDescending(_ =&gt; _.AuditId)
group listItem by listItem.Id into grp
select grp.OrderByDescending(listItem =&gt; listItem.AuditId)
.FirstOrDefault();
</code></pre>

<p>While this code works, it is EXTREMELY slow because of the sheer amount of data that is being returned from the collection, so I need to do the grouping on the list as part of the collection get/find.</p>

<p>Please let me know if I can provide any additional information.</p>

<p><strong>Update:</strong> With Axel's help I managed to get it resolved:</p>

<pre><code>var pipeline = new[] { new BsonDocument { { "$group", new BsonDocument { { "_id", "$Id" }, { "LastAuditId", new BsonDocument { { "$last", "$_id" } } } } } } };
var lastAuditIds = collection.Aggregate&lt;Audit&gt;(pipeline).ToListAsync().Result.ToList().Select(_=&gt;_.LastAuditId);
</code></pre>

<p>I moved that to it's own method and then use the IDs to get the collection items back, with my projection working as well:</p>

<pre><code>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();
</code></pre>

<p>"filter" in this case is either an Expression or a BsonDocument. The performance is great as well - sub-second for the whole thing. Thanks for the help, Axel!</p>