Speeding up LinqDataSource

Question

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.

score 0 · Answer 1 · answered Mar 04 '11 at 18:47

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.

Will this affect the update and delete operations for the grid view? If I switch the datasource in such a way, how will LinqDataSource execute the update and delete requests? — SPIRiT_1984, Mar 05 '11 at 06:02

score 0 · Accepted Answer · answered Mar 06 '11 at 16:40

0

Set the LoadOptions in the LinqDataSource.ContextCreated Event

Have a look here how to do it http://codebetter.com/davidhayden/2007/08/06/linq-to-sql-query-tuning-for-performance-optimization/

answered Mar 06 '11 at 16:40

Pleun

8,856
2
30
50

Speeding up LinqDataSource

2 Answers2