{"id":1835,"date":"2013-01-26T16:56:00","date_gmt":"2013-01-26T22:56:00","guid":{"rendered":"http:\/\/www.wiredprairie.us\/blog\/?p=1835"},"modified":"2013-01-26T14:39:01","modified_gmt":"2013-01-26T20:39:01","slug":"how-to-rewrite-a-mongodb-c-linq-with-a-projection-requirement-using-a-mongocursor","status":"publish","type":"post","link":"https:\/\/www.wiredprairie.us\/blog\/index.php\/archives\/1835","title":{"rendered":"How to rewrite a MongoDB C# LINQ with a Projection Requirement using a MongoCursor"},"content":{"rendered":"<p align=\"left\">The LINQ Provider for MongoDB does not <a href=\"https:\/\/jira.mongodb.org\/browse\/CSHARP-456\">currently<\/a> take into account data projections efficiently when returning data. This could mean that you\u2019re unnecessarily returning more data from the database than is needed. <\/p>\n<p>So, I\u2019m going to show you the pattern I applied as a replacement for the LINQ queries when I need to use a projection.<\/p>\n<p>Given the following simple LINQ statement:<\/p>\n<pre class=\"csharpcode\">var query = \n    (from r <span class=\"kwrd\">in<\/span> DataLayer.Database\n         .GetCollection&lt;Research&gt;()\n         .AsQueryable&lt;Research&gt;()\n        <span class=\"kwrd\">where<\/span> !r.Deleted\n        select <span class=\"kwrd\">new<\/span>\n        {\n            Id = r.Id,\n            Title = r.Title,\n            Created = r.Created\n        }).Skip(PageSize * page).Take(PageSize);<\/pre>\n<p><style type=\"text\/css\">\n.csharpcode, .csharpcode pre\n{\n\tfont-size: small;\n\tcolor: black;\n\tfont-family: consolas, \"Courier New\", courier, monospace;\n\tbackground-color: #ffffff;\n\t\/*white-space: pre;*\/\n}\n.csharpcode pre { margin: 0em; }\n.csharpcode .rem { color: #008000; }\n.csharpcode .kwrd { color: #0000ff; }\n.csharpcode .str { color: #006080; }\n.csharpcode .op { color: #0000c0; }\n.csharpcode .preproc { color: #cc6633; }\n.csharpcode .asp { background-color: #ffff00; }\n.csharpcode .html { color: #800000; }\n.csharpcode .attr { color: #ff0000; }\n.csharpcode .alt \n{\n\tbackground-color: #f4f4f4;\n\twidth: 100%;\n\tmargin: 0em;\n}\n.csharpcode .lnum { color: #606060; }<\/style>\n<p>it can be converted to a MongoCursor style search like this:<\/p>\n<pre class=\"csharpcode\">var cursor = DataLayer.Database.GetCollection&lt;Research&gt;()\n    .FindAs&lt;Research&gt;(Query&lt;Research&gt;.NE(r =&gt; r.Deleted, <span class=\"kwrd\">true<\/span>))\n        .SetFields(\n            Fields&lt;Research&gt;.Include(\n                r =&gt; r.Id, \n                r =&gt; r.Title, \n                r =&gt; r.Created))\n        .SetLimit(PageSize)\n        .SetSkip(PageSize * page);           <\/pre>\n<p><style type=\"text\/css\">\n.csharpcode, .csharpcode pre\n{\n\tfont-size: small;\n\tcolor: black;\n\tfont-family: consolas, \"Courier New\", courier, monospace;\n\tbackground-color: #ffffff;\n\t\/*white-space: pre;*\/\n}\n.csharpcode pre { margin: 0em; }\n.csharpcode .rem { color: #008000; }\n.csharpcode .kwrd { color: #0000ff; }\n.csharpcode .str { color: #006080; }\n.csharpcode .op { color: #0000c0; }\n.csharpcode .preproc { color: #cc6633; }\n.csharpcode .asp { background-color: #ffff00; }\n.csharpcode .html { color: #800000; }\n.csharpcode .attr { color: #ff0000; }\n.csharpcode .alt \n{\n\tbackground-color: #f4f4f4;\n\twidth: 100%;\n\tmargin: 0em;\n}\n.csharpcode .lnum { color: #606060; }<\/style>\n<p>I\u2019ve attempted to format it in a similar way mostly for my own sanity. As you see, both queries first get access to the database collection. But, instead of using <strong>AsQueryable&lt;T&gt;<\/strong>, you\u2019ll use the <strong>FindAs&lt;T&gt;<\/strong> method. The query is more verbose in the second example, although not overly so. I chose to keep it strongly typed by using the generic version <strong>Query&lt;Research&gt;<\/strong>. By doing so, it meant that I could use an Expression to set the field\/property that was being queried, rather than rely on a string (I could have just passed \u201cDeleted\u201d as a string in the code). <\/p>\n<p>By strongly typing the parameters in this way, it meant that the compile process can catch things like type mismatches (verifying that the value being compared is a Boolean for example as is the Deleted property).<\/p>\n<p>Secondly, and this addresses the requirement of a <a href=\"http:\/\/docs.mongodb.org\/manual\/core\/read-operations\/#result-projections\">result projection<\/a>, I\u2019ve included just those fields that are required by the UI rather than all of the fields in the document. One of the fields is potentially quite long (up to 1MB of text), and in this case, unnecessary in a summary list display in my web application. Here, I used the <strong>SetFields <\/strong>method of the MongoCursor.<\/p>\n<p>The C# driver includes a static class called <strong>Fields<\/strong> (in a generic and non-generic form) which can be used to express the set of fields to be included\/excluded. I\u2019ll point out that there is an option to just pass in a list of strings to <strong>SetFields<\/strong>, but it\u2019s not strongly typed. So again, no compile-time checking that I\u2019ve got the property names correct. I\u2019m going for safety here, so I chose the strongly-typed generic implementation of <strong>Fields&lt;Research&gt;<\/strong>. Then, using the <strong>Expression<\/strong> syntax again, I\u2019ve selected the fields I needed as a parameter list.<\/p>\n<p>Finally, I added some code to limit the result size and set the skip equivalent to the original LINQ query. <\/p>\n<p>There are a number of other Query methods that you can use to build more complex operations.<\/p>\n<p>For example:<\/p>\n<pre class=\"csharpcode\">var q = Query.And(\n    Query&lt;Research&gt;.Exists(r =&gt; r.Title), \n    Query&lt;Research&gt;.Matches(\n        r =&gt; r.Title, BsonRegularExpression.Create(<span class=\"kwrd\">new<\/span> Regex(<span class=\"str\">&quot;^R&quot;<\/span>))));<\/pre>\n<p><style type=\"text\/css\">\n.csharpcode, .csharpcode pre\n{\n\tfont-size: small;\n\tcolor: black;\n\tfont-family: consolas, \"Courier New\", courier, monospace;\n\tbackground-color: #ffffff;\n\t\/*white-space: pre;*\/\n}\n.csharpcode pre { margin: 0em; }\n.csharpcode .rem { color: #008000; }\n.csharpcode .kwrd { color: #0000ff; }\n.csharpcode .str { color: #006080; }\n.csharpcode .op { color: #0000c0; }\n.csharpcode .preproc { color: #cc6633; }\n.csharpcode .asp { background-color: #ffff00; }\n.csharpcode .html { color: #800000; }\n.csharpcode .attr { color: #ff0000; }\n.csharpcode .alt \n{\n\tbackground-color: #f4f4f4;\n\twidth: 100%;\n\tmargin: 0em;\n}\n.csharpcode .lnum { color: #606060; }<\/style>\n<p>The above maps to the following MongoDB query:<\/p>\n<pre class=\"csharpcode\">{ <span class=\"str\">&quot;$and&quot;<\/span> : [{ <span class=\"str\">&quot;Title&quot;<\/span> : { <span class=\"str\">&quot;$exists&quot;<\/span> : <span class=\"kwrd\">true<\/span> } }, { <span class=\"str\">&quot;Title&quot;<\/span> : \/^R\/ }] }<\/pre>\n<p>Title field exists and the Title field starts with an uppercase \u201cR\u201d.<\/p>\n<p>While the Query style syntax is more verbose than the equivalent LINQ statement, the result still is expressive and very readable and maintainable.<\/p>\n<p>FYI: If there\u2019s an index on Title, then the \/^R\/ syntax returns the results the most <a href=\"http:\/\/docs.mongodb.org\/manual\/reference\/operator\/regex\/#_S_regex\">efficiently<\/a> in MongoDB (as it stops searching after the first character).<\/p>\n","protected":false},"excerpt":{"rendered":"<p>The LINQ Provider for MongoDB does not currently take into account data projections efficiently when returning data. This could mean that you\u2019re unnecessarily returning more data from the database than is needed. So, I\u2019m going to show you the pattern I applied as a replacement for the LINQ queries when I need to use a [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"spay_email":"","jetpack_publicize_message":"","jetpack_is_tweetstorm":false,"jetpack_publicize_feature_enabled":true},"categories":[4],"tags":[82,131,129],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/pd5QIe-tB","jetpack_likes_enabled":true,"jetpack-related-posts":[{"id":1833,"url":"https:\/\/www.wiredprairie.us\/blog\/index.php\/archives\/1833","url_meta":{"origin":1835,"position":0},"title":"How to view the MongoDB Query when using the C# LINQ Provider","date":"January 26, 2013","format":false,"excerpt":"If you\u2019re using the Official MongoDB C# Driver from 10gen, you may want to occasionally verify that the generated query matches your LINQ query (or at least that it\u2019s building something efficient). Take for example this query: var query = (from r in DataLayer.Database.GetCollection<Research>().AsQueryable<Research>() where !r.Deleted select new { Id\u2026","rel":"","context":"In &quot;Coding&quot;","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1820,"url":"https:\/\/www.wiredprairie.us\/blog\/index.php\/archives\/1820","url_meta":{"origin":1835,"position":1},"title":"Knockout binding for JavaScript route fixup","date":"January 25, 2013","format":false,"excerpt":"Part one. After the first round, I felt compelled to KnockOut the code a bit more. I\u2019d mentioned I wasn\u2019t pleased with the code exactly. It needed some refactoring. So, I\u2019ve created a new Knockout binding handler. This binding handler replaces\u00a0 named parameters with a model\u2019s properties in a path.\u2026","rel":"","context":"In &quot;Coding&quot;","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1345,"url":"https:\/\/www.wiredprairie.us\/blog\/index.php\/archives\/1345","url_meta":{"origin":1835,"position":2},"title":"Creating a simple Entity Reference system for Ember.js","date":"December 28, 2011","format":false,"excerpt":"I had some data stored in a structure similar to this: Nothing too fancy. A table of Gift(s) and a table of Person(s). Each gift had a foreign key relationship to a Person (the person who \u201cgave\u201d the gift). Since some people may give several gifts, I didn\u2019t want to\u2026","rel":"","context":"In &quot;Coding&quot;","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1857,"url":"https:\/\/www.wiredprairie.us\/blog\/index.php\/archives\/1857","url_meta":{"origin":1835,"position":3},"title":"Finding duplicates in MongoDB via the shell","date":"March 10, 2013","format":false,"excerpt":"I thought this was an interesting question to answer on StackOverflow (summarized here): I\u2019m trying to create an index, but an error is returned that duplicates exist for the field I want to index. What should I do? I answered with one possibility. The summary is that you can use\u2026","rel":"","context":"In &quot;Coding&quot;","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1996,"url":"https:\/\/www.wiredprairie.us\/blog\/index.php\/archives\/1996","url_meta":{"origin":1835,"position":4},"title":"Animating an element&rsquo;s visibility using AngularJS","date":"January 31, 2014","format":false,"excerpt":"There was a question on StackOverflow about animating an element and I wanted to give it a shot, so here goes: http:\/\/jsfiddle.net\/wiredprairie\/5tFCZ\/1\/ \u00a0 Imagine nearly the simplest Angular JS application possible: <div ng-app=\"App\"> <div ng-init=\"checked=true\"> <div> <label> <input type=\"checkbox\" ng-model=\"checked\" \/>Is Visible...<\/label> <\/div> <div class=\"sample\" ng-class=\"{ hidden: !checked }\">Visible...<\/div> <\/div>\u2026","rel":"","context":"In &quot;Coding&quot;","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1430,"url":"https:\/\/www.wiredprairie.us\/blog\/index.php\/archives\/1430","url_meta":{"origin":1835,"position":5},"title":"Ember.JS and EZdata, and Rails","date":"January 7, 2012","format":false,"excerpt":"I\u2019ve been trying to do some additional work on my ember.js extension for data management. At the same time though, I\u2019ve been trying (to learn and) build a simple Ruby on Rails web demo application using the new JavaScript library. There have been more than a few things that have\u2026","rel":"","context":"In &quot;Coding&quot;","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"https:\/\/www.wiredprairie.us\/blog\/index.php\/wpjson\/wp\/v2\/posts\/1835"}],"collection":[{"href":"https:\/\/www.wiredprairie.us\/blog\/index.php\/wpjson\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.wiredprairie.us\/blog\/index.php\/wpjson\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.wiredprairie.us\/blog\/index.php\/wpjson\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.wiredprairie.us\/blog\/index.php\/wpjson\/wp\/v2\/comments?post=1835"}],"version-history":[{"count":0,"href":"https:\/\/www.wiredprairie.us\/blog\/index.php\/wpjson\/wp\/v2\/posts\/1835\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.wiredprairie.us\/blog\/index.php\/wpjson\/wp\/v2\/media?parent=1835"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.wiredprairie.us\/blog\/index.php\/wpjson\/wp\/v2\/categories?post=1835"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.wiredprairie.us\/blog\/index.php\/wpjson\/wp\/v2\/tags?post=1835"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}