Create specific json response from linq to SQL query output?-CodePudding

I am working on Web API. I am getting data from one database table:

DispatchData dta = new DispatchData();

using (SalesDataContext oSqlData4 = new SalesDataContext())
{
    var result = (from x in oSqlData4.Finances
                  where (x.records.ID.Equals("12") ||
                         x.records.ID.Equals("123"))
                  where (x.Status == "Not Approved")
                  select x).ToList();

    foreach (var item in result)
    {
        dta.data = new string[] { item.Order_ID.ToString(), item.ID.ToString() };
    }

    var json = JsonConvert.SerializeObject(dta);
        
    return json;
}

public class DispatchData
{
    public string[] data;
}

This code is returning only one record:

{ "data": ["2508", "4684"] }

I want each row in array like this

{"data":[ ["2508","4684"],["2223","1123"],....] }

CodePudding user response：

Why not make return the result as strongly typed Model of List<DispatchData>:

Your DispatchData class:

public class DispatchData
{
    public string Order_ID {get;set;}
    public string ID {get;set;}
}

You can create a Root class to handle your JSON:

public class Root
{
  public List<DispatchData> data=new List<DispatchData>();
}

And you can return from your query like this:

Root dta = new Root();
using (SalesDataContext oSqlData4 = new SalesDataContext())
{
    dta.data = (from x in oSqlData4.Finances
                  where (x.records.ID.Equals("12") ||
                         x.records.ID.Equals("123"))
                  where (x.Status == "Not Approved")
                  select x).ToList();

    var json = JsonConvert.SerializeObject(dta);
        
    return json;
}

CodePudding user response：

For the result you want, your class is defined wrongly

public class DispatchData
{
    public string[][] data;
}

Then you can directly select the pair of IDs in an array, then nest them in another array, and assign it directly to dta.data

using (SalesDataContext oSqlData4 = new SalesDataContext())
{
    var dta = new DispatchData() {
        data = (from x in oSqlData4.Finances
                  where (x.records.ID.Equals("12") ||
                         x.records.ID.Equals("123"))
                  where (x.Status == "Not Approved")
                  select new[]{ x.Order_ID.ToString(), item.ID.ToString() }
               ).ToArray()
    };

    var json = JsonConvert.SerializeObject(dta);
        
    return json;
}