<p>I have the following (pretty standard) table structure:</p>

<pre><code>Post &lt;-&gt; PostTag &lt;-&gt; Tag
</code></pre>

<p>Suppose I have the following records:</p>

<pre><code>PostID Title
1,     'Foo'
2,     'Bar'
3,     'Baz'

TagID Name
1,    'Foo'
2,    'Bar'

PostID TagID
1      1
1      2
2      2
</code></pre>

<p>In other words, the first post has two tags, the second has one and the third one doesn't have any.</p>

<p><strong>I'd like to load all posts and it's tags in one query</strong> but haven't been able to find the right combination of operators. I've been able to load either <em>posts with tags only</em> or <em>repeated posts when more than one tag</em>.</p>

<p>Given the database above, <strong>I'd like to receive three posts and their tags (if any) in a collection property of the Post objects</strong>. Is it possible at all?</p>

<p>Thanks</p>

[{'answer_id': 79979, 'author': 'sirrocco', 'author_id': 5246, 'author_profile': 'https://Stackoverflow.com/users/5246', 'pm_score': 0, 'selected': False, 'text': '<p>I\'ve answered this in another post : <a href="https://stackoverflow.com/questions/50169/optimizing-a-linq-to-sql-query#50240">About eager loading</a>. In your case it would probably be something like :</p>\n\n<pre><code>DataLoadOptions options = new DataLoadOptions();    \noptions.LoadWith&lt;Post&gt;(p =&gt; p.PostTag);\noptions.LoadWith&lt;PostTag&gt;(pt =&gt; pt.Tag); \n</code></pre>\n\n<p>Though be careful - the DataLoadOptions must be set BEFORE ANY query is sent to the database - if not, an exception is thrown (no idea why it\'s like this in Linq2Sql - probably will be fixed in a later version).</p>\n'}, {'answer_id': 80038, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 0, 'selected': False, 'text': "<p>I'm sorry no, Eager Loading will execute one extra query per tag per post.</p>\n\n<p>Tested with this code:</p>\n\n<pre><code>var options = new DataLoadOptions();\noptions.LoadWith&lt;Post&gt;(p =&gt; p.PostTags);\noptions.LoadWith&lt;PostTag&gt;(pt =&gt; pt.Tag);\nusing (var db = new BlogDataContext())\n{\n    db.LoadOptions = options;\n    return (from p in db.Posts\n            where p.Status != PostStatus.Closed\n            orderby p.PublishDateGmt descending\n            select p);\n}\n</code></pre>\n\n<p>In the example database it would execute 4 queries which is not acceptable in production. Can anyone suggest another solution?</p>\n\n<p>Thanks</p>\n"}, {'answer_id': 80629, 'author': 'sirrocco', 'author_id': 5246, 'author_profile': 'https://Stackoverflow.com/users/5246', 'pm_score': 1, 'selected': False, 'text': "<p>It's a bit strange because </p>\n\n<pre><code>DataLoadOptions o = new DataLoadOptions ( );\no.LoadWith&lt;Listing&gt; ( l =&gt; l.ListingStaffs );\no.LoadWith&lt;ListingStaff&gt; ( ls =&gt; ls.MerchantStaff );\nctx.LoadOptions = o;\n\nIQueryable&lt;Listing&gt; listings = (from a in ctx.Listings\n            where a.IsActive == false \n                            select a);\nList&lt;Listing&gt; list = listings.ToList ( );\n</code></pre>\n\n<p>results in a query like : </p>\n\n<pre><code>SELECT [t0].*, [t1].*, [t2].*, (\nSELECT COUNT(*)\nFROM [dbo].[LStaff] AS [t3]\nINNER JOIN [dbo].[MStaff] AS [t4] ON [t4].[MStaffId] = [t3].[MStaffId]\nWHERE [t3].[ListingId] = [t0].[ListingId]\n) AS [value]\nFROM [dbo].[Listing] AS [t0]\nLEFT OUTER JOIN ([dbo].[LStaff] AS [t1]\nINNER JOIN [dbo].[MStaff] AS [t2] ON [t2].[MStaffId] = [t1].[MStaffId]) ON \n[t1].[LId] = [t0].[LId] WHERE NOT ([t0].[IsActive] = 1) \nORDER BY [t0].[LId], [t1].[LStaffId], [t2].[MStaffId]\n</code></pre>\n\n<p>(I've shortened the names and added the * on the select).</p>\n\n<p>So it seems to do the select ok.</p>\n"}, {'answer_id': 83396, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 1, 'selected': False, 'text': "<p>I'm sorry. The solution you give works, but I found out that it breaks when paginating with Take(N). The complete method I'm using is the following:</p>\n\n<pre><code>public IList&lt;Post&gt; GetPosts(int page, int records)\n{\n    var options = new DataLoadOptions();\n    options.LoadWith&lt;Post&gt;(p =&gt; p.PostTags);\n    options.LoadWith&lt;PostTag&gt;(pt =&gt; pt.Tag);\n    using (var db = new BlogDataContext())\n    {\n        db.LoadOptions = options;\n        return (from p in db.Posts\n                where p.Status != PostStatus.Closed\n                orderby p.PublishDateGmt descending\n                select p)\n                .Skip(page * records)\n                //.Take(records)\n                .ToList();\n    }\n}\n</code></pre>\n\n<p>With the Take() method commented it generates a query similar to to what you posted but if I add the Take() again it generates 1 + N x M queries.</p>\n\n<p>So, I guess my question now is: <strong>Is there a replacement to the Take() method to paginate records?</strong></p>\n\n<p>Thanks</p>\n"}, {'answer_id': 83771, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 2, 'selected': False, 'text': "<p>Yay! It worked. </p>\n\n<p>If anyone is having the same problem here's what I did:</p>\n\n<pre><code>public IList&lt;Post&gt; GetPosts(int page, int record)\n{\n    var options = new DataLoadOptions();\n    options.LoadWith&lt;Post&gt;(p =&gt; p.PostTags);\n    options.LoadWith&lt;PostTag&gt;(pt =&gt; pt.Tag);\n    using (var db = new DatabaseDataContext(m_connectionString))\n    {\n        var publishDateGmt = (from p in db.Posts\n                              where p.Status != PostStatus.Hidden\n                              orderby p.PublishDateGmt descending\n                              select p.PublishDateGmt)\n                              .Skip(page * record)\n                              .Take(record)\n                              .ToList()\n                              .Last();\n        db.LoadOptions = options;\n        return (from p in db.Posts\n                where p.Status != PostStatus.Closed \n                    &amp;&amp; p.PublishDateGmt &gt;= publishDateGmt\n                orderby p.PublishDateGmt descending\n                select p)\n                .Skip(page * record)\n                .ToList();\n    }\n}\n</code></pre>\n\n<p>This executes only two queries and loads all tags for each post.</p>\n\n<p>The idea is to get some value to limit the query at the last post that we need (in this case the PublishDateGmt column will suffice) and then limit the second query with that value instead of Take().</p>\n\n<p>Thanks for your help sirrocco.</p>\n"}, {'answer_id': 3980733, 'author': 'jordanbtucker', 'author_id': 164430, 'author_profile': 'https://Stackoverflow.com/users/164430', 'pm_score': 0, 'selected': False, 'text': '<p>I know this is an old post, but I have discovered a way to use Take() while only performing one query. The trick is to perform the Take() inside of a nested query.</p>\n\n<pre><code>var q = from p in db.Posts\n        where db.Posts.Take(10).Contains(p)\n        select p;\n</code></pre>\n\n<p>Using DataLoadOptions with the query above will give you the first ten posts, including their associated tags, all in one query. The resulting SQL will be a much less concise version of the following:</p>\n\n<pre><code>SELECT p.PostID, p.Title, pt.PostID, pt.TagID, t.TagID, t.Name FROM Posts p\nJOIN PostsTags pt ON p.PostID = pt.PostID\nJOIN Tags t ON pt.TagID = t.TagID\nWHERE p.PostID IN (SELECT TOP 10 PostID FROM Posts)\n</code></pre>\n'}]
['https://Stackoverflow.com/questions/79939', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/-1/']