Convert a standard EF list to a nested JSON

Question

I have a WEB Method in my ASPX page that retrieves a List from SQL DB using Entity Framework.

    Using rep As New RBZPOSEntities
        q = rep.getSalesByLocation(fDate, tDate).ToList
    End Using

Thereafter i use javascriptserializer to convert this list into a JSON string

    Dim jss As JavaScriptSerializer = New JavaScriptSerializer()
    Dim json As String = jss.Serialize(q)

So the above works great and i can use AJAX on the client side to display the results successfully.

The problem i am having now is converting a Flat List into a Nested JSON string. So consider a list like:

locationName as string
MonthName as string
totalAmount as string

Which needs to be converted into a JSON like this:

[{locationName:'Tokyo',totalAmount:[100,200,300,400]},
 {locationName:'New York',totalAmount:[500,600,700,800]}]

So the totalAmount values in the above case correspond to totalAmounts for a Location for a specific month. E.g. Tokyo total amount in January is 100, in February is 200 and etc.

What i can do: I can create a nested list and populate it with results from EF and then serialize to JSON.

What i am asking: Is there any other cleaner way to do it.

Thank you

Answer 1

As Todd has already said you need to convert your flat list into a list of another intermediate type. Let's call your existing Type the "InputType" and the Type you want the "OutputType"

Public Class InputClass
  Public locationName as String
  Public MonthName as String
  Public totalAmount as String
End Class

Public Class OutputClass
  Public LocationName As String
  Public TotalAmount As List(Of String)

  Public Sub New(groupedInputClass As IGrouping(Of String, InputClass))
    LocationName = groupedInputClass.Key
    TotalAmount = groupedInputClass.Select(Function(x) x.totalAmount).ToList()
  End Sub
End Class

Then all you need to do is transform a List of InputType into a List of OutputType which is really easy with LINQ.

Public Class MappingHelper
  Public Function Map (inputList as List(Of InputClass)) As List(Of OutputClass)

    Dim groupedByLocation = inputList.GroupBy(Function(k) k.LocationName)

    Dim nestedLocations = groupedByLocation.Select(Function(x) new OutputClass(x)).ToList()

    Return nestedLocations
  End Function
End Class