Remote vs. Local Query Execution (LINQ to SQL)
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:
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
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.
}
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:
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
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.
}
}
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 How to: Convert a Type to a Generic IEnumerable (LINQ to SQL).
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.