C# Group JSON by value of key

Question

community I have a stored procedure that returns this JSON as a response:

[
  {
    "displayorder": 2,
    "oppItemID": 4,
    "opportunityName": "Net 10",
    "title": "New Amazing Promotion",
    "description": "Amazing",
    "image": "imagelink",
    "opDate": "2018-08-09T22:00:00.937",
    "slideOrder": 0,
    "status": false,
    "isCover": false
  },
  {
    "displayorder": 1,
    "oppItemID": 2,
    "opportunityName": "Simple Mobile",
    "title": "New promo",
    "description": "Amazing",
    "image": "imagelink",
    "opDate": "2018-08-09T22:00:00.937",
    "slideOrder": 0,
    "status": false,
    "isCover": false
  },
  {
    "displayorder": 1,
    "oppItemID": 3,
    "opportunityName": "Simple Mobile",
    "title": "New Amazing Promotion",
    "description": "Amazing",
    "image": "imagelink",
    "opDate": "2018-08-10T22:00:00.937",
    "slideOrder": 0,
    "status": false,
    "isCover": false
  },
  {
    "displayorder": 8,
    "oppItemID": 5,
    "opportunityName": "Verizon",
    "title": "New Amazing Promotion",
    "description": "Amazing",
    "image": "imagelink",
    "opDate": "2018-08-09T22:00:00.937",
    "slideOrder": 0,
    "status": false,
    "isCover": false
  },
  {
    "displayorder": 8,
    "oppItemID": 27,
    "opportunityName": "Verizon",
    "title": "New Amazing",
    "description": "Amazing",
    "image": "imagelink",
    "opDate": "2018-08-22T22:00:00.937",
    "slideOrder": 0,
    "status": false,
    "isCover": false
  }
]

As you can see, there are multiple objects that have the same "opportunityName"

What I want to do is group them by that specific "opportunityName" and have a list of the objects i.e:

"Varizon":{
     0:{
        "displayorder": 8,
        "oppItemID": 5,
        "title": "New Amazing Promotion",
        "description": "Amazing",
        "image": "imagelink",
        "opDate": "2018-08-09T22:00:00.937",
        "slideOrder": 0,
        "status": false,
        "isCover": false
      },
      1:{
        "displayorder": 8,
        "oppItemID": 27,
        "title": "New Amazing",
        "description": "Amazing",
        "image": "imagelink",
        "opDate": "2018-08-22T22:00:00.937",
        "slideOrder": 0,
        "status": false,
        "isCover": false
      }
}

And the same for the rest of the opportunity Names

This is the code:

public List<OpportunitiesByNameForDisplay> GetBdmOpportunites(string rswnum)
        {
            List<OpportunitiesByNameForDisplay> OpportunitiesByNameForDisplay = new List<OpportunitiesByNameForDisplay>();
            SqlParameter[] sqlParams;
            List<object> real = new List<object>();
            try
            {
                var rswnumber = new SqlParameter("@AccountNumber", rswnum);

                sqlParams = new SqlParameter[1] {
                   rswnumber
                };

                string sql = "exec RSW_Reports_API.dbo.sp_CRMApp_GetAppointmentOpportunitiesFromZoomTesting " + rswnumber ;
                OpportunitiesByNameForDisplay = db.Database.SqlQuery<OpportunitiesByNameForDisplay>(sql,sqlParams).ToList();

                real = OpportunitiesByNameForDisplay.GroupBy(g => new List<Object> {
                    g.OpportunityName,
                    slide = new
                    {
                        g.Title,
                        g.Description,
                        g.Image,
                        g.opDate,
                        g.status,
                        g.SlideOrder,
                        g.displayorder
                    }
                });
            }
            catch (Exception ex){}
            return OpportunitiesByNameForDisplay;
        }

I m getting invalid initializer declarator on g.OpportunityName Is there any way to make this work or better?

Answer 1

Just going from that pure json that you posted and using LINQ to transform the data. I think this is close to what you are after assuming you want json string. You are returning an object not a serialized string from your method?.

The tricky part getting values into key positions and that could be done with ToDictionary().

If it is not a perfect solution for you I hope it gives some input.

