return custom json from LINQ select statement

Question

A very new programmer to MVC, JSON & LINQ - I have created an ActionResult that returns a JSONResult:

var formhistory = from p in _formsRepository.ReturnedForms
                          where p.DateAdded >= DateTime.Now.Date.AddDays(-15) && p.DateAdded <= DateTime.Now.Date
                          group p by new {p.Centre, p.Form, p.DateAdded}
                          into g
                          select new {
                                         g.Key.Centre,
                                         g.Key.Form,
                                         g.Key.DateAdded,
                                         Total = g.Sum(p => p.Quantity)
                                     };
return Json(formhistory, JsonRequestBehavior.AllowGet);

This gives me a nice JSON result set as follows:

[
  {"Centre":"Centre1","Form":"Advice","DateAdded":"\/Date(1331856000000)\/","Total":1067},
  {"Centre":"Centre1","Form":"Advice","DateAdded":"\/Date(1332460800000)\/","Total":808},
  {"Centre":"Centre1","Form":"Advice","DateAdded":"\/Date(1333062000000)\/","Total":559},
  {"Centre":"Centre1","Form":"Advice","DateAdded":"\/Date(1333666800000)\/","Total":1448}
]

My question is this: I'm trying to manipulate this JSON string so that instead of 2 key/value pairs for "Form" and "Total" I only have 1, i.e. "Form":"Total".

I realise this is probably a very basic question, but can anyone point me in the correct direction? (Apart from the door!)

You're selecting 4 fields and those 4 fields are written to the JSON string. I don't quite understand what you're trying to do instead. — VVS
– VVS, Commented Apr 27, 2012 at 9:58
VVS, Thanks for your reply - Instead of writing those 4 fields to the JSON string, I'm trying to write 3: Centre, DateAdded, and a third which is a combination of Form(as key) and Total(as value). As I said, very very new to this so if I'm being stupid please say so. I had thought I may need to build a custom JSON result combining the two, however I'm unclear on how to achieve this... — cwocky
– cwocky, Commented Apr 27, 2012 at 10:03

VVS · Accepted Answer · 2012-04-27 10:00:55Z

2

select new {
   g.Key.Centre,
   //g.Key.Form,
   g.Key.DateAdded,
   Form = g.Sum(p => p.Quantity)
}

would give you a key "Form" whose value is the former "Total". Is that what you want?

answered Apr 27, 2012 at 10:00

VVS

19.6k5 gold badges50 silver badges66 bronze badges

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

3 Comments

cwocky Over a year ago

I had tried this as well, however FORM will not take the value of the Form attribute from the SELECT query (i.e. I want the result as "Advice":"9999", not "Form":"9999". Apologies if I wasn't explaining myself very well!

VVS Over a year ago

@cwocky: Thanks for explaining. Are you sure you want keys (or field names) you don't know in advance? Say, there is a new form called "stackoverflow". Now the code that uses your JSON would have to know that there is a new key which contains the quantity. Why would you ever want this?

cwocky Over a year ago

Hi VVS, apologies for the delay replying. I'm going to close this request as we've been able to work around it (don't you love clients moving the goalposts?!) so it's no longer an issue - however I'm grateful for your input (and UserGS, thank you too)!

userGS · Accepted Answer · 2012-04-27 10:08:45Z

0

Modify the select part in your linq query

   select new {  
      g.Key.Centre, 
      g.Key.DateAdded,  
      NewField =  String.Format("{0} - {1}",g.Key.Form,g.Sum(p => p.Quantity).ToString())                                     
     };

I think this will solve your purpose.

answered Apr 27, 2012 at 10:08

userGS

2201 silver badge7 bronze badges

3 Comments

cwocky Over a year ago

userGS, Thank you very much - this almost gets it, however it still formats the result as "NewField":"Advice - 3101". Is there no way to shorten to "Advice":"3101"?

userGS Over a year ago

JSON has strings based on key-value pair. I dont think you'll get rid of field name.

cwocky Over a year ago

Hmmm, that's a shame - I had figured there must be a way to cast the value of form (from the query) to being the key of total, appreciate the assistance however! Back to the old drawing board :)

Collectives™ on Stack Overflow

return custom json from LINQ select statement

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related