title: Entity Framework: generate dynamic where clause and select custom columns
body: <p>I have 3 tables: <code>Person</code>, <code>PersonFriend</code>, <code>PersonGroup</code>.</p>
  
  <p>Using LINQ, i want to join the 3 tables, filter using a dynamically generated where clause, and select custom columns with flattened rows (flattened one-to-many relationship table columns).</p>
  
  <p>Pseudo-SQL design:</p>
  
  <pre><code>CREATE TABLE Person (int id, varchar socialclass, date createddate);
  CREATE TABLE Person_Friend (int id, id personid references person.id, id friendpersonid references person.id, varchar friendtype);
  CREATE TABLE Person_Group (int id, int memberid references person.id, varchar membershiplevel);
  </code></pre>
  
  <p>Entities:</p>
  
  <pre><code>public class Person
  {
      public int Id { get; set; }
  
      public string SocialClass { get; set; }
  
      public DateTime? CreatedDate { get; set; }
  
      public ICollection&lt;PersonFriend&gt; Friend { get; set; }
  
      public ICollection&lt;PersonGroup&gt; Group { get; set; }
  }
  
  public class PersonFriend
  {
      public int Id { get; set; }
  
      public int PersonId { get; set; }
  
      public int FriendPersonId { get; set; }
  
      public string FriendType { get; set; }
  }
  
  public class PersonGroup
  {
      public int Id { get; set; }
  
      public int MemberId { get; set; }
  
      public string MembershipLevel { get; set; }
  }
  </code></pre>
  
  <p>query syntax LINQ:</p>
  
  <pre><code>var queryResult = from person in _context.Person
                  join friend in _context.PersonFriend on person.Id equals friend.FriendPersonId
                  join group in _context.PersonGroup on person.Id equals group.MemberId
                  where (friend.PersonId == 1 &amp;&amp; friend.FriendType == "type1") || (friend.PersonId == 3 &amp;&amp; friend.FriendType == "type2") || ...
                  select new { person.Id, person.SocialClass, person.CreatedDate, friend.FriendPersonId, friend.FriendType, group.Id, group.MembershipLevel };
  </code></pre>
  
  <p>Notice the where clause; Given a list of <code>{ PersonId, FriendType }</code> object, I want to build the where clause like above.</p>
  
  <p>Since I could not figure building a dynamic where clause for a <code>query syntax LINQ</code>,
  I tried converting it to the <code>Method syntax LINQ</code> statement so i can leverage the <code>PredicateBuilder</code> (<a href="http://www.albahari.com/nutshell/predicatebuilder.aspx" rel="nofollow noreferrer">http://www.albahari.com/nutshell/predicatebuilder.aspx</a>) but I run into the problem during Selecting one-to-many things into a flattened object.</p>
  
  <pre><code>var methodResult = _context.Person
                  .Include(x =&gt; x.Friend)
                  .Include(x =&gt; x.Group)
                  .Select(person =&gt; new { person.Id, person.SocialClass, person.CreatedDate, person.friend.FriendPersonId, person.friend.FriendType, person.group.Id, person.group.MembershipLevel }); 
  </code></pre>
  
  <p>notice that the above Select is not possible because <code>friend</code> is a ICollection.</p>
  
  <p>I also tried using the above <code>query syntax LINQ</code> statement <strong><em>without the where clause</em></strong>, making it return a object instead of an annonymous object, and then calling the method <code>.Where()</code> with the predicate builder. But the built expression runs into <code>LINQ =&gt; Entity Framework SQL conversion error</code> and executes the <code>where</code> in the application, not in DB.</p>
  
  <pre><code>var queryResultWithoutWhere = from person in _context.Person
                  join friend in _context.PersonFriend on person.Id equals friend.FriendPersonId
                  join group in _context.PersonGroup on person.Id equals group.MemberId
                  select new SelectedObject { PersonId = person.Id, SocialClass = person.SocialClass, CreatedDate = person.CreatedDate, FriendId = friend.FriendPersonId, FriendType = friend.FriendType, GroupId = group.Id, MembershipLevel = group.MembershipLevel };
  
  
  var predicate = PredicateBuilder.New&lt;SelectedObject&gt;(false);
  
  foreach (var searchObject in searchRequestObjects)
  {
      predicate.Or(p =&gt; p.FriendPersonId == searchObject.FriendPersonId &amp;&amp; p.FriendType == searchObject.FriendType);
  }
  
  var result = queryResultWithoutWhere.Where(predicate).ToList();
  </code></pre>
  
  <p>I feel like I tried everything I could, and I cannot seem to generate this SQL. Last resort would be writing a raw SQL string and then executing it, but I really would like to get this working with Entity Framework.</p>
  
  <p>How would I accomplish creating a dynamic where clause, select into a custom flattened object, and have entity framework generate the SQL?</p>