var json = "[{\"displayorder\":2,\"oppItemID\":4,\"opportunityName\":\"Net 10\",\"title\":\"New Amazing Promotion\",\"description\":\"Amazing\",\"image\":\"imagelink\",\"opDate\":\"2018-08-09T22:00:00.937\",\"slideOrder\":0,\"status\":false,\"isCover\":false},{\"displayorder\":1,\"oppItemID\":2,\"opportunityName\":\"Simple Mobile\",\"title\":\"New promo\",\"description\":\"Amazing\",\"image\":\"imagelink\",\"opDate\":\"2018-08-09T22:00:00.937\",\"slideOrder\":0,\"status\":false,\"isCover\":false},{\"displayorder\":1,\"oppItemID\":3,\"opportunityName\":\"Simple Mobile\",\"title\":\"New Amazing Promotion\",\"description\":\"Amazing\",\"image\":\"imagelink\",\"opDate\":\"2018-08-10T22:00:00.937\",\"slideOrder\":0,\"status\":false,\"isCover\":false},{\"displayorder\":8,\"oppItemID\":5,\"opportunityName\":\"Verizon\",\"title\":\"New Amazing Promotion\",\"description\":\"Amazing\",\"image\":\"imagelink\",\"opDate\":\"2018-08-09T22:00:00.937\",\"slideOrder\":0,\"status\":false,\"isCover\":false},{\"displayorder\":8,\"oppItemID\":27,\"opportunityName\":\"Verizon\",\"title\":\"New Amazing\",\"description\":\"Amazing\",\"image\":\"imagelink\",\"opDate\":\"2018-08-22T22:00:00.937\",\"slideOrder\":0,\"status\":false,\"isCover\":false}]";
        var obj = JsonConvert.DeserializeObject<List<JSonClass>>(json);

        var transformed = obj 
                            .Select(  (value, index) => new {
                                            name = value.opportunityName,
                                            obj = new JSonClass2 {
                                                    displayorder = value.displayorder,
                                                    oppItemID = value.oppItemID,
                                                    title = value.title,
                                                    description = value.description,
                                                    image = value.image,
                                                    opDate = value.opDate,
                                                    slideOrder = value.slideOrder,
                                                    status = value.status,
                                                    isCover = value.isCover
                                                    }
                                            })
                .GroupBy(u=>u.name)  // groups by name
                .ToDictionary(       // use dictionary to move value from value position to key position 
                                wrap =>wrap.Key, 
                                wrap => wrap.Select( (v,i) => new { i, v.obj })
                .ToDictionary(w => w.i, w => w.obj )  
                );

        Console.WriteLine(JsonConvert.SerializeObject(transformed, Formatting.Indented));

Console output is then:

{
  "Net 10": {
    "0": {
      "displayorder": 2,
      "oppItemID": 4,
      "title": "New Amazing Promotion",
      "description": "Amazing",
      "image": "imagelink",
      "opDate": "2018-08-09T22:00:00.937",
      "slideOrder": 0,
      "status": false,
      "isCover": false
    }
  },
  "Simple Mobile": {
    "0": {
      "displayorder": 1,
      "oppItemID": 2,
      "title": "New promo",
      "description": "Amazing",
      "image": "imagelink",
      "opDate": "2018-08-09T22:00:00.937",
      "slideOrder": 0,
      "status": false,
      "isCover": false
    },
    "1": {
      "displayorder": 1,
      "oppItemID": 3,
      "title": "New Amazing Promotion",
      "description": "Amazing",
      "image": "imagelink",
      "opDate": "2018-08-10T22:00:00.937",
      "slideOrder": 0,
      "status": false,
      "isCover": false
    }
  },
  "Verizon": {
    "0": {
      "displayorder": 8,
      "oppItemID": 5,
      "title": "New Amazing Promotion",
      "description": "Amazing",
      "image": "imagelink",
      "opDate": "2018-08-09T22:00:00.937",
      "slideOrder": 0,
      "status": false,
      "isCover": false
    },
    "1": {
      "displayorder": 8,
      "oppItemID": 27,
      "title": "New Amazing",
      "description": "Amazing",
      "image": "imagelink",
      "opDate": "2018-08-22T22:00:00.937",
      "slideOrder": 0,
      "status": false,
      "isCover": false
    }
  }
}