Linq to Entities : Left Join and filter

Question

I have 3 tables :

USERS_TABLE(userid, name)
ACTIVITY_HISTORY_TABLE(userid, datefrom,dateto,activityId)
ACTIVITY_TABLE (activityId, name)

In EF these are the below entities coresponding to the tables

User(UserId,Name, AssignedActivities) AssignedActivities beeing the navigation property
ActivityHistory (UserId, DateFrom,DateTo,activityId,TheActivity) TheActivity being the navigation property
Activity (ActivityId, Name)

I need to return a List with all my users including the activities they are doing at the time of the requests (ActivityHistory.Datefrom <= now and ActivityHistory.DateTo >= now).

Users with no current activity are also to be returned.

Users that have multiple activities must not be duplicated

I need "User.AssignedActivities" and "ActivityHistory.TheActivity" to be loaded (no lazy loading).

So far I have 4 solutions that all fail one of my needs

1 - Only returns users that have activities at the given time and not all of the users

public List<User> GetAll()
{
  using (Entities _db = new Entities())
  {
    return _db.USERS_TABLE
       .Include("ActivityHistory")
       .Include("ActivityHistory.Activity")
       .Where(x => x.ActivityHistory.Any(y => y.DateFrom <= DateTime.Now && y.DateTo >= DateTime.Now))
       .ToList();    
   }
}

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)

(solution seen here : EF Query With Conditional Include)

public List<User> 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 => y.DateFrom <= now && y.DateTo >= now)
                              .DefaultIfEmpty()
                              select ActivityAssigned
            };

var query2 = query
    .AsEnumerable()
    .Select(x => x.users);

return query2.ToList();
   }
}

3 - Returns duplicates when users have many activities and the activies are not loaded (System.ObjectDisposedException when manipulating User.AssignedActivities in the returned list)

(Solution from Steve Ruble here below)

public List<User> GetAll()
{
  using (Entities _db = new Entities())
  {
    var query = 
     from u in _db.USERS_TABLE
     select new {
     User = u,
     CurrentActivities = u.ActivityHistory
                          .Where(y => 
                                 y.DateFrom <= DateTime.Now
                              && y.DateTo >= DateTime.Now)
                          .Select(ah => new 
                          {
                            ActivityHistory = ah,
                            Activity = ah.Activity
                          }
     }

return query.AsEnumerable().Select(x=> x.User).ToList();    
}
}

4 - Does what I want but takes forever to do it, using the repository of the ActivityHistory entity:

public List<User> GetAll()
{
  using (Entities _db = new Entities())
  {
List<Users> MyUsersList = new List<Users>();

MyUsersList = _db.USERS_TABLE
    .Include("ActivityHistory")
    .Include("ActivityHistory.Activity")
    .ToList();

for (int i = 0; i < MyUsersList.Count(); i++)
{
    List<ActivityHistory > UserCurrentActivityList = new List<ActivityHistory >();

    ActivityListRepository activityListRepo = new ActivityListRepository();

    UserCurrentActivityList = activityListRepo.GetUserCurrentActivity(MyUsersList[i].UserId);

    MyUsersList[i].AssignedActivities = UserCurrentActivityList;
}

return MyUsersList; 
   }
}

Help :) !

Aron · Answer 1 · 2013-09-26T15:25:13.143

_db.Database.EnableLazyLoading = false;
_db.Database.UseProxyObjects = false;
var now = DateTime.Now; //DON'T REFACTOR THIS LINE!!!
var query = from user in _db.USERS_TABLE
            from activity in user.ACTIVITY_HISTORY
            group activity by user into g
            select new {
               User = g.Key,
               CurrentActivities = g.Where(activity =>
                           activity.DateFrom <= now && activity.DateTo >= now)
            };
return query.ToList().Select(x => x.User).ToList();

Edit: Fixed...I hope. However I would advise against using this query. Its hugely hacky. I STRESS that if you do want to have a lookup for CurrentActivities I would use a data structure to store that.

The fact that this works is held together by some nuances of how EF works. It is hugely bad practice and YOU WILL end up with a lot of problems down the line.

well i get an issue on Where(activity => activity.DateFrom <= now && activity.DateTo >= now) stating that System.Collections.Generic.Icollection<...THEENTITY> does not contain a definition for 'Datefrom" ... thought it does. — DonQi, Sep 26 '13 at 15:07
And also i need to return a List which is the entity that coresponds to my USERS_TABLE. but thanks anyway!! :) — DonQi, Sep 26 '13 at 15:12

Steve Ruble · Answer 2 · 2013-10-01T13:31:46.400

If what you want is a list of all users, with the current activity or null for each user, this should do it. The trick is in the DefaultOrEmpty, which gets translated into a LEFT OUTER JOIN on the SQL side.

from u in _db.USERS_TABLE
from aht in u.ActivityHistory
             .Where(y => y.DateFrom <= DateTime.Now && y.DateTo >= DateTime.Now)
             .DefaultIfEmpty()
select new {
    User = u,
    CurrentActivityHistory = ah
    CurrentActivity = ah.Activity
}

EDIT

If what you want is a list of all users, with all the current ActivityHistory records, and with the activity eagerly loaded for each ActivityHistory, you can do this:

var query = 
 from u in _db.USERS_TABLE
 select new {
     User = u,
     CurrentActivities = u.ActivityHistory
                          .Where(y => 
                                 y.DateFrom <= DateTime.Now
                              && y.DateTo >= DateTime.Now)
                          .Select(ah => new 
                          {
                            ActivityHistory = ah,
                            Activity = ah.Activity
                          }
 }

Note concerning the materialized entities:

Consider the value of this variable:

var record = query.First();

The property record.User.ActivityHistory will be populated with a collection containing the same ActivityHistory objects as record.CurrentActivities, even if the user has many more non-current ActivityHistory records in the database. In other words, if you need to get all the ActivityHistory records for a User, you will need to make another query, rather than relying on lazy-loading to pull them in.

The issue here is that when a user has 2 "activities" at the same time then i get a duplicate. What i need is to be able to list all my users and access user.ActivityHistory and ActivityHistory.Activity for the current activities. — DonQi, Oct 01 '13 at 08:11
I need to eagerly load everything. I'm also having an error on the .include "'System.Collections.Generic.ICollection' does not contain a definition for 'Include' " — DonQi, Oct 01 '13 at 12:17
@DonQi, I've removed the incorrect use of `Include` and replaced it with a projection which will ensure that the Activity is loaded for each ActivityHistory. I'm not sure what you mean by "I need to eagerly load everything", but if it means that you need record.User.ActivityHistory to contain *all* the ActivityHistory records for that user, you could try putting the `Include` calls back on _ds.USERS_TABLE in the query. — Steve Ruble, Oct 01 '13 at 13:34

DonQi · Accepted Answer · 2013-10-10T12:16:11.857

And finally, after much too many hours spent on this subject I finally found my solution (which I believe is somehow close to the Relationship Fixup concept, but i might be saying an absurdity here).

public List<User> GetAll()
{
   using (Entities _db = new Entities())
   {
      _db.Configuration.LazyLoadingEnabled = false;

      var now = DateTime.Now;

      var currentActivities = _db.ActivityHistory 
                                .Where(x => x.DateFrom <= now && x.DateTo >= now)
                                .Include("TheActivity")
                                .ToList();

      var Controlers = _db.User.ToList();

      return Controlers;
   }
}

Actually the answer was Here all the time :

Issue two separate queries: one for the Movies, one for the Reviews, and let relationship fix-up do the rest.

Any comment appreciated thought. Thanks.

Linq to Entities : Left Join and filter

3 Answers3