title: Dynamic query with linq to entities using PredicateBuilder
body: <p>I'm trying to run a query on a very simple table but I'm not getting to any result. The table contains an action log with the following columns: </p>
  
  <ul>
  <li>ID</li>
  <li>ActionTypeID (create, edit or delete)</li>
  <li>EntryID (the id of the created/edited/or deleted entry)</li>
  <li>TableName (where the action was committed)</li>
  <li>ControlName</li>
  <li>Person (who committed the action. nt-user with department)</li>
  <li>Date</li>
  </ul>
  
  <p>What I wanted to get is a list of all EntryIDs with ActionTypeID == 1 and Person (department) ending with one of a dynamic list of strings. The Problem is that only the first action for one EntryID in the action log should be checked to match one of these departments.</p>
  
  <p>I know this sounds pretty weird. This may result from my bad english and from the database designing disability of my former colleague. I don't know which is worse.</p>
  
  <p>My attempt to get the result I wanted is this:</p>
  
  <pre><code>var predicate = PredicateBuilder.False&lt;ActionLog&gt;();
  var pOuter = PredicateBuilder.True&lt;ActionLog&gt;();
  
  pOuter = pOuter.And(h =&gt; h.TableName.ToUpper() == "Contact".ToUpper());
  pOuter = pOuter.And(h =&gt; h.ActionType.ID == 1);
  
  foreach (var dep in b.DepartmentList)
  {
      predicate = predicate.Or(x =&gt; x.Person.EndsWith("/" + dep.Value));
  }
  
  pOuter = pOuter.And(predicate.Expand());
  
  mContactIDs = (from h in db.ActionLog
  orderby h.Date
  select h).AsExpandable().Where(pOuter).Select(x =&gt; x.EntryID).Distinct().ToList();
  </code></pre>
  
  <p>But this returns me all entries where the 'Person' ends with the selected departments. I just want to check the first entry with this EntryID.</p>
  
  <p>I hope anyone understands what I'm trying to translate from confused german to weird english.</p>
  
  <p>oh- by the way, it's .Net 3.5</p>
  
  <p><strong>EDIT</strong>
  I think I have some problems finding the right explanation - I'll try it with some dummy data.</p>
  
  <pre><code>ID | ActionTypeID | EntryID | TableName  | ControlName | Person             | Date
  ----------------------------------------------------------------------------------------
  1  | 1            | 3       | 'Contacts' | NULL        | 'xxxx/department1' | 04/11/2012
  2  | 1            | 3       | 'Contacts' | NULL        | 'yyyy/department2' | 04/11/2012
  3  | 1            | 5       | 'Contacts' | NULL        | 'yyyy/department2' | 04/13/2012
  4  | 1            | 14      | 'Contacts' | NULL        | 'zzzz/department1' | 04/16/2012
  </code></pre>
  
  <p>In my example i would be searching for log entries with "Person ends with '/department2' created the first occurrence of a new EntryID", "TableName == 'Contacts'" and "ActionTypeID == 1". The result I want to receive would be (only EntryIDs) 5, as this is the only entry where a user of department2 has been the first one Inserting this EntryID with ActionTypeID 1. I know this is stupid and if I had designed the database I wouldn't have done it this way, but now I have to deal with it. If the quantity of departments to query would not be dynamic I would use this snippet:</p>
  
  <pre><code>IQueryable&lt;ActionLog&gt; log = db.ActionLog;
  mContactIDs = (from k in db.Contacts
      where (from h in log
          where h.TableName == "Contacts"
          &amp;&amp; h.EntryID == k.ID
          &amp;&amp; h.ActionType.ID == 1
          orderby h.Date
          select h).FirstOrDefault().Person.EndsWith("/" + b.Department)
      select k.ID).ToList();
  </code></pre>
  
  <p>But I don't know how to connect those dynamic departments with the stupid design (and multiple ActionTypeID 1 (create) for one entry. I don't even know why the application saves this)</p>
  
  <p>Thank you, Ben.</p>