C# - Group by common properties to object

Question

I have this result table returned from a query

+----------+------------+-------------------+------------+
| Id       | Name       | ApplicationUserId | JobTypeId  |
+----------+------------+-------------------+------------+
| 6000000  | A          | 4327428           | 234        |
| 6000000  | A          | 8764328           | 232        | 
| 6000001  | B          | 1238474           | 456        |
| 6000001  | B          | 8743428           | 432        |
| 6000001  | B          | 9292384           | 122        |
+----------+------------+-------------------+------------+

I use raw sql query of EF like this:

db.Database.SqlQuery<Example>(query).ToList();

How can I extract the common values and return an object from that result table that will look like this:

[
  {
    "Id": 6000000, "Name": "A", "Therapists": 
    [
      {"Id":"4327428", "JobTypeId": 234},
      {"Id":"8764328", "JobTypeId": 232}
    ]
  },

 {
  "Id": 6000001, "Name": "B", "Therapists": 
    [
     {"Id":"1238474", "JobTypeId": 456}, 
     {"Id":"8743428","JobTypeId": 432},
     {"Id":"9292384", "JobTypeId": 122}
    ]
 }
]

The return object can be Json or any type of object that will preserve this structure

EDIT I managed to preform this task by 2 foreach loops but I want something more elegant

I also used this class to hold the sql result table

public class Example 
{

    public int Id { get; set; }
 
    public string Name { get; set; }
    
    public Guid ApplicationUserId { get; set; }
    
    public int? JobTypeId { get; set; } 
}

Do you have a question? FYI, "How can it be done?" is not a good question for StackOverflow. What have you tried? What specific problem did you run into? — tnw, Aug 03 '21 at 13:47
Linq provides the `GroupBy` operator which should do what you need: https://learn.microsoft.com/en-us/dotnet/csharp/linq/group-query-results — Jack A., Aug 03 '21 at 13:53
It seems that you need to **convert c# list to json** take a look at [Serializing a list to JSON](https://stackoverflow.com/questions/9110724/serializing-a-list-to-json) — mohabbati, Aug 03 '21 at 13:53
"can be Json or any type of object" - JSON is the TEXT representation of data. Do you really want a json string as the result? — Christoph Lütjen, Aug 03 '21 at 14:11

score 2 · Accepted Answer · answered Aug 03 '21 at 14:17

Hey i would like to recommend to you to try something like my example below. These are the classes I used in the following example:

public class Example
{
    public int Id { get; set; }

    public string Name { get; set;}

    public Guid ApplicationUserId { get; set; }

    public int? JobTypeId { get; set; }
}


public class GroupedResults
{
    public int Id { get; set; }

    public string Name { get; set; }

    public IEnumerable<Result> Therapists { get; set; }
}

public class Result
{
    public Guid Id { get; set; }

    public int? JobTypeId { get; set; }
}

and these is the group by command i used:

var results = dbResult.GroupBy(c => new
                                        {
                                            c.Id,
                                            c.Name
                                        }).Select(entry => new GroupedResults
                                                               {
                                                                   Id = entry.Key.Id, 
                                                                   Name = entry.Key.Name, 
                                                                   Therapists = entry.Select(x => new Result
                                                                                                      {
                                                                                                          Id = x.ApplicationUserId, 
                                                                                                          JobTypeId = x.JobTypeId
                                                                                                      })
                                                               });

I hope this is similar to what you were looking for. In the end you have to use a serializer to convert the objects to JSON to receive the desired data structure.

C# - Group by common properties to object

1 Answers1