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");

Ingest

To ingest a list of a collection to Kusto, you need to convert the collection into a format that Kusto can ingest. One common approach is to use a DataTable to represent the collection. Here’s how you can do it:

  1. Install the necessary package:

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

  2. Create a class to handle the ingestion:

    using Microsoft.Azure.Kusto.Data;
using Microsoft.Azure.Kusto.Ingest;
using System.Data;

public class KustoIngestService
{
    private IKustoIngestClient _kustoIngestClient;
    private string _database;

    public KustoIngestService(string kustoUri, string database, string appId, string appKey, string tenantId)
    {
        var kustoConnectionStringBuilder = new KustoConnectionStringBuilder(kustoUri)
            .WithAadApplicationKeyAuthentication(appId, appKey, tenantId);
        _kustoIngestClient = KustoIngestFactory.CreateDirectIngestClient(kustoConnectionStringBuilder);
        _database = database;
    }

    public async Task IngestDataAsync(DataTable dataTable, string tableName)
    {
        var ingestionProperties = new KustoIngestionProperties(_database, tableName);
        var dataStream = new DataReaderSource(dataTable.CreateDataReader());

        await _kustoIngestClient.IngestFromDataReaderAsync(dataStream, ingestionProperties);
    }
}

  3. Convert your collection to a DataTable:

    using System;
using System.Collections.Generic;
using System.Data;

public class MyData
{
    public int Id { get; set; }
    public string Name { get; set; }
    public DateTime Timestamp { get; set; }
}

public static class DataTableExtensions
{
    public static DataTable ToDataTable<T>(this IList<T> data)
    {
        PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
        DataTable table = new DataTable();

        foreach (PropertyDescriptor prop in properties)
        {
            table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
        }

        foreach (T item in data)
        {
            DataRow row = table.NewRow();
            foreach (PropertyDescriptor prop in properties)
            {
                row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
            }
            table.Rows.Add(row);
        }
        return table;
    }
}

  4. Use the ingestion service in your application:

    using System;
using System.Collections.Generic;
using System.Threading.Tasks;

public class Program
{
    public static async Task Main(string[] args)
    {
        var kustoUri = "https://yourcluster.kusto.windows.net";
        var database = "yourdatabase";
        var appId = "your-app-id";
        var appKey = "your-app-key";
        var tenantId = "your-tenant-id";

        var ingestService = new KustoIngestService(kustoUri, database, appId, appKey, tenantId);

        var data = new List<MyData>
        {
            new MyData { Id = 1, Name = "Item1", Timestamp = DateTime.UtcNow },
            new MyData { Id = 2, Name = "Item2", Timestamp = DateTime.UtcNow }
        };

        var dataTable = data.ToDataTable();
        await ingestService.IngestDataAsync(dataTable, "your-kusto-table");
    }
}

In this example:

  1. KustoIngestService: Handles the ingestion of data into Kusto.
  2. DataTableExtensions: Provides an extension method to convert a list of objects to a DataTable.
  3. Program: Demonstrates how to use the service to ingest a collection of data into Kusto.

By converting the list to a DataTable and using the ingestion service, you can ingest your collection of data into a Kusto table.