none
Include does not work with join

    Question

  • Hi - "Include" is driving me nuts. If i rune the following query :

     

                IQueryable<f.Resource> matchedResources =
                    from r in feelzdata.Resource.Include("UserResource")

                    orderby r.ResourceID
                    select r;

     

    ... then i can print out the contents of the UserResource collection for a Resource.

     

    If i try a join, it does not include the UserResource items ....

     

                IQueryable<f.Resource> matchedResources =
                    from r in feelzdata.Resource.Include("UserResource")
                    join ur in feelzdata.UserResource on r.ResourceID equals ur.ResourceID
                    orderby r.ResourceID
                    select r;

     

    Anyone know why?

     

    thanks,

    steven

    http://livz.org

    Thursday, December 06, 2007 4:44 PM

Answers

  •  

    Include() is an “exact-entity” operator of ObjectQuery<T>. (Reference: http://msdn2.microsoft.com/en-us/library/bb738708(VS.90).aspx) It denotes a navigation property that you want to dereference and materialize as part of your resulting entity collection. That’s why the “T” in the ObjectQuery<T> must be an entity type, i.e. it may not be DbDataRecord. In the first case all the clauses produce such interim ObjectQuery instances.

     

    The JOIN in the second case produces an ObjectQuery<DbDataRecord>. Thus Include() is no longer applicable, and it is silently dropped.

     

    Since you are using LINQ to Entities, you don’t need Include() at all. Instead, you should project an anonymous type:

     

    IQueryable<f.Resource> matchedResources =
               from r in feelzdata.Resource

               orderby r.ResourceID

               select  new

                       {

                           Resource = r;

                           UserResource = r.UserResource;

                       };

     

     

    Zlatko Michailov

    Program Manager, Entity Services

    Microsoft Corp.

    http://blogs.msdn.com/esql

    Thursday, December 13, 2007 8:54 PM
    Moderator

All replies

  •  

    Include() is an “exact-entity” operator of ObjectQuery<T>. (Reference: http://msdn2.microsoft.com/en-us/library/bb738708(VS.90).aspx) It denotes a navigation property that you want to dereference and materialize as part of your resulting entity collection. That’s why the “T” in the ObjectQuery<T> must be an entity type, i.e. it may not be DbDataRecord. In the first case all the clauses produce such interim ObjectQuery instances.

     

    The JOIN in the second case produces an ObjectQuery<DbDataRecord>. Thus Include() is no longer applicable, and it is silently dropped.

     

    Since you are using LINQ to Entities, you don’t need Include() at all. Instead, you should project an anonymous type:

     

    IQueryable<f.Resource> matchedResources =
               from r in feelzdata.Resource

               orderby r.ResourceID

               select  new

                       {

                           Resource = r;

                           UserResource = r.UserResource;

                       };

     

     

    Zlatko Michailov

    Program Manager, Entity Services

    Microsoft Corp.

    http://blogs.msdn.com/esql

    Thursday, December 13, 2007 8:54 PM
    Moderator
  • Is this advice still applicable - I am trying this with the following query

    var results = from p in context.Person 
                        select new { person = p, stuff = p.Member}; 


    and it doesn't Include the Member reference on the Person objects and in fact stuff is also null as well - which seems to be contrary to the advice seen here and on this thread where it seems that doing it this way with anonymous types should force eager loading in the query - http://social.msdn.microsoft.com/forums/en-US/adodotnetentityframework/thread/581349fb-c940-4686-aef8-35fe8a0366e0/  .  I am a proponent of Entity Framework in general and am excited about where they are going, but am extremely disappointed with how messed up with how Include is handled in V1 - it really hamstrings how you use LINQ to Entities - I read the explanation as well and feel that the team just missed it on this - from the explanation it sounds like the team was worrying about performance and trying to do an optimization for you instead of usability - transparently dropping Includes is maddening


    Friday, December 12, 2008 11:55 PM
  • Zlatko,

    We recently decided on a major rewrite and have been courting Entity Framework to handle our DataAccess. While still in the early stages, this issue has already given us quite a bit of difficulty, as the anonymous method isn't a great solution with WCF in the mix and altering our table structure is unfortunately not really an option.

    I'm a little surprised that this was an EF1 issue from almost 2 years ago that still hasn't been addressed. Will this be addressed at some point?

    Friday, June 11, 2010 11:22 PM
  • Hi all,

    This Include-Join trouble gave me headaches for 3 days and since I am using WCF as well, the anonymous type solution was simply no the way to do it. So, after a bit of research, I found a solution:

    Create your LINQ query, then cast the IQueryable result object query to an ObjectContext<T> in order to get to the Include() method, then you can execute it as you wish. So, let's say you have some Projects and Tasks entities (in a 1 to many relationship) and you want to join your Projects with a list<int> that holds some filtered ProjectIDs for you - but still you'd like to get the tasks as well not only the projects with one query. Your code will look like this:

     var projects = from p in ents.Projects
                  from i in projectIDs
                  where i == p.ID
                  select p;
    
    var res = ((ObjectQuery<Project>)projects).Include("Tasks").ToList();

    This should do it.

    HTH,

    Stefan

    • Proposed as answer by Stephanne Monday, June 21, 2010 5:18 PM
    Monday, June 21, 2010 5:18 PM
  • This is exactly what I was looking for.  Thanks a million for sharing with the community.
    Tuesday, November 16, 2010 10:02 PM
  • In general you should have include as the last operation in a query. This is similar to what i would recommend for orderby clause, although in some cases orderby clause works because of orderby lifting feature in EF4.

    Below posting might help

    http://weblogs.asp.net/zeeshanhirani/archive/2010/10/10/thanks-ef4-now-i-can-put-my-orderby-clause-anywhere.aspx


    Zeeshan Hirani Entity Framework 4.0 Recipes by Apress
    http://weblogs.asp.net/zeeshanhirani
    Wednesday, November 17, 2010 6:00 AM