# Optimizing LINQ Queries with LiveLinq

## Content

In this section, we will show a sample that illustrates how **LiveLinq** can optimize queries that use the **where** operator to filter data.

To start, follow these steps:

1. Create a new **WinForms** project
2. Add a reference to the **C1.LiveLinq.dll** assembly
3. Use the **Data \| Add New DataSource** menu and add a reference to the **NORTHWND.MDF** database. Accept all the default options offered by the wizard, and pick all the tables in the database.

At this point, your project should look like this

![](https://cdn.mescius.io/document-site-files/images/94bcede7-c342-4bf9-97a8-95fb8706d41c/optimizing_linq_queries_with_livelinq_files/image001.jpg)

Next, double-click the form and add the following code:

```auto
// declare northwind DataSet
NORTHWNDDataSet _ds = new NORTHWNDDataSet();
private void Form1_Load(object sender, EventArgs e)
{
  // load data into DataSet
  new NORTHWNDDataSetTableAdapters.CustomersTableAdapter()
    .Fill(_ds.Customers);
  new NORTHWNDDataSetTableAdapters.OrdersTableAdapter()
    .Fill(_ds.Orders);
}
```

This code declares an ADO.NET **DataSet** and loads some data into it.

Now that we have some data, let’s do something with it.

Add a button to the form, double-click it, and then enter the following code:

```auto
private void button1_Click(object sender, EventArgs e) 
{
  // get reference to source data
  var customers = _ds.Customers;
  var orders = _ds.Orders;
  // find all orders for the first customer
  var q =
    from o in orders
    where o.CustomerID == customers[0].CustomerID
    select o;
  // benchmark the query (execute 1000 times)
  var start = DateTime.Now;
  int count = 0;
  for(int i = 0; i < 1000; i++)
  {
    foreach (var d in q)
      count++;
  }
  Console.WriteLine("LINQ query done in {0} ms",
     DateTime.Now.Subtract(start).TotalMilliseconds);
}
```

The code creates a simple LINQ query that enumerates all orders for the first customer in the database, then executes the query 1000 times and reports how long the process took.

If you run the project now and click the button, the Visual Studio output window should show something like this:

**LINK query done in 262 ms**

Now let us optimize this query with **LiveLinq**.

Start by adding the following **using** statements to the top of the code:

```auto
using C1.LiveLinq;    
```

```auto
using C1.LiveLinq.AdoNet;
```

Next, add a second button to the form, double-click it and enter the following code:

```auto
private void button2_Click(object sender, EventArgs e) 
{
  // get reference to source data
  var customers = _ds.Customers;
  var orders = _ds.Orders;
  // find all orders for the first customer
  var q =
    from o in orders.AsIndexed()
    where o.CustomerID.Indexed() == customers[0].CustomerID
    select o;
  // benchmark the query (execute 1000 times)
  var start = DateTime.Now;
  int count = 0;
  for(int i = 0; i < 1000; i++)
  {
    foreach (var d in q)
    count++;
  }
  Console.WriteLine("LiveLinq query done in {0} ms",
     DateTime.Now.Subtract(start).TotalMilliseconds);
}
```

The code is almost identical to the plain LINQ version we used before. The only differences are:

* We use the **AsIndexed** method to convert the **orders** table into a **LiveLinq** indexed data source,
* We use the **Indexed** method to indicate to **LiveLinq** that it should index the data source by customer id,

AND

* The output message changed to show we are using **LiveLinq** now.

If you run the project again and click both buttons a few times, you should get something like this:

**LINQ query done in 265 ms**

**LINQ query done in 305 ms**

**LINQ query done in 278 ms**

**LiveLinq query done in 124 ms**

**LiveLinq query done in 7 ms**

**LiveLinq query done in 2 ms**

Notice how the plain LINQ query takes roughly the same amount of time whenever it executes. The **LiveLinq** query, on the other hand, is about twice as fast the first time it executes, and about **one hundred** times faster for all subsequent runs. This level of performance gain is typical for this type of query.

This happens because **LiveLinq** has to build the index when the query is executed for the first time. From then on, the same index is reused and performance improves dramatically. Note that the index is automatically maintained, so it is always up-to-date even when the underlying data changes.

In this example, the index was created when the **Indexed** method executed for the first time. You can also manage indexes using code if you need the extra control. For example, the code below declares an indexed collection and explicitly adds an index on the **CustomerID** field:

```auto
var ordersIndexed = _ds.Orders.AsIndexed();    
```

```auto
ordersIndexed.Indexes.Add(o => o.CustomerID);
```

Note that the indexes are associated with the source data, and are kept even if the collection goes out of scope. If you executed the code above once, the index would remain active even after the **ordersIndexed** collection went out of scope.

Note also that the **AsIndexed** method is supported only for collections that provide change notifications (**LiveLinq** has to monitor changes in order to maintain its indexes). This requirement is satisfied for all common collections used in WinForms and WPF data binding. In particular, **AsIndexed** can be used with **DataTable**, **DataView**, **BindingList\<T>**, **ObservableCollection\<T>**, and LINQ to XML collections.

The **LiveLinq** installation includes a sample called **LiveLinqQueries** that contains many other examples with benchmarks, and includes queries against plain CLR objects, ADO.NET, and XML data.

Summarizing, **LiveLinq** can significantly optimize queries that search for any type of data that can be sorted. For example, searching for a specific customer or product, or listing products within a certain price range. These are typical queries that can be easily optimized to perform about a hundred times faster that they would using plain LINQ.