加速 LinqDataSource

发布于 2024-10-20 08:43:58 字数 708 浏览 4 评论 0原文

您好，我有以下问题。假设数据库中有两个表：

Clients(
  id,
  name,
  address)

Orders(
  id,
  name,
  desc,
  datemodified,
  client_id)

第二个表引用第一个表，即每个订单都分配给客户。现在假设我有一个 .aspx 页面，其中包含用于订单表的 LinqDataSource，以及一个使用此数据源并显示包含以下列的表的 GridView：

订单名称。
订单说明
客户名称。
客户地址。

据我了解，Linq to SQL 的设计方式是这样的，默认情况下它不加载任何关联实体，仅在请求子属性时才加载。因此，当加载页面时，将发生以下情况：

第一个查询将从 Orders 表中检索记录。
对于 GridView 显示的每一行，当请求其中一个客户端属性时，将执行附加查询。

因此，如果我们有 100 个订单，这意味着将执行 101 个查询，而不是 1 个（或者甚至可能是 201 个，如果将为每个客户端属性执行查询）？如何避免这种情况并让 LinqDataSource 通过单个查询加载所有必需的字段？

现在我看到这个问题的唯一解决方法 - 使用 SqlDataSource 和联接查询，它将立即检索所有必需的字段。

原文

Greetings, I have a following question. Suppose where are two tables in the database:

Clients(
  id,
  name,
  address)

Orders(
  id,
  name,
  desc,
  datemodified,
  client_id)

The second one references the first one, that is each order is assigned to the client. Now suppose I have an .aspx page with a LinqDataSource for Orders table, and a GridView that uses this datasource and displays a table with a following columns:

Order name.
Order desc.
Client name.
Client address.

As far as I understand, the Linq to SQL is designed in such a way, that by default it does not load any associated entities, it only does it when a child property is requested. So, when a page is loaded, the following situation will occur:

First query will retrieve the records from the Orders table.
For each row displayed by GridView an additional query will be performed when one of the client properties is requested.

Therefore, if we have 100 orders, this means will perform 101 queries instead of one (or even maybe 201, if a query will be performed for each client property)? How to avoid this and make LinqDataSource load all the required fields by a single query?

Right now I see the only workaround for this problem - use an SqlDataSource with a join query, that will retrieve all required fields at once.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

流年里的时光 2024-10-27 08:43:58

在 LinqDataSource.ContextCreated 事件中设置 LoadOptions

看看这里如何做
http://codebetter. com/davidhayden/2007/08/06/linq-to-sql-query-tuning-for-performance-optimization/

回复收藏 0 原文

我不吻晚风 2024-10-27 08:43:58

您可以重写 LinqDataSource 的 OnSelecting 事件，让它检索包含您正在查找的确切数据的匿名类型：

protected void LinqDataSource_OnSelecting(object sender, LinqDataSourceSelectEventArgs e)
{
    DataContext dc = new DataContext();
    var query = from o in dc.Orders
                select new
                {
                    OrderName = o.name,
                    OrderDesc = o.desc,
                    ClientName = o.Client.name,
                    ClientAddress = o.Client.address
                };
    // Insert any necessary conditional statements adjustments as needed.

    e.Result = query;
}

它将发送到数据库的查询将仅获取这四个字段，并使用内部联接来执行此操作，因此您只会为每行检索一次数据。

You could override the OnSelecting event of the LinqDataSource to have it retrieve an anonymous type with the exact data you are looking for:

protected void LinqDataSource_OnSelecting(object sender, LinqDataSourceSelectEventArgs e)
{
    DataContext dc = new DataContext();
    var query = from o in dc.Orders
                select new
                {
                    OrderName = o.name,
                    OrderDesc = o.desc,
                    ClientName = o.Client.name,
                    ClientAddress = o.Client.address
                };
    // Insert any necessary conditional statements adjustments as needed.

    e.Result = query;
}

The query it will send to the database will grab only those four fields, and do it with an inner join, so you'll only retrieve data once for each row.

回复收藏 0 原文

~没有更多了~