Edit

Share via

Remote vs. Local Execution

  • Article

You can decide to execute your queries either remotely (that is, the database engine executes the query against the database) or locally (LINQ to SQL executes the query against a local cache).

Remote Execution

Consider the following query:

            Northwnd db = new Northwnd(@"northwnd.mdf");
            Customer c = db.Customers.Single(x => x.CustomerID == "19283");
foreach (Order ord in
    c.Orders.Where(o => o.ShippedDate.Value.Year == 1998))
{
    // Do something.
}

    Dim db As New Northwnd("c:\northwnd.mdf")
    Dim c As Customer = _
(From cust In db.Customers _
 Where cust.CustomerID = 19283).First()
    Dim orders = From ord In c.Orders _
                 Where ord.ShippedDate.Value.Year = 1998
    For Each nextOrder In orders
        ' Do something.
    Next

If your database has thousands of rows of orders, you do not want to retrieve them all to process a small subset. In LINQ to SQL, the EntitySet<TEntity> class implements the IQueryable interface. This approach makes sure that such queries can be executed remotely. Two major benefits flow from this technique:

  • Unnecessary data is not retrieved.

  • A query executed by the database engine is often more efficient because of the database indexes.

Local Execution

In other situations, you might want to have the complete set of related entities in the local cache. For this purpose, EntitySet<TEntity> provides the Load method to explicitly load all the members of the EntitySet<TEntity>.

If an EntitySet<TEntity> is already loaded, subsequent queries are executed locally. This approach helps in two ways:

  • If the complete set must be used locally or multiple times, you can avoid remote queries and associated latencies.

  • The entity can be serialized as a complete entity.

The following code fragment illustrates how local execution can be obtained:

            Northwnd db = new Northwnd(@"northwnd.mdf");
            Customer c = db.Customers.Single(x => x.CustomerID == "19283");
c.Orders.Load();

foreach (Order ord in
    c.Orders.Where(o => o.ShippedDate.Value.Year == 1998))
{
    // Do something.
}
        }

    Dim db As New Northwnd("c:\northwnd.mdf")
    Dim c As Customer = _
(From cust In db.Customers _
 Where cust.CustomerID = 19283).First
    c.Orders.Load()

    Dim orders = From ord In c.Orders _
                 Where ord.ShippedDate.Value.Year = 1998

    For Each nextOrder In orders
        ' Do something.
    Next

Comparison

These two capabilities provide a powerful combination of options: remote execution for large collections and local execution for small collections or where the complete collection is needed. You implement remote execution through IQueryable, and local execution against an in-memory IEnumerable<T> collection. To force local execution (that is, IEnumerable<T>), see Convert a Type to a Generic IEnumerable.

Queries Against Unordered Sets

Note the important difference between a local collection that implements List<T> and a collection that provides remote queries executed against unordered sets in a relational database. List<T> methods such as those that use index values require list semantics, which typically cannot be obtained through a remote query against an unordered set. For this reason, such methods implicitly load the EntitySet<TEntity> to allow local execution.

See also