none
Include does not work with many to many relations RRS feed

  • Question

  • Hi, i spend sevrals hours understanding why code below dont work.

    Can you help me ?

            public IQueryable<Element> GetElementsPourCommune(int communeId)
            {
                var q = (ObjectQuery<Element>)from c in GetCommunes()
                                              from e in c.Elements
                                              where c.Id == communeId
                                              select e;
    
                q.Include("Groupe");
    
    
                return q;
            }
    
    

    c.Elements is a many to many relation between table 'Commune' (id, value) and table 'Element' (id, value, groupId)

    the relation is done with the table 'Commune_Element' (communeId, ElementId)


    The query above return the exact values form my database (sqlserver2005) but the 'Groupe' property in Element is always null.

    when inspecting the sql query generated, no column of the 'Groupe' table are include in the select clause.

    Why ? why ? why ?

    What's wrong in this code ?

    Thanks a lot for your response

    Wednesday, September 14, 2011 4:49 PM

Answers

  • Hi philippe,

    Thanks for your feedback.

    When you know the exact shape of the graph of related entities that your application requires, you can use the Include method on the ObjectQuery to define a query path that controls which related entities to return as part of the initial query.

    The relationship between Commune and Element is Many to Many, there are Navigations for each of them. According to your post, I didn't any "Groupe" in your models.

    BTW, I think you can try to explict loading the related entities, please refer this link : http://msdn.microsoft.com/en-us/library/bb896272.aspx

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, September 20, 2011 8:31 AM
    Moderator
  • Hi Philippe,

    I found another forum where somebody had this exact problem after adding an "Include"

    http://stackoverflow.com/questions/921023/entity-framework-load-error

    Maybe the name you've used is not correct?  Check to make sure it should be "Groupe".


    Tom Overton
    Monday, September 19, 2011 1:30 PM
  • Hi, Tom and Alan,

    Your two last post help me very much.

    I tried to create an objectQuery first with the include method and after, i wrote a linq query from the ObjectQuery and it work fine !

    Thanks a lot for your time spent on my problem.

    Have a nice day.


    Philippe VALLAR
    Friday, September 23, 2011 12:49 PM

All replies

  • Do you need to do your include in the from clause?  LIke this:
    
     public IQueryable<Element> GetElementsPourCommune(int communeId)
            {
                var q = (ObjectQuery<Element>)from c in GetCommunes()
                                 from e in c.Elements.Include("Groupe")
                                              where c.Id == communeId
                                              select e;
    
                     }
    
    
    

     


    Tom Overton
    Wednesday, September 14, 2011 5:28 PM
  • Hi philippe,

    Welcome!

    Is any updates for your question? I'd like give a related link: http://coderjournal.com/2011/02/a-smarter-entity-framework-include-method/

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Sunday, September 18, 2011 4:16 AM
    Moderator
  • Hi Tom,

    Thanks for your response. Sorry for the delay of mine but i wasn't at work theses last days.

    I was tried that solution bat when i build my solution i have this error :

    'System.Data.Objects.DataClasses.RelatedEnd.Include(bool, bool)' est inaccessible en raison de son niveau de protection

    'System.Data.Objects.DataClasses.RelatedEnd.Include (bool, bool)' is inaccessible/unreachable due to its protection level

    I need to obtain a collection of elements and, for each one, the related Groupe and all of these with an only one sql query.

    This is pretty simple with linq to sql but with linq to entities....  ):

     


    Philippe VALLAR
    Monday, September 19, 2011 8:17 AM
  • Hi Alan_chen,

    Thanks for your link.

    The way it prupose is very pretty but, for my today's problem, it not help me.

    Best regards


    Philippe VALLAR
    Monday, September 19, 2011 8:20 AM
  • Hi Philippe,

    I found another forum where somebody had this exact problem after adding an "Include"

    http://stackoverflow.com/questions/921023/entity-framework-load-error

    Maybe the name you've used is not correct?  Check to make sure it should be "Groupe".


    Tom Overton
    Monday, September 19, 2011 1:30 PM
  • Hi philippe,

    Thanks for your feedback.

    When you know the exact shape of the graph of related entities that your application requires, you can use the Include method on the ObjectQuery to define a query path that controls which related entities to return as part of the initial query.

    The relationship between Commune and Element is Many to Many, there are Navigations for each of them. According to your post, I didn't any "Groupe" in your models.

    BTW, I think you can try to explict loading the related entities, please refer this link : http://msdn.microsoft.com/en-us/library/bb896272.aspx

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, September 20, 2011 8:31 AM
    Moderator
  • Hi, Tom and Alan,

    Your two last post help me very much.

    I tried to create an objectQuery first with the include method and after, i wrote a linq query from the ObjectQuery and it work fine !

    Thanks a lot for your time spent on my problem.

    Have a nice day.


    Philippe VALLAR
    Friday, September 23, 2011 12:49 PM