Ef's .Include with ParallelFor

Question

I am having issue with caching data in memory with EF's Include().

var data = _context.Users
             .Where(x => x.IsTrainee)
             .GroupJoin(_context.Feedbacks.Include(x => x.User), u => u.UserId, f => f.AddedFor, (u, f) => new {u, f})
             .ToList();

Here every User will have 0 to n Feedback and every feedback has 1-1 user associated with it, using FK AddedBy.

When I iterate over data using foreach the navigational properties get filled up and I can successfully return the result.

But when I use Parallel.ForEach or .AsParallel().ForAll, the Include() fails for every feedback, and the navigational property User for Feedback Db set are null.

Surprisingly, the above parallel operation doesn't fail every time, sometimes the requests are processed with Feedback having User, but sometimes it returns null.

if (data.Any(r => r.f != null && r.f.Any(t => t.User == null)))
{
    LogUtility.ErrorRoutine(new Exception("lastWeeklyFeedback is null:"));
}

But when I added the above if block to debug, to my surprise my parallel operation works like a charm. And if I comment the above if condition, then the parallel operation acts weirdly (ie. sometimes it works sometimes it fails).

I believe it is something related to EF's Include. Why is my code block behaving like this? What EF concept am I missing?.

Note: The above operation is used for read-only operations, it isn't used to add/update the DB, just to fetch data.

EDIT

Include the entire method. The conditions and business logic is irrelevant to context, But I added the entire snippet for just in case if you need it.

   public DashboardVm GetAllTraineeDataForDashbaord(int teamId)
    {


        var data = _context.Users.Where(x => x.IsTrainee == true && (teamId == 0 || x.TeamId == teamId) && x.IsActive == true)
                                 .GroupJoin(_context.Feedbacks.Include(x => x.User), u => u.UserId, f => f.AddedFor, (u, f) => new {u, f})                            
                                 .OrderBy(x=>x.u.UserName).AsNoTracking()
                                 .ToList();

        //if (data.Any(r => r.f != null && r.f.Any(t => t.User == null)))
        //{
        //    LogUtility.ErrorRoutine(new Exception("lastWeeklyFeedback is null:"));
        //}

       UserConverter userConverter = new UserConverter();
       FeedbackConverter feedbackConverter = new FeedbackConverter();

       DateTime mondayTwoWeeksAgo = Common.Utility.UtilityFunctions.GetLastDateByDay(DayOfWeek.Monday, DateTime.Now.AddDays(-14));
       DateTime lastFriday = Common.Utility.UtilityFunctions.GetLastDateByDay(DayOfWeek.Friday, DateTime.Now);
       DateTime lastMonday = lastFriday.AddDays(-5);

       var concurrentTrainee = new ConcurrentQueue<UserData>();
      //  data.AsParallel().ForAll(traineeLocal=>
       Parallel.ForEach(data, traineeLocal =>
       // foreach(var trainee in data)
       {
           var trainee = traineeLocal;
           bool lastWeekFeedbackAdded =     trainee.u.DateAddedToSystem >= lastFriday
                                        || (trainee.f.Any(x => x.FeedbackType == (int)Common.Enumeration.FeedbackType.Weekly 
                                        && ( x.StartDate >= lastMonday || (x.EndDate <= lastFriday && x.EndDate >= lastMonday))));

           Feedback lastWeeklyFeedback = lastWeekFeedbackAdded ? (trainee.f.OrderByDescending(feedback => feedback.FeedbackId)
                                                                                   .FirstOrDefault(x => x.FeedbackType == (int)Common.Enumeration.FeedbackType.Weekly))
                                                                : null;

          
           concurrentTrainee.Enqueue(new UserData
           {
               User =  userConverter.ConvertFromCore(trainee.u),

               IsCodeReviewAdded = trainee.u.DateAddedToSystem >= lastFriday
                                   || trainee.f.Any(x => x.FeedbackType == (int)Common.Enumeration.FeedbackType.CodeReview && x.AddedOn >= mondayTwoWeeksAgo),

               LastWeekFeedbackAdded = lastWeekFeedbackAdded,

               WeeklyFeedback = lastWeeklyFeedback == null ? new List<Common.Entity.Feedback>() : 
                                                             new List<Common.Entity.Feedback> { feedbackConverter.ConvertFromCore(lastWeeklyFeedback)},

               RemainingFeedbacks = feedbackConverter.ConvertListFromCore(trainee.f.Where(x => x.FeedbackType == (int)Common.Enumeration.FeedbackType.CodeReview
                                                                                                    || x.FeedbackType == (int)Common.Enumeration.FeedbackType.Weekly
                                                                                                    || x.FeedbackType == (int)Common.Enumeration.FeedbackType.Assignment
                                                                                                    || x.FeedbackType == (int)Common.Enumeration.FeedbackType.Skill
                                                                                                    || x.FeedbackType == (int)Common.Enumeration.FeedbackType.RandomReview)
                                                                                    .OrderByDescending(x=>x.FeedbackId)
                                                                                    .Take(5)
                                                                                    .ToList()),
                WeekForFeedbackNotPresent = new List<string>(),
                AllAssignedCourses = new List<CourseTrackerDetails>()

           });
       }
        );

       return new DashboardVm
       {
           Trainees = concurrentTrainee.ToList()
       };          
   }

