none
How To Query hierarchy entity which intersects ? RRS feed

  • Question

  • I know some entry level LINQ query, select, selectmany, where, etc.

    but this one I couldn't figure out, let me describe my problem

    We have these entity Football teams, football players, children, schools

    Each football team has some football players, some football players has children.

    and there are many schools which those children goes to.

    Then how do I use LINQ to find out the set of school which one particular football team's players children goes to?

    am I able to do this in one nested query?
    • Edited by Maxi Wu Wednesday, January 15, 2014 10:02 AM
    Wednesday, January 15, 2014 10:01 AM

Answers

  • Hi Maxi;

    If I understand you correctly the following diagram shows the relationships between the four tables you have stated in your question. I have posted two possiable ways to your question, "Then how do I use LINQ to find out the set of school which one particular football team's players children goes to?", One starts with the team -> Player -> Child -> School and two from School -> child -> player -> team.

    var school = (from t in Teams
                  where t.TeamID == 2
                  from p in t.Players
                  from c in p.Childrens
                  select c.School.Name).Distinct();
                 
    var school2 = (from s in Schools
                   from c in s.Childrens
                   where c.Player.Team.TeamID == 2
                   select s.Name).Distinct();

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Thursday, January 16, 2014 6:12 PM

All replies

  • Hello,

    From your description, we can know that football teams should have one to many with football players, children has many to one relationship with School, however, we cannot know the association between football players with children, one to zero-or-one or one to many?

    If it is possible, could you please provide the detail table schema, the fields and the associations between these tables?

    Or it is better for you to provide the T-SQL you want it to be translated to LINQ query.

    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.

    Thursday, January 16, 2014 2:02 AM
    Moderator
  • Team and players, 1 to many. player and children, 1 to many.

    children to school, many to one.

    I could try to come up with a T-SQL, but I don't have one yet.

    P.S.

    actually this is a simplify version of my question.

    consider that one children could enroll different course at different school. that is child and course, 1 to many. course and school, many to 1.

    Thursday, January 16, 2014 9:13 AM
  • Hi Maxi;

    If I understand you correctly the following diagram shows the relationships between the four tables you have stated in your question. I have posted two possiable ways to your question, "Then how do I use LINQ to find out the set of school which one particular football team's players children goes to?", One starts with the team -> Player -> Child -> School and two from School -> child -> player -> team.

    var school = (from t in Teams
                  where t.TeamID == 2
                  from p in t.Players
                  from c in p.Childrens
                  select c.School.Name).Distinct();
                 
    var school2 = (from s in Schools
                   from c in s.Childrens
                   where c.Player.Team.TeamID == 2
                   select s.Name).Distinct();

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Thursday, January 16, 2014 6:12 PM