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");
I just finished reading your blog post on querying Kusto databases with C# and getting the results as a strongly typed list. I appreciate the detailed explanation and code examples provided, which make it easy to understand and implement.
The core idea of your post is to help developers interact with Kusto databases using C# and retrieve results in a strongly typed manner. Your approach of creating an abstract class for Kusto response rows and a KustoRepository class to handle querying is well thought out. I also appreciate your suggestions for wrapping the query function with caching, retry engine, and asynchronous execution for better performance, reliability, and code style.
One of the highlights of your post is the clear and concise code examples, which are easy to follow and understand. However, I would recommend adding more comments within the code snippets to provide better context and explanation for those who might not be familiar with certain aspects of the code.
Regarding potential improvements, I noticed that you mentioned not to copy the code examples directly, but to use one's own retry engine and cache service. It would be helpful if you could provide some guidance or references on how to implement these components, as it might not be clear for some readers.
Overall, your blog post is informative and provides a great starting point for developers looking to work with Kusto databases in C#. Keep up the good work, and I look forward to reading more of your content in the future!