I am not doing any operation over Entity, I am using that entity to build new DTO. None of the propertie's state has been changed in loop @CodeCaster. I have also used .`.AsNoTracking` but with no help — Shekhar Pankaj, Mar 16 '17 at 08:37
I'd like to know why are you using `.Include` in the first place. Since after the `.Where` call, you are still coding against an `IQueryable` EF is able to resolve relationship references to SQL without manually asking it to include. This by itself won't solve your problem I think, I'd just like to know the reason; maybe there is some connection between them. — Balázs, Mar 16 '17 at 08:49
Please also clarify whether you are calling the parallel extensions _before_ or _after_ `ToList()` -- it does matter, since if you do it before, you are parallelizing (simply put) the `DataReader` being used behind the scenes which might cause issues but if you call it _after_ `ToList()`, you are parallelizing an already-fully-loaded-into-memory collection. — Balázs, Mar 16 '17 at 08:49
@Balázs, I was doing `hit n trail`, So I added `Include` to make sure the related entities are Getting loaded. Apart from that `Include` has nothing important to do here. Secondly, I am doing the parallel operation after the `.ToList()` — Shekhar Pankaj, Mar 16 '17 at 09:19
Strange, since you are using projection, the `Include` should be ignored. But in general, EF `DbContext` is not thread safe, hence should not be used with `Parallel` class. — Ivan Stoev, Mar 16 '17 at 09:20
If you are doing parallel after `ToList`, most likely you are hitting hidden db context lazy loading. Are your navigation properties `virtual`? — Ivan Stoev, Mar 16 '17 at 09:21
If you did it before `ToList` however, you'd be likely hitting a MARS (Multiple Active Result Set) disabled exception. And by just letting lazy loading do its job after loading the objects into memory you'd be facing a select n+1 issue. So the cleanest thing to do would be to flatten the results - that is, instead of `new { u, f }`, specify exactly what piece of data you need from which entity. — Balázs, Mar 16 '17 at 09:24
Is the EF6? If so, the `Include` doesn't have any effect and lazy loading is triggered. This is bound to cause exceptions, or unexpected behavior at best, because the context isn't thread safe and the lazy loading requests are fired from multiple parallel threads. Check the SQL output. — Gert Arnold, Mar 16 '17 at 09:51
@Gert what? `Include()` queries related entities with an explicit JOIN in the initial query, so you won't _need_ lazy loading anymore. — CodeCaster, Mar 16 '17 at 10:44
@CodeCaster The result object is a projection (`new {u, f}`), so EF6 ignores the `Include`. That's different in ef-core. — Gert Arnold, Mar 16 '17 at 11:21
@Gert alright, [that's true](http://stackoverflow.com/questions/10572328/linq-when-using-groupby-include-is-not-working), I missed that group entirely. Thanks! — CodeCaster, Mar 16 '17 at 11:21
The edit just proves that you are doing the parallel operation after materializing the query with `ToList`. But it doesn't show your entity objects and their navigation properties, hence we don't know if EF creates proxies or not. As I mentioned (and now @Gert), `Include` is ignored in such queries. Try removing the `Include` and you should get exactly the same behavior. To verify if it's caused by lazy loading, add `_context.Configuration.ProxyCreationEnabled = false;` at the very beginning. — Ivan Stoev, Mar 16 '17 at 11:33

score 0 · Answer 1 · answered Mar 17 '17 at 20:27

A few things are happening here. First, you are doing the Include on the inside of the GroupJoin. You have to to the include right before the ToList or it gets lost.

Second, the source DbContext is on a different thread from the Parallel.Foreach. My gut tells me you have LazyLoading turned on. As you loop through each item, it is doing to do a database hit to load it. However, the original context is on a different thread. I'm kind of surprised it even let you do that. But ultimately, as it runs through, I imagine it isn't waiting for DbContext to come back. Now, some users are probably shared, so eventually some come back afterwards and are able to map correctly without making a DB call inside DbContext. When you debug, you end up causing it to wait longer than it normally would and then it loads correctly.

Ef's .Include with ParallelFor

1 Answers1