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.

Markuzy · Accepted Answer · 2020-07-27 02:15:05Z

2

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.

edited Jul 27, 2020 at 2:15

answered Jul 24, 2020 at 14:01

Markuzy

5057 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

rcanpahali Over a year ago

Thanks for your answer! It's very close to my solution but I think I also need to seperate (grouping) this JSON according to my "Branches". Thats the braking point of query.

Markuzy Over a year ago

Hi @shadowman_93, I have updated my query to fit the grouping to "Branches" and effectively "Year".

Collectives™ on Stack Overflow

Entity Framework Query Result as List of Complex Object

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related