title: How can I improve the performance of LinqToSql queries that use EntitySet properties?
body: <p>I'm using LinqToSql to query a small, simple SQL Server CE database.</p>
  
  <p>I've noticed that any operations involving sub-properties are disappointingly slow.</p>
  
  <p>For example, if I have a <code>Customer</code> table that is referenced by an <code>Order</code> table, LinqToSql will automatically create an <code>EntitySet&lt;Order&gt;</code> property. This is a nice convenience, allowing me to do things like <code>Customer.Order.Where(o =&gt; o.ProductName = "Stopwatch")</code>, but for some reason, SQL Server CE hangs up pretty bad when I try to do stuff like this. One of my queries, which isn't really that complicated takes 3-4 seconds to complete.</p>
  
  <p>I can get the speed up to acceptable, even fast, if I just grab the two tables individually and convert them to <code>List&lt;Customer&gt;</code> and <code>List&lt;Order&gt;</code>, then join then manually with my own query, but this requires a lot of extra code. LinqToSql generates these <code>EntitySet&lt;T&gt;</code> properties automatically--I'd like to use them.</p>
  
  <p>So, how can I improve the performance? For example, are there any <code>DataContext</code> options that would help? </p>
  
  <p>Note: My database in its initial state is only about 250K and I don't expect it to grow to more than 1-2Mb. So, it's not like there are a lot of records.</p>
  
  <hr>
  
  <p><strong>Update</strong></p>
  
  <p>Here are the table definitions for the example I used in my question:</p>
  
  <pre><code>create table Order
  (
      Id int identity(1, 1) primary key,
      ProductName ntext null,
      Quantity int null
  )
  
  create table Customer
  (
      Id int identity(1, 1) primary key,
      OrderId int null references Order (Id)
  )
  </code></pre>