优化 LINQ to SQL 查询 [英] Optimizing a LINQ to SQL query

问题描述

我有一个类似这样的查询:

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

这将生成一个 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();
    }
}

谢谢 Sirrocco.

Thanks sirrocco.

推荐答案

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

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.

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