title: Linq self join composite key
body: <p>please somebody could help me with this one.</p>
  
  <p>I have a table that needs to be joined to itself. The table contains a composite key. So far the following SQL statement works perferct.</p>
  
  <pre><code>select * from releases as a inner join
  (
  select * from releases as r1
  where id=50
  ) as x 
  on (a.ParentSeriesId = x.SeriesId and a.ParentPeriod = x.Period) OR a.id=50
  </code></pre>
  
  <p>The problem is how to translate this to linq.</p>
  
  <p>What I have come out with so far is</p>
  
  <pre><code>from a in Releases
  join x in (
           (from r1 in Releases
           where
           r1.Id == 50
           select new {
           r1
           }))
           on new { a.ParentSeriesId, a.ParentPeriod, a.Id }
    equals new { ParentSeriesId = x.r1.SeriesId, ParentPeriod = x.r1.Period, Id = 50 }
  select new{
  
  }
  </code></pre>
  
  <p>But this produces the following SQL statement</p>
  
  <pre><code>SELECT NULL AS [EMPTY]
  FROM [Releases] AS [t0]
  INNER JOIN [Releases] AS [t1] ON ([t0].[ParentSeriesId] = [t1].[SeriesId]) AND ([t0].[ParentPeriod] = [t1].[Period]) AND ([t0].[Id] = @p0)
  WHERE [t1].[Id] = @p1
  </code></pre>
  
  <p>The problem is how can I manage to make it as my original SQL statement. Thanks!!</p>