<p>I have a query that looks like this:</p>

<pre><code>public IList&lt;Post&gt; FetchLatestOrders(int pageIndex, int recordCount)
{
    DatabaseDataContext db = new DatabaseDataContext();
    return (from o in db.Orders
            orderby o.CreatedDate descending
            select o)
            .Skip(pageIndex * recordCount)
            .Take(recordCount)
            .ToList();
}
</code></pre>

<p>I need to print the information of the order and the user who created it:</p>

<pre><code>foreach (var o in FetchLatestOrders(0, 10))
{
    Console.WriteLine("{0} {1}", o.Code, o.Customer.Name);
}
</code></pre>

<p>This produces a SQL query to bring the orders and one query for each order to bring the customer. Is it possible to optimize the query so that it brings the orders and it's customer in one SQL query?</p>

<p>Thanks</p>

<p>UDPATE: By suggestion of sirrocco I changed the query like this and it works. Only one select query is generated:</p>

<pre><code>public IList&lt;Post&gt; FetchLatestOrders(int pageIndex, int recordCount)
{
    var options = new DataLoadOptions();
    options.LoadWith&lt;Post&gt;(o =&gt; o.Customer);
    using (var db = new DatabaseDataContext())
    {
        db.LoadOptions = options;
        return (from o in db.Orders
                orderby o.CreatedDate descending
                select o)
                .Skip(pageIndex * recordCount)
                .Take(recordCount)
                .ToList();
    }
}
</code></pre>

<p>Thanks sirrocco.</p>

[{'answer_id': 50191, 'author': 'John Boker', 'author_id': 2847, 'author_profile': 'https://Stackoverflow.com/users/2847', 'pm_score': 0, 'selected': False, 'text': '<p>you might want to look into using compiled queries</p>\n\n<p>have a look at <a href="http://www.3devs.com/?p=3" rel="nofollow noreferrer">http://www.3devs.com/?p=3</a></p>\n'}, {'answer_id': 50240, 'author': 'sirrocco', 'author_id': 5246, 'author_profile': 'https://Stackoverflow.com/users/5246', 'pm_score': 3, 'selected': True, 'text': '<p>Something else you can do is EagerLoading. In Linq2SQL you can use LoadOptions : <a href="http://oakleafblog.blogspot.com/2007/08/linq-to-sql-query-execution-with.html" rel="nofollow noreferrer">More on LoadOptions</a>\nOne VERY weird thing about L2S is that you can set LoadOptions only before the first query is sent to the Database.</p>\n'}, {'answer_id': 56966505, 'author': 'Bharat Kumar', 'author_id': 3471489, 'author_profile': 'https://Stackoverflow.com/users/3471489', 'pm_score': 0, 'selected': False, 'text': "<p>Given a LINQ statement like:</p>\n\n<pre><code>context.Cars\n  .OrderBy(x =&gt; x.Id)\n  .Skip(50000)\n  .Take(1000)\n  .ToList();\n</code></pre>\n\n<p>This roughly gets translated into:</p>\n\n<pre><code>select * from [Cars] order by [Cars].[Id] asc offset 50000 rows fetch next 1000 rows\n</code></pre>\n\n<p>Because offset and fetch are extensions of order by, they are not executed until after the select-portion runs (google). This means an expensive select with lots of join-statements are executed on the whole dataset ([Cars]) prior to getting the fetched-results.</p>\n\n<p><strong>Optimize the statement</strong>\nAll that is needed is taking the OrderBy, Skip, and Take statements and putting them into a Where-clause:</p>\n\n<pre><code>context.Cars\n  .Where(x =&gt; context.Cars.OrderBy(y =&gt; y.Id).Select(y =&gt; y.Id).Skip(50000).Take(1000).Contains(x.Id))\n  .ToList();\n</code></pre>\n\n<p>This roughly gets translated into:</p>\n\n<pre><code>exec sp_executesql N'\nselect * from [Cars]\nwhere exists\n  (select 1 from\n    (select [Cars].[Id] from [Cars] order by [Cars].[Id] asc offset @p__linq__0 rows fetch next @p__linq__1 rows only\n    ) as [Limit1]\n    where [Limit1].[Id] = [Cars].[Id]\n  )\norder by [Cars].[Id] asc',N'@p__linq__0 int,@p__linq__1 int',@p__linq__0=50000,@p__linq__1=1000\n</code></pre>\n\n<p>So now, the outer select-statement only executes on the filtered dataset based on the where exists-clause!</p>\n\n<p>Again, your mileage may vary on how much query time is saved by making the change. General rule of thumb is the more complex your select-statement and the deeper into the dataset you want to go, the more this optimization will help.</p>\n"}]
['https://Stackoverflow.com/questions/50169', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/4269/']