Parsing Azure Analysis Response to a JSON in Azure function using ADOMDClient

Question

I am using Microsoft.AnalysisServices.AdomdClient.dll file to connect to Azure Analysis Service for executing DAX queries in Azure function and I need it to spit out in a JSON. Below is how am doing but when there are vast records I see delay in converting the response to json. Analysis service response in 2 secs but masking the response to json is taking more than 40secs. Can someone help suggesting a better way

AdomdCommand cmd = new AdomdCommand(query, _connect);
public List<Dictionary<string, object>> Results { get; } = new List<Dictionary<string, object>>();
var reader = cmd.ExecuteReader();
var schemeTable = reader.GetSchemaTable();
ISet<string> columnSet = new HashSet<string>();
foreach (DataRow row in schemeTable.Rows)
{
 String columnName = row[0].ToString();
 columnSet.Add(columnName);
}
while (reader.Read())
{
 Dictionary<string, object> columns = new Dictionary<string, object>();
 foreach (string columnName in columnSet)
 {
 var value = reader[reader.GetOrdinal(columnName)];
 if (value != null)
 {
  columns.Add(columnName, value);
 }
 else
 {
  columns.Add(columnName, null);
 }
}
 Results.Add(columns);
}
JsonConvert.SerializeObject(Results)

Answer 1

I have a sample for this on GitHub: microsoft/azure-analysis-services-http-sample. It streams the results from an AdomdDataReader to an output stream as JSON. The Stream can be a MemoryStream or (in my case) an HttpResponse stream.

public static async Task WriteResultsToStream(object results, Stream stream, CancellationToken cancel)
{

    if (results == null)
    {
        return;
    }

    if (results is AdomdDataReader rdr)
    {
        var encoding = new System.Text.UTF8Encoding(false);
        using (var tw = new StreamWriter(stream,encoding,1024*4,true))
        using (var w = new Newtonsoft.Json.JsonTextWriter(tw))
        {

            await w.WriteStartObjectAsync(cancel);
            var rn = "rows";

            await w.WritePropertyNameAsync(rn);
            await w.WriteStartArrayAsync(cancel);

            while (rdr.Read())
            {
                await w.WriteStartObjectAsync(cancel);
                for (int i = 0; i < rdr.FieldCount; i++)
                {
                    string name = rdr.GetName(i);
                    object value = rdr.GetValue(i);

                    await w.WritePropertyNameAsync(name, cancel);
                    await w.WriteValueAsync(value, cancel);
                }
                await w.WriteEndObjectAsync(cancel);
            }

            await w.WriteEndArrayAsync(cancel);
            await w.WriteEndObjectAsync(cancel);

            await w.FlushAsync();
            await tw.FlushAsync();
            await stream.FlushAsync();
        }
    }
    else if (results is CellSet  cs)
    {
        throw new NotSupportedException("CellSet results");

    }
    else
    {
        throw new InvalidOperationException("Unexpected result type");
    }

}