title: Linq to Entities : Left Join and filter
body: <p>I have 3 tables : </p>
  
  <ul>
  <li>USERS_TABLE(userid, name) </li>
  <li>ACTIVITY_HISTORY_TABLE(userid, datefrom,dateto,activityId)</li>
  <li>ACTIVITY_TABLE (activityId, name) </li>
  </ul>
  
  <p>In EF these are the below entities coresponding to the tables</p>
  
  <ul>
  <li>User(UserId,Name, AssignedActivities) AssignedActivities beeing the navigation property</li>
  <li>ActivityHistory (UserId, DateFrom,DateTo,activityId,TheActivity) TheActivity being the navigation property</li>
  <li>Activity (ActivityId, Name)</li>
  </ul>
  
  <p>I need to return a List with all my users including the activities they are doing at the time of the requests (ActivityHistory.Datefrom &lt;= now and ActivityHistory.DateTo >= now).</p>
  
  <p>Users with no current activity are also to be returned.</p>
  
  <p>Users that have multiple activities must not be duplicated</p>
  
  <p>I need "User.AssignedActivities" and "ActivityHistory.TheActivity" to be loaded (no lazy loading).</p>
  
  <p>So far I have 4 solutions that all fail one of my needs</p>
  
  <p>1 - Only returns users that have activities at the given time and not all of the users</p>
  
  <pre><code>public List&lt;User&gt; GetAll()
  {
    using (Entities _db = new Entities())
    {
      return _db.USERS_TABLE
         .Include("ActivityHistory")
         .Include("ActivityHistory.Activity")
         .Where(x =&gt; x.ActivityHistory.Any(y =&gt; y.DateFrom &lt;= DateTime.Now &amp;&amp; y.DateTo &gt;= DateTime.Now))
         .ToList();    
     }
  }
  </code></pre>
  
  <p>2 - Returns the correct list of Users (complete with no duplicates) but activities are not loaded  (System.ObjectDisposedException when manipulating User.AssignedActivities in the returned list)</p>
  
  <p>(solution seen here : <a href="https://stackoverflow.com/questions/1535443/ef-query-with-conditional-include">EF Query With Conditional Include</a>)</p>
  
  <pre><code>public List&lt;User&gt; GetAll()
  {
    using (Entities _db = new Entities())
    {
      var query = from users in _db.USERS_TABLE
                  select new
                  {
                      users,
                  assignments = from ActivityAssigned in users.AssignedActivities
                                .Where(y =&gt; y.DateFrom &lt;= now &amp;&amp; y.DateTo &gt;= now)
                                .DefaultIfEmpty()
                                select ActivityAssigned
              };
  
  var query2 = query
      .AsEnumerable()
      .Select(x =&gt; x.users);
  
  return query2.ToList();
     }
  }
  </code></pre>
  
  <p>3 - Returns duplicates when users have many activities and the activies are not loaded (System.ObjectDisposedException when manipulating User.AssignedActivities in the returned list)</p>
  
  <p>(Solution from Steve Ruble here below)</p>
  
  <pre><code>public List&lt;User&gt; GetAll()
  {
    using (Entities _db = new Entities())
    {
      var query = 
       from u in _db.USERS_TABLE
       select new {
       User = u,
       CurrentActivities = u.ActivityHistory
                            .Where(y =&gt; 
                                   y.DateFrom &lt;= DateTime.Now
                                &amp;&amp; y.DateTo &gt;= DateTime.Now)
                            .Select(ah =&gt; new 
                            {
                              ActivityHistory = ah,
                              Activity = ah.Activity
                            }
       }
  
  return query.AsEnumerable().Select(x=&gt; x.User).ToList();    
  }
  }   
  </code></pre>
  
  <p>4 - Does what I want but takes forever to do it, using the repository of the ActivityHistory entity:</p>
  
  <pre><code>public List&lt;User&gt; GetAll()
  {
    using (Entities _db = new Entities())
    {
  List&lt;Users&gt; MyUsersList = new List&lt;Users&gt;();
  
  MyUsersList = _db.USERS_TABLE
      .Include("ActivityHistory")
      .Include("ActivityHistory.Activity")
      .ToList();
  
  for (int i = 0; i &lt; MyUsersList.Count(); i++)
  {
      List&lt;ActivityHistory &gt; UserCurrentActivityList = new List&lt;ActivityHistory &gt;();
  
      ActivityListRepository activityListRepo = new ActivityListRepository();
  
      UserCurrentActivityList = activityListRepo.GetUserCurrentActivity(MyUsersList[i].UserId);
  
      MyUsersList[i].AssignedActivities = UserCurrentActivityList;
  }
  
  return MyUsersList; 
     }
  }               
  </code></pre>
  
  <p>Help :) !</p>