Entity Framework Query Result as List of Complex Object

Question

First of all I'm newbie at Back-End. Forgive me if that is stupid question. I was searching for the answer almost for 3 days. I learned a lot but still couldn't find proper solution.

My Teams table,

ID | Team   | Branch | Year |
---|--------|----------|------|
1  |Team A  | 9401     | 2020 |
2  |Team B  | 9401     | 2020 |
3  |Team C  | 9449     | 2020 |
4  |Team D  | 9491     | 2019 |

My Team_Members table,

ID  | TeamID  | UserID | UserName |
----|---------|--------|----------|
55  | 1       | 0001   | user1    |
56  | 1       | 0002   | user2    |
57  | 2       | 0003   | user3    |
58  | 2       | 0004   | user4    |
59  | 2       | 0005   | user5    |
60  | 3       | 0006   | user6    |
61  | 4       | 0007   | user7    |

Teams table has reference with column "TeamID" in Team_Members table. I can claim all relational fields with this query,

var query = _dbContext.Teams
    .Include(i => i.Team_Members)
    .Where(x=> x.Year == "2020")
    .ToList();

But I need to convert this query to following Object Pattern.

Example: Year is required parameter, If I enter "2020" for year, I need to claim fields with following object pattern,

[
  {
    "Branch":"9401",
    "Year":"2020",
    "Teams":[
      {
        "TeamName":"Team A",
        "TeamMembers":[
          {
            "UserName":"user1",
            "UserID":"0001"
          },
          {
            "UserName":"user2",
            "UserID":"0002"
          }
        ]
      },
      {
        "TeamName":"Team B",
        "TeamMembers":[
          {
            "UserName":"user3",
            "UserID":"0003"
          },
          {
            "UserName":"user4",
            "UserID":"0004"
          },
          {
            "UserName":"user5",
            "UserID":"0005"
          }
        ]
      }
    ]
  },
  {
    "Branch":"9449",
    "Year":"2020",
    "Teams":[
      {
        "TeamName":"Team C",
        "TeamMembers":[
          {
            "UserName":"user6",
            "UserID":"0006"
          }
        ]
      }
    ]
  }
]

Best Regards.

Answer 1

I assume you can try to select to an anonymous type of which you can directly serialize into JSON.

I removed the include because we only need it if we are querying .ToList() the entity classes and child properties.

var query = _dbContext.Teams
    //.Include(i => i.Team_Members)
    .Where(x => x.Year == "2020")
        .GroupBy(g => new { g.Branch, g.Year})
        .Select(t => new {
            Branch = t.Key.Branch,
            Year = t.Key.Year,
            Teams = t.GroupBy(g => g.TeamName).Select(te => new
            {
                TeamName = te.Key,
                TeamMembers = te.SelectMany(tm => tm.Team_Members).Select(tm => new
                {
                    UserName = tm.UserName,
                    UserId = tm.UserID,
                }),
            }),
        })
        .ToList();

A caveat to note is that I did not test whether there may be any quirks in EF evaluating this LINQ.

If there are issues, you can do the following where ... is the GroupBy and Select blocks as above.

var data = _dbContext.Teams
    .Include(i => i.Team_Members)
    .Where(x => x.Year == "2020").ToList();
var result = data
 ...
.ToList();

edit: Updated to fix a misunderstanding on my part.