EF6 include with join not including.

Question

I'm well aware this is an issue with a clear solution in more simple situations, but i have implemented something that i think doing it otherwise or changing it completely due to this issue is wrong, maybe there is something that I don't see. well, I have clients, each client have missions,each mission has agents assigned to it. I want to load all that to an object, and to query on those parameters according to some search object i get from my client, like this.

public IEnumerable<object> GetMissionsBySearch(MissionSearchCriteria search, int loggedUserId)
    {
        var today = DateTime.Now.AddHours(12);
       try
        {
            var query = from mission in context.Missions.Include("AssignedAgents")
                join client in context.Clients on mission.ClientId equals client.Id
                select new
                    {
                        mission,
                        client.Name,
                        client.Office,
                        client.Id,
                    }
                into x
                select x;

            if (search.ByType != ActivityType.All)
            {
                query = query.Where(s => s.mission.ActivityType == search.ByType);
            }
            if (search.IsTakenCareOf != "all")
            {
                bool endedOrNot;
                bool value = Boolean.TryParse(search.IsTakenCareOf, out endedOrNot);
                if (value)
                {
                    query = query.Where(s => s.mission.TakenCareOf == endedOrNot);    
                }

            }
            if (search.ByCreator == ByCreator.Me)
            {
                query = query.Where(s => s.mission.AddedbyUserId == loggedUserId
                    && s.mission.AssignedAgents.Count(a => a.Id == loggedUserId) > 0);
            }
            if (search.ByCreator == ByCreator.Other)
            {
                query = query.Where(s => s.mission.AddedbyUserId != loggedUserId 
                    && s.mission.AssignedAgents.Count(a => a.Id == loggedUserId) > 0);
            }
            if (search.ByCreator == ByCreator.Both)
            {
                //query = query.Where(s => s.mission.AddedbyUserId == loggedUserId
                //   || s.mission.AssignedAgents.Count(a => a.Id == loggedUserId) > 0);
                //query = query.Where(s => s.mission.AddedbyUserId != loggedUserId
                //   && s.mission.AssignedAgents.Count(a => a.Id == loggedUserId) > 0);
            }
            if (search.ByDate == ByDate.All)
            {
                //Doing Nothing
            }
            if (search.ByDate == ByDate.Today)
            {
                query = query.Where(s=> s.mission.Deadline.Value <= today);
            }
            else if (search.ByDate == ByDate.Yesterday)
            {

                var yesterday = DateTime.Now.AddHours(-24);
                query = query.Where(s => s.mission.Deadline > yesterday
                    && s.mission.Deadline < today);
            }
            else if (search.ByDate == ByDate.LastWeek)
            {
                var lastweek = DateTime.Now.AddDays(-8);
                query = query.Where(s => s.mission.Deadline > lastweek
                    && s.mission.Deadline < today);
            }
            else if (search.ByDate == ByDate.ByDays && search.Days > 0)
            {
                var daysBack = DateTime.Now.AddDays(search.Days*-1);
                query = query.Where(s => s.mission.Deadline > daysBack);
            }
            else if (search.ByDate == ByDate.BetweenDates)
            {
                query = query.Where(s => s.mission.Deadline > search.FromDate && s.mission.Deadline < search.ToDate);
            }
            else if (search.ByDate == ByDate.EveryThingBackToDate)
            {
                query = query.Where(s =>  s.mission.Deadline < search.ToDate);
            }              
            return query.ToList();

I read those three articles and still have no success in getting this to work, Entity Framework & Include http://blogs.msdn.com/b/alexj/archive/2009/06/02/tip-22-how-to-make-include-really-include.aspx http://itsharpau.wordpress.com/2011/09/30/entity-framework-4-query-with-include-doesnt-work-with-join/

Please help, Thank You.

Answer 1

Include never works if you project to an (anonymous or named) type. Think of it, in which class should EF include the child collection?

The include will work if you do

var query = from mission in context.Missions.Include("AssignedAgents")
    join client in context.Clients on mission.ClientId equals client.Id
    select mission

Then EF will have Mission objects for which it can load AssignedAgents collections.

If you don't want to get the full objects you can get the same effect by incorporating AssignedAgents as a property in the anonymous type:

var query = from mission in context.Missions
    join client in context.Clients on mission.ClientId equals client.Id
    select new
        {
            mission,
            client.Name,
            client.Office,
            client.Id,
            Agents = mission.AssignedAgents
        };