C# deserialize JSON from Power BI REST API

In two last posts we configured:

• an access to Power BI REST API through a service principal: Power BI REST API — configuration
• made a few requests from a C# application: C# and Power BI REST API

Today, we will parse a JSON string returned by one of the calls. Our example is a list of datasets in a workspace.

This is a cut down example of a JSON response:

{
    "@odata.context": "https://wabi-west-europe-e-primary-redirect.analysis.windows.net/v1.0/myorg/groups/7*****a/$metadata#datasets",
    "value": [
        {
            "id": "4*****5",
            "name": "Iowa_Liquor_Sales_Power_Automate",
            "webUrl": "https://app.powerbi.com/groups/7*****a/datasets/4*****5",
            "addRowsAPIEnabled": false,
            "configuredBy": "m*****@*****.com",
            "isRefreshable": true,
            "isEffectiveIdentityRequired": false,
            "isEffectiveIdentityRolesRequired": false,
            "isOnPremGatewayRequired": false,
            "targetStorageMode": "Abf",
            "createdDate": "2021-08-07T11:24:43.497Z",
            "createReportEmbedURL": "https://app.powerbi.com/reportEmbed?config=ey*****d",
            "qnaEmbedURL": "https://app.powerbi.com/qnaEmbed?config=ey*****d",
            "upstreamDatasets": [],
            "users": [],
            "queryScaleOutSettings": {
                "autoSyncReadOnlyReplicas": true,
                "maxReadOnlyReplicas": 0
            }
        },
        {
            "id": "6*****6",
            "name": "Iowa_Liquor_Sales",
            "webUrl": "https://app.powerbi.com/groups/7*****a/datasets/6*****6",
            "addRowsAPIEnabled": false,
            "configuredBy": "m*****@*****.com",
            "isRefreshable": true,
            "isEffectiveIdentityRequired": false,
            "isEffectiveIdentityRolesRequired": false,
            "isOnPremGatewayRequired": false,
            "targetStorageMode": "Abf",
            "createdDate": "2022-02-01T09:26:09.273Z",
            "createReportEmbedURL": "https://app.powerbi.com/reportEmbed?config=ey*****d",
            "qnaEmbedURL": "https://app.powerbi.com/qnaEmbed?config=ey*****d",
            "upstreamDatasets": [],
            "users": [],
            "queryScaleOutSettings": {
                "autoSyncReadOnlyReplicas": true,
                "maxReadOnlyReplicas": 0
            }
        }
    ]
}

Like in the previous article. We do an API call:

var reportsResponse = client.GetStringAsync($"https://api.powerbi.com/v1.0/myorg/groups/{workspaceId}/datasets");
string jsonString = reportsResponse.Result;

To deserialize the JSON we make two approaches. Both are pretty similar.

The first approach assumes we need all the data from the response. In order to extract everything, we need to create three classes:

• Datasets — a main object encloses an entire JSON,
• Dataset — a subset included in a Datasets “value” property,
• QueryScaleOutSettings — a subset included in a Dataset “QueryScaleOutSettings” property.

public class Datasets
{
    [JsonPropertyName("@odata.context")]
    public string OdataContext { get; set; }

    [JsonPropertyName("value")]
    public List<Dataset> Dataset { get; set; }

}
public class Dataset
{
    [JsonPropertyName("id")]
    public string Id { get; set; }

    [JsonPropertyName("name")]
    public string Name { get; set; }

    [JsonPropertyName("webUrl")]
    public string WebUrl { get; set; }

    [JsonPropertyName("addRowsAPIEnabled")]
    public bool AddRowsAPIEnabled { get; set; }

    [JsonPropertyName("configuredBy")]
    public string ConfiguredBy { get; set; }

    [JsonPropertyName("isRefreshable")]
    public bool IsRefreshable { get; set; }

    [JsonPropertyName("isEffectiveIdentityRequired")]
    public bool IsEffectiveIdentityRequired { get; set; }

    [JsonPropertyName("isEffectiveIdentityRolesRequired")]
    public bool IsEffectiveIdentityRolesRequired { get; set; }

    [JsonPropertyName("isOnPremGatewayRequired")]
    public bool IsOnPremGatewayRequired { get; set; }

    [JsonPropertyName("targetStorageMode")]
    public string TargetStorageMode { get; set; }

    [JsonPropertyName("createdDate")]
    public DateTime CreatedDate { get; set; }

    [JsonPropertyName("createReportEmbedURL")]
    public string CreateReportEmbedURL { get; set; }

    [JsonPropertyName("qnaEmbedURL")]
    public string QnaEmbedURL { get; set; }

    [JsonPropertyName("upstreamDatasets")]
    public List<int> UpstreamDatasets { get; set; }

    [JsonPropertyName("users")]
    public List<string> Users { get; set; }

    [JsonPropertyName("queryScaleOutSettings")]
    public QueryScaleOutSettings QueryScaleOutSettings { get; set; }
}

public class QueryScaleOutSettings
{
    [JsonPropertyName("autoSyncReadOnlyReplicas")]
    public bool AutoSyncReadOnlyReplicas { get; set; }

    [JsonPropertyName("maxReadOnlyReplicas")]
    public int MaxReadOnlyReplicas { get; set; }
}

And here is the code:

// 1: three classes without string operations
var datasets = JsonSerializer.Deserialize<Datasets>(jsonString);
var iowaDatasets = datasets.Dataset.Where(
    d => d.Name.Contains("iowa", StringComparison.OrdinalIgnoreCase) ||
    d.Name.Contains("eightfive", StringComparison.OrdinalIgnoreCase)).OrderBy(d => d.TargetStorageMode).Reverse();

Console.WriteLine(datasets.OdataContext);
foreach (Dataset dataset in iowaDatasets)
{
    Console.WriteLine($"Name: {dataset.Name}, Storage mode: {dataset.TargetStorageMode} : {dataset.QueryScaleOutSettings.AutoSyncReadOnlyReplicas}");
}

What is happening here? Firstly, we deserialize the JSON into a Datasets object. Then a list of Datasets.Dataset is filtered out and sorted.

Here is an outcome.

The second approach doesn’t use the first class (Datasets) and reads data directly into a list of Dataset objects. The original JSON response is modified with a substring function in order to generate an array of JSON objects.

// 2: two classes and string operations
int first = jsonString.IndexOf(@"value"":") + @"value"":".Length;
string jsonSubstring = jsonString.Substring(first, jsonString.Length - first - 2);

var datasets = JsonSerializer.Deserialize<List<Dataset>>(jsonSubstring);
var iowaDatasets = datasets.Where(
    d => d.Name.Contains("iowa", StringComparison.OrdinalIgnoreCase) ||
    d.Name.Contains("eightfive", StringComparison.OrdinalIgnoreCase)).OrderBy(d => d.TargetStorageMode).Reverse();

foreach (Dataset dataset in iowaDatasets)
{
    Console.WriteLine($"Name: {dataset.Name}, Storage mode: {dataset.TargetStorageMode} : {dataset.QueryScaleOutSettings.AutoSyncReadOnlyReplicas}");
};

Next steps like in the first approach filter out an order of the data with subtle differences in references to objects properties. The outcome is the same, without any information from the first part of the JSON — it was cut out, so it is not available here.

Here is the code on GitHub.