title: Linq self-join query
body: <p>I need some help with Linq self-join. </p>
  
  <p>I have the following classs:</p>
  
  <pre><code>public class Owner
  {
  public string OwnerId {get;set;}
  public string Name {get;set;}
  public string Area {get;set;}
  public string City {get;set;}
  public string Sex {get;set;}
  public List&lt;Dog&gt; dog {get;set;}
  }
  </code></pre>
  
  <p>And table....</p>
  
  <pre><code>ID OwnerId OwnerName TypeId TypeName   TypeValue        TypeCodeId
  1     1     John       1     Area      United States       440
  2     1     John       2     City      Los-Angeles         221
  3     1     John       3     Sex       Female              122
  4     2     Mary       1     Area      Mexico              321
  4     2     Mary       2     City      Cancun              345
  ............................................................
  </code></pre>
  
  <p>I need to parse results of table1 into list of owners the fastest way possible. 
  Note: Type can be null, but I still need to show that owner (So, I assume left join should work).</p>
  
  <p>Here's What I do. (owners is a webservice class that contains table1 results)</p>
  
  <pre><code>public IEnumerable&lt;Owner&gt; GetOwners() { 
    return  (from owner in owners 
            join area in owners into owner_area
            from oa in owner_area.DefaultIfEmpty()
            join City in owners into owner_city
            from oc in owner_city.DefaultIfEmpty()
            join sex  in owners into owner_sex
            from os in owner_sex.DefaultIfEmpty()
            where oa.TypeId == 1 &amp;&amp; oc.TypeId ==2 &amp;&amp; os.TypeId ==3
            select new Owner() {OwnerId = owner.OwnerId, 
                                Name = owner.Name,
                                Area = oa.TypeValue,
                                City = oc.TypeValue,
                                Sex = os.TypeValue}).Distinct(); 
  }
  </code></pre>
  
  <p>This query has several issues: </p>
  
  <ol>
  <li>It returns multiple results and distinct does not work as expected</li>
  <li>I've tried to use GroupBy but it says that cannot implicitly convert Owner into      <code>IEnumerable &lt;int, Owner&gt;</code></li>
  <li>It is super slow</li>
  </ol>
  
  <p>How can I get distinct record with self join and improve performance?
  Thanks</p>
  
  <p><strong>UPDATE:</strong>
  Thank you guys for your ansewers, testing now, but I figured out that I forgot to supply one more thing. I've added a new column called TypeCodeId to the table layout(see above) 
  User can filter values based on their selection. So, I have TypeCodeId + TypeValue dictionaries for Area, City and Sex. All of those parameters are optional (If the user didn't select any, I just show them all records.</p>
  
  <p>So, Assume that the user has selected filter <code>Area: Unites States</code> and filter <code>City: Los Angeles</code></p>
  
  <p>them my query would look like this:</p>
  
  <pre><code>Select Projects where Area equals United States(440) and City equals Los Angeles(221)
  </code></pre>
  
  <p>If Only Area:Mexico was selected then my query would read something like this:</p>
  
  <pre><code>Select Projects where Area equals Mexico(321)
  </code></pre>
  
  <p>I'm not sure how to do optional where clauses with what you've provided in the examples.</p>