title: Linq to SQL - Joins and Skip+Take
body: <p>I have Linq-to-SQL code that works with a many-to-many relationship, but note that the relationship itself has its own set of attributes (in this case, Products are in Many Categories, and each product-in-category relation has its own SortOrder attribute).</p>
  
  <p>I have a Linq-to-SQL block that returns matching Products with Category membership information. When I execute the code it generates optimised T-SQL code like so:</p>
  
  <pre><code>exec sp_executesql N'SELECT [t0].[ProductId], [t0].[Name], [t1].[ProductId] AS [ProductId2], [t1].[CategoryId], [t1].[SortOrder] AS [SortOrder2], [t2].[CategoryId] AS [CategoryId2], [t2].[Name] AS [Name2] (
  SELECT COUNT(*)
  FROM [dbo].[ProductsInCategories] AS [t3]
  INNER JOIN [dbo].[Categories] AS [t4] ON [t4].[CategoryId] = [t3].[CategoryId]
  WHERE [t3].[ProductId] = [t0].[ProductId]
  ) AS [value]
  FROM [dbo].[Products] AS [t0]
  LEFT OUTER JOIN ([dbo].[ProductsInCategories] AS [t1]
  INNER JOIN [dbo].[Categories] AS [t2] ON [t2].[CategoryId] = [t1].[CategoryId]) ON [t1].[ProductId] = [t0].[ProductId]
  WHERE (([t0].[OwnerId]) = @p0) AND ([t0].[Visible] = 1)
  ORDER BY [t0].[SortOrder], [t0].[Name], [t0].[ProductId], [t1].[CategoryId]',N'@p0 bigint',@p0=3
  </code></pre>
  
  <p>However, when I add paging instructions (i.e.".Skip(0).Take(50)") to the Linq expression the generated SQL becomes this:</p>
  
  <pre><code>exec sp_executesql N'SELECT TOP (50) [t0].[ProductId], [t0].[Name]
  FROM [dbo].[Products] AS [t0]
  WHERE (([t0].[OwnerId]) = @p0) AND ([t0].[Visible] = 1)
  ORDER BY [t0].[SortOrder], [t0].[Name]',N'@p0 bigint',@p0=3
  </code></pre>
  
  <p>Which means the Category membership information isn't loaded anymore, so Linq-to-SQL then executes the manual loading code 50 times over (one for each member in the returned set):</p>
  
  <pre><code>exec sp_executesql N'SELECT [t0].[ProductId], [t0].[CategoryId], [t0].[SortOrder], [t1].[CategoryId] AS [CategoryId2], [t1].[Name]
  FROM [dbo].[ProductsInCategories] AS [t0]
  INNER JOIN [dbo].[Categories] AS [t1] ON [t1].[CategoryId] = [t0].[CategoryId]
  WHERE [t0].[ProductId] = @x1',N'@x1 bigint',@x1=1141
  </code></pre>
  
  <p>(obviously the "@x1" ID parameter varies for each result from the original query).</p>
  
  <p>So clearly Linq paging breaks the query and causes it to load data separately. Is there a way around this or should I do paging in my own software?</p>
  
  <p>...fortunately the number of products in the database is small enough (&lt;500) to do this, but it just feels dirty because there <strong>could</strong> be tens of thousands of products, and this just wouldn't be a good query.</p>
  
  <p>EDIT:</p>
  
  <p>Here is my Linq:</p>
  
  <pre><code>DataLoadOptions dlo = new DataLoadOptions();
  dlo.LoadWith&lt;Product&gt;( p =&gt; p.ProductsInCategories );
  dlo.LoadWith&lt;ProductsInCategory&gt;( pic =&gt; pic.Category );
  this.LoadOptions = dlo;
  
  query = from p in this.Products
  select p;
  
  // The lines below are added conditionally:
  query = query.OrderBy( p =&gt; p.SortOrder ).ThenBy( p =&gt; p.Name );
  query = query.Where( p =&gt; p.Visible );
  query = query.Where( p =&gt; p.Name.Contains( filter ) || p.Description.Contains( filter ) );
  query = query.Where( p =&gt; p.OwnerId == siteId );
  </code></pre>
  
  <p>The skip/take lines are added optionally, and are the only differences that cause the different T-SQL generation (as far as I know):</p>
  
  <pre><code>IQueryable&lt;Product&gt; query = GetProducts( siteId, category, filter, showHidden, sortBySortOrder );
  
  ///////////////////////////////////
  
  total = query.Count();
  
  var pagedProducts = query.Skip( pageIndex * pageSize ).Take( pageSize );
  return pagedProducts;
  </code></pre>