优化LINQ to SQL查询

问题描述

我有一个如下查询:

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);
}

这将产生一个SQL查询以带来订单，并为每个订单带来一个查询以带来客户.是否可以优化查询，以便在一个SQL查询中带来订单和客户?

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?

谢谢

UDPATE:通过sirrocco的建议，我像这样更改了查询并成功了.仅生成一个选择查询:

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

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

感谢西罗科.

推荐答案

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

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

这篇关于优化LINQ to SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！