none
Need example of getting related entities 2 layers deep similar to using left join RRS feed

  • Question

  • Say you had 3 tables, A B and C

    A is the primary table, which holds a foreign key to B.  B holds a foreign key to C

    I want to use a projection query to populate a POCO class with values from all 3 tables.

    I can do it with query type syntax using a join, but I want value back from A and B, regardless if C has a record or not

    Anyone got an example?

    Thursday, July 31, 2014 4:28 PM

Answers

  • Hello,

    >>I can do it with query type syntax using a join, but I want value back from A and B, regardless if C has a record or not

    Do you mean that if C do not have a record, its projection properties should be the null in the returned collection?

    If it is, you could use DefaultIfEmpty() method which would perform as left out join in database:

      var query = from person in people
    
                            join pet in pets on person equals pet.Owner into gj
    
                            from subpet in gj.DefaultIfEmpty()
    
                            select new { person.FirstName, PetName = subpet };
    

    The collection would contains records even pet is null. For details, you can check this article:

    http://msdn.microsoft.com/en-us/library/bb397895.aspx

    If I misunderstand, please let me know.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by shiftbit Monday, August 4, 2014 12:20 PM
    Friday, August 1, 2014 5:25 AM
    Moderator

All replies

  • Well, I just did it with two queries instead of trying to wrap it all up into a single query.  Im still confident it can be done in one.  I think I will have to use the group into methods because I have multiple child tables I want to pull data from.


    • Edited by shiftbit Thursday, July 31, 2014 7:21 PM sdfgsdfg
    Thursday, July 31, 2014 7:20 PM
  • Hello,

    >>I can do it with query type syntax using a join, but I want value back from A and B, regardless if C has a record or not

    Do you mean that if C do not have a record, its projection properties should be the null in the returned collection?

    If it is, you could use DefaultIfEmpty() method which would perform as left out join in database:

      var query = from person in people
    
                            join pet in pets on person equals pet.Owner into gj
    
                            from subpet in gj.DefaultIfEmpty()
    
                            select new { person.FirstName, PetName = subpet };
    

    The collection would contains records even pet is null. For details, you can check this article:

    http://msdn.microsoft.com/en-us/library/bb397895.aspx

    If I misunderstand, please let me know.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by shiftbit Monday, August 4, 2014 12:20 PM
    Friday, August 1, 2014 5:25 AM
    Moderator
  • Yes, exactly.  If the C entity doesnt have a record, its value in the result should just be null.

    DefaulIftEmpty and group into are what I have been reading about as a result of this.

    My final solution however, since I return POCO objects from the library where Im doing these queries, is to simply do this search in two separate steps.  

    Monday, August 4, 2014 12:23 PM