优化LINQ to SQL查询

I have a query that looks like this:

我有一个看起来像这样的查询:

public IList<Post> 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();
}

I need to print the information of the order and the user who created it:

我需要打印订单信息和创建订单的用户:

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

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?

这将生成一个SQL查询,以便为每个订单带来订单和一个查询以带来客户。是否可以优化查询,以便将订单及其客户带入一个SQL查询?

Thanks

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

UDPATE:通过sirrocco的建议,我改变了这样的查询,它的工作原理。只生成一个选择查询:

public IList<Post> FetchLatestOrders(int pageIndex, int recordCount)
{
    var options = new DataLoadOptions();
    options.LoadWith<Post>(o => 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();
    }
}

Thanks sirrocco.

2 个解决方案

#1

Something else you can do is EagerLoading. In Linq2SQL you can use LoadOptions : More on LoadOptions One VERY weird thing about L2S is that you can set LoadOptions only before the first query is sent to the Database.

您可以做的其他事情是EagerLoading。在Linq2SQL中,您可以使用LoadOptions:有关LoadOptions的更多信息关于L2S的一个非常奇怪的事情是您只能在第一个查询发送到数据库之前设置LoadOptions。

#2

you might want to look into using compiled queries

您可能希望查看使用编译的查询

have a look at http://www.3devs.com/?p=3

看看http://www.3devs.com/?p=3

#1

#2