title: How can I join data with Linq to Entities and WCF Data Services?
body: <p>I have 4 entities that are related as follows:</p>
  
  <pre><code>LocalAgency&lt;-0..1----1-&gt;Agency&lt;-0..1----1-&gt;Organization&lt;-0..1----1-&gt;Customer
  </code></pre>
  
  <p>In other words, a <code>LocalAgency</code> has one related <code>Agency</code>, etc.  The data model is set up using <code>Entity Framework</code> (containing navigation properties to peruse those relationships), and a WCF <code>DataService</code> is set up to provide that data to clients.</p>
  
  <p>On the client side that consumes the <code>DataService</code>, I'm trying to return a query of local agencies based on a customer name, but haven't found a supported way to formulate this simple query.</p>
  
  <p>The first method I tried was using <code>Expand</code> as follows:</p>
  
  <pre><code>var items = (from i in Context.LocalAgencies.Expand("Agency").Expand("Organization").Expand("Customer")
               where (String.IsNullOrEmpty(CustomerName) || i.Agency.Organization.Customer.CustomerName.Contains(CustomerName))
               select i).Skip(StartIndex).Take(PageSize).ToList&lt;LocalAgency&gt;();
  </code></pre>
  
  <p>This approach works if the "join" is only 1 level deep, but this fails to get navigation properties of navigation properties.</p>
  
  <p>I then tried a <code>join</code> as follows:</p>
  
  <pre><code>var items = (from localAgency in Context.LocalAgencies
               join agency in Context.Agencies on localAgency.CustomerID equals agency.CustomerID
               join organization in Context.Organizations on localAgency.CustomerID equals organization.CustomerID
               join customer in Context.Customers on localAgency.CustomerID equals customer.CustomerID
               where (String.IsNullOrEmpty(CustomerName) || customer.CustomerName.Contains(CustomerName))
               select localAgency).Skip(StartIndex).Take(PageSize).ToList&lt;LocalAgency&gt;();
  </code></pre>
  
  <p>But, <code>join</code> is not supported in this instance.</p>
  
  <p>I then tried using the <code>Except</code> method as follows:</p>
  
  <pre><code>IQueryable&lt;LocalAgency&gt; items = Context.LocalAgencies;
  items = items.Except(from i in items
                       where (String.IsNullOrEmpty(CustomerName) || i.Agency.Organization.Customer.CustomerName.Contains(CustomerName))
                       select i).Skip(StartIndex).Take(PageSize);
  </code></pre>
  
  <p>But, <code>Except</code> is not supported in this instance.</p>
  
  <p>What am I missing?  Do I need to set up something on the <code>DataService</code> side to allow a simple join along defined navigation properties?</p>