title: How to use Linq or Lambda to join 1-to-many tables and project flattened results into an anonymous type
body: <p>I have 3 tables linked by Foreign Keys:  ChangeSet, ObjectChanges, PropertyChanges.  These tables have 1-To-Many Relationships with each other.  I need to join and project and flatten the results into an anonymous type.</p>
  
  <p>We use Entity Framework at the data layer and I essentially need to make the following Query with linq.</p>
  
  <pre><code>select c.Id as ChangeSetId,
  c.Timestamp,
  c.Author_Id,
  u.Name as [User],
  o.id as ObjectChangeId,
  o.TypeName,
  o.ObjectReference as EntityId,
  o.DisplayName,
  p.Id as PropertyChangeId,
  p.PropertyName, 
  p.ChangeType,
  p.OriginalValue, 
  p.Value  
  from ChangeSets c
  inner join ObjectChanges o
      on c.Id = o.ChangeSetId
  left join PropertyChanges p
      on p.ObjectChangeId = o.Id
  inner join Users u
      on u.Id = c.Author_Id
  order by c.id desc
  </code></pre>
  
  <p>The Method in question however looks like this:</p>
  
  <pre><code>GetAllWhereExpression(Expression&lt;Func&lt;ChangeSet, bool&gt;&gt; expression)
  </code></pre>
  
  <p>The expression in this case is likely to be a Where o.EntityId = [Some Value] and c.TimeStamp > X and &lt; Y.</p>
  
  <p>I got very close I felt in linq with the following but couldn't figure out how to inject the expression: (The .GetRepository().Entities is basically DbSet)</p>
  
  <pre><code>var foo = from c in _uow.GetRepository&lt;ChangeSet&gt;().Entities
          join o in _uow.GetRepository&lt;ObjectChange&gt;().Entities on c.Id equals o.ChangeSetId
          join p in _uow.GetRepository&lt;PropertyChange&gt;().Entities on o.Id equals p.ObjectChangeId
          where expression // This Line Not Valid
          select new
          {
              ChangeSetId = c.Id,
              Timestamp = c.Timestamp,
              User = c.User.DisplayName,  
              EntityType = o.TypeName,
              EntityValue = o.DisplayName,
              Property = p.PropertyName,
              OldValue = p.OriginalValue,
              NewValue = p.Value
          };
  </code></pre>
  
  <p>I'd prefer to use Lambda syntax but I can't figure out how to construct it.  I know I need SelectMany to project and flatten the results but I can't figure out how to use them within the anonymous type for the subcollections:</p>
  
  <pre><code>var queryable = _uow.GetRepository&lt;ChangeSet&gt;().Entities // This is basically the DbSet&lt;ChangeSet&gt;()
              .Where(expression)
              .SelectMany(c =&gt; new
              {
                  ChangeSetId = c.Id,
                  Timestamp = c.Timestamp,
                  User = c.User.DisplayName,  
                  EntityType = c.ObjectChanges.SelectMany(o =&gt; o.TypeName), //Doesn't work, turns string into char array
                  //PropertyName = c. this would be a 1 to many on the entity
              }
                  )
  </code></pre>
  
  <p>How do I craft the linq to produce basically the same results as the sql query?</p>