Hazelcast is a powerful real-time stream processing platform that can scale, enrich and process large data volumes of fresh, new and historical data. As every enterprise in this day and age has built numerous “haystacks” of data and seeking the needle hidden in each. Thankfully, Hazelcast has great query abilities to help you find the needle – at the right time – with SQL. To simplify the search for our users, we have implemented the LINQ to SQL provider for Hazelcast .Net Client to make SQL easier for the .NET platform.
I will explain the LINQ usage for Hazelcast .Net Client but before that, we need a cluster to connect. You can download the Hazelcast binary or build it yourself, however, I will use Hazelcast Viridian Serverless solution; ready for action, with no infrastructure to worry about. You can sign-up and create a free cluster in a few minutes.
For Viridian users, after you have signed up and created a cluster, you will see a “Connect Client“ button.
Viridian Connect Client
You can download an example client ready to run with your cluster or retrieve the credentials to connect to the cluster. I choose the second option because I want to create my own project. To reach your credentials, click the “Advanced setup“ at the top.
This screen has everything you need to connect the cluster. Keep the window open, we will need it later.
Okay, we launched the cluster with almost no effort. Now, it is time to connect to it.
First, let’s create a new .NET project named as HzLinqUsage.
dotnet new console -n HzLinqUsage
Then, go to the project folder and add dependencies to the Hazelcast.Net NuGet package, and to the new Hazelcast.Net.Linq.Async NuGet package, which provides LINQ.
cd HzLinqUsage
dotnet add package Hazelcast.Net
dotnet add package Hazelcast.Net.Linq.Async
dotnet add Microsoft.Extensions.Logging.Console
Note that we also add a dependency to the Microsoft.Extensions.Logging.Console package as we want to use the Hazelcast .NET client logging abilities to view the SQL queries emitted by LINQ.
Now, open the project with your favorite IDE. I can demonstrate the new features with a simple data type but real life is not always simple. So, let’s do something more fancy. I will create a map which hold academy awards as keys and best pictures of that award as values.
You can create the data models as classes or records. It doesn’t matter because with Hazelcast 5.2 Compact serialization, I do not need to handle the serialization, and can let the Hazelcast .NET Client do it with zero configuration. If you are curious about Compact, I strongly encourage you to look for details.
After having data models, here is the start of my Program.cs file, with the data records.
using Hazelcast;
using Microsoft.Extensions.Logging;
Console.WriteLine("Hello, World! Let's use LINQ over Hazelcast.");
public record AcademyAward(int Year, string Title)
{
public AcademyAward() : this(0, "") {} // parameter-less ctor
};
public record Film(string FilmName, string Director, int Budget)
{
public Film() : this("","", 0) {} // parameter-less ctor
}
Note that the LINQ provider only works with public properties, and requires that the class (or record) exposes a parameter-less constructor (this constraint may be lifted in the future).
Next, configure and create the client. It’s time to revisit the Viridian credentials page.
using Hazelcast;
using Microsoft.Extensions.Logging;
Console.WriteLine("Hello, World! Let's use LINQ over Hazelcast.");
var options = new HazelcastOptionsBuilder()
.With("Logging:LogLevel:Hazelcast", "Debug")
.With(opt =>
{
opt.ClusterName = "CLUSTER_ID";
opt.Networking.Cloud.DiscoveryToken = "DISCOVERY_TOKEN";
opt.Networking.Ssl.Enabled = true;
opt.Networking.Ssl.CertificatePassword = "KEY_STORE_PASSWORD";
opt.Networking.Ssl.CertificateName = "PATH_TO_CLIENT.PFX"; // You'll get it by "Download keystore file"
})
.With((configuration, opt) =>
{
opt.LoggerFactory.Creator = () => LoggerFactory.Create(loggingBuilder =>
loggingBuilder.AddSimpleConsole());
})
.Build();
await using var client = await HazelcastClientFactory.StartNewClientAsync(options);
public record AcademyAward(int Year, string Title)
{
public AcademyAward() : this(0, "") {} // default ctor
};
public record Film(string FilmName, string Director, int Budget, bool Liked)
{
public Film() : this("","", 0, false) {} // default ctor
}
Client is ready. Now, let’s put some data to query.
var map = await client.GetMapAsync<AcademyAward, Film>("bestPicture");
var a93 = new AcademyAward(2021, "93rd Academy Awards");
var a93_film = new Film("Nomadland", "Chloé Zhao", 15_000_000);
await map.PutAsync(a93, a93_film);
var a92 = new AcademyAward(2020, "92rd Academy Awards");
var a92_film = new Film("Parasite", "Bong Joon-ho", 5_000_000);
await map.PutAsync(a92, a92_film);
var a91 = new AcademyAward(2019, "91rd Academy Awards");
var a91_film = new Film("Green Book", "Peter Farrelly", 23_000_000);
await map.PutAsync(a91, a91_film);
Console.WriteLine("Put the data!");
Cluster, data and client is ready. Now, let’s run queries. Before moving on, we need to create a mapping for SQL, so that it can understand the data and its field.
await client.Sql.ExecuteCommandAsync(@"
CREATE OR REPLACE MAPPING bestPicture (
Year INT EXTERNAL NAME \"__key.Year\",
Title VARCHAR EXTERNAL NAME \"__key.Title\",
FilmName VARCHAR,
Director VARCHAR,
Budget INT
)
TYPE IMap OPTIONS (
'keyFormat' = 'compact', 'keyCompactTypeName' = 'award',
'valueFormat' = 'compact', 'valueCompactTypeName' = 'film'
)
");
While mapping fields of the object, you should be careful about naming and casing. They must match with class/record properties.
Now, we can use LINQ.
var likedFilmNames = map.AsAsyncQueryable()
.Where(p=> p.Value.Liked)
.Select(p=> p.Value.FilmName);
await forearch(var name in likedFilmNames)
Console.WriteLine($"-Liked {name}");
Here, we filtered the the entries with Where in the map by Liked property. Note that, since we are working on a map Key and Value fields appeared as expected. We reached the Film object by Value of the entry. Later, with Select we projected the FilmName to a string enumerable.
Since the Hazelcast .NET Client as well as the LINQ Provider are async, we fetch the entries by awaiting. When await foreach will call the MoveNextAsync of likedFilmNames, the SQL query will be prepared and will be executed on the server. Currently, it uses the default SQL options of the for execution and fetching.
What was the case before LINQ Provider?
Let’s look at it;
var sqlFilmNameResult = await client.Sql.ExecuteQueryAsync("Select FilmName From bestPicture Where Liked == TRUE");
await foreach(var row in sqlFilmNameResult)
Console.WriteLine($"-Liked { row.GetColumn("FilmName")}");
It’ still okay but more error prone because string is involved. Also, we need to deal reading the column unless you fetch the whole row as object, deserialize and cast it.
Here some other examples,
Entries which has film budget more than 10,000,000.
var bigBudgetOnes = map.AsAsyncQueryable()
.Where(p=> p.Value.Budget > 10_000_000);
await forearch(var entry in bigBudgetOnes)
Console.WriteLine($"-Budget of {entry.Value.FilmName} is ${entry.Value.Budget:N1}.");
Custom projection of entry’s title is “91rd Academy Awards“.
var awards93= map.AsAsyncQueryable()
.Where(p=> p.Key.Title == "91rd Academy Awards"")
.Select(p=> new { Film=p.Value.FilmName, Director=p.Value.Director });
await forearch(var entry in awards93)
Console.WriteLine($"93rd Best Picture: {entry.FilmName} is directed ${entry.Director}.");
Currently, LINQ provider is beta and has limited functionalities compare to the full SQL interface. However, we are planning to enlarge its abilities. Your feedbacks are important guide for us, please contact us via GitHub or Slack Community channel if you have any questions.
If you curious about Hazelcast .NET Client, please don’t forget to check out the repo and examples.
