How to query Kusto (Azure data explorer) in C# and get strong type result?

Install Kusto client first.

<PackageReference Include="Microsoft.Azure.Kusto.Data" Version="9.2.0" />

And build an abstract class as a Kusto response row.

public abstract class KustoResponseRow
{
    public void SetPropertiesFromReader(IDataReader reader)
    {
        foreach (var property in this.GetType().GetProperties())
        {
            if (property.SetMethod != null)
            {
                property.SetValue(this, reader[property.Name]);
            }
        }
    }
}

Then you can create a new class named "KustoRepository".

Build a new KustoClient in its constructor. You'd better read the appId and appkey from configuration.

To get your app Id and app Key, you need to register it at Azure AD and allow it to access your Kusto (Azure data explorer) client.

this.kustoClient = KustoClientFactory.CreateCslQueryProvider(new KustoConnectionStringBuilder
{
    DataSource = "https://someinstance.westus.kusto.windows.net/somedatabase",
    ApplicationClientId = "appId",
    ApplicationKey = "appKey",
    Authority = "tennat-id",
    FederatedSecurity = true
});

And build your query function:

public List<T> QueryKQL<T>(string query) where T : KustoResponseRow, new()
{
    var result = new List<T>();
    var reader = this.kustoClient.ExecuteQuery("set notruncation;\n" + query);

    while (reader.Read())
    {
        var newItem = new T();
        newItem.SetPropertiesFromReader(reader);
        result.Add(newItem);
    }

    return result;
}

We suggest you wrap it with a cache service. (Better performance)

We suggest you wrap it with a retry engine. (Better reliability)

And we also suggest you wrap it with a `Task.Run()`. (Better code style)

It finally might be looking like this. (Don't copy those code. Please use your own retry engine and cache service.)

Finally, when you need to use it, just create a new class with expected response row type.

Example:

// Sample. Do NOT COPY!
public class PatchEventCore : KustoResponseRow
{
    public DateTime EndTime { get; set; }

    public string Machine { get; set; }

    public string WorkflowResult { get; set; }
}

And query now!

var eventsList = await patchRepo.QueryKQLAsync<PatchEventCore>(@"Patches
	| where PatchId == 'abcd'
	| sort by EndTime
	| project EndTime, Machine, WorkflowResult");