Linq Query Features With .NET 9 Alpha

Hi,

Today, we will talk about new coming Linq Query features with .Net 9 Alpha. There are three new coming features. They are “CountBy”, “AggregateBy” and “Index”.

Firstly let’s download .Net 9.0 Alpha from this link “https://github.com/dotnet/installer?tab=readme-ov-file"

We will create a Console application on Visual Studio 2022. And don’t forget to select .NET 9.0(Preview) for the .Net version.

We will work with the MSSQL server. And our test Database is Northwind. You can get the Create Northwind Database script from this link (When you run the script, it could give some errors. But you can ignore them. And The DB will be created properly.): https://borakasmer.com/projects/Northwind_Script.html

Now It Is Time To Create DAL Project

We will add a new Class Project to the solution. We will use DB First for this DAL application. In the beginning, we will install the below packages with Nuget Package Manager.

We will call the scaffold command for creating DBContext and Entities from existing Northwind DB. I created the Models folder. I came to under Models folder path. Call the below command from the terminal.

dotnet ef dbcontext scaffold "Data Source=.;initial catalog=Northwind;Trusted_Connection=True;Encrypt=False" Microsoft.EntityFrameworkCore.SqlServer --output-dir Models/DB

Not: If you don’t have “scaffold” command you have to install EntityFrameworkCore.Tools “Install-Package Microsoft.EntityFrameworkCore.Tools”

Now our DAL project and NorthwindContext are ready as seen above :)

CountBy

The first example is before .Net 9.0 We will group Orders by CustomerId and find the total orderCount per Customer by using .Net 8.0 Linq Query as seen below.

var ordersCount = dbContext.Orders
    .GroupBy(order => order.CustomerId)
    .Select(group => new { Customer = group.Key, Count = group.Count() });

foreach (var order in ordersCount)
{
    Console.WriteLine($"Customer: {order.Customer}, Count: {order.Count}");
}

Sql Result

Sql Query: If you call the “ordersCount.ToQueryString()” method at the end of the query, you can take Raw SQL Query as seen below.

SELECT [o].[CustomerID] AS [Customer], COUNT(*) AS [Count]
FROM [Orders] AS [o]
GROUP BY [o].[CustomerID]

With .Net 9.0, we will do the same thing with CountBy(): It groups data by selector field and returns an enumeration of KeyValuePairs like a dictionary. In this example, the “key” is the object that is used for grouping the elements and the “value is the count of the elements in the group.

var data = dbContext.Orders.OrderBy(o => o.CustomerId).ToList()
.CountBy(order => order.CustomerId);

foreach (var order in data)
{
    Console.WriteLine($"Customer: {order.Key}, Count: {order.Value}");
}

AggregateBy

The first example is before .Net 9.0, the AggregateBy() method is a little different from the CountBy() method. Instead find the count of elements we will aggregate them. We will group “OrderDetails” by “OrderId”. We will get the Total Price of every Order which has OrderId < 10255.

    var orderDetails = dbContext.OrderDetails
        .Where(orderDetail => orderDetail.OrderId < 10255)
        .GroupBy(orderDetail => orderDetail.OrderId)
        .Select(group => new { Order = group.Key, TotalPrice = group.Sum(order=>order.Quantity*order.UnitPrice) });
    foreach (var orderDetail in orderDetails)
    {
        Console.WriteLine($"Order: {orderDetail.Order}, Total Price: {orderDetail.TotalPrice}");
    }
}

Sql Query: “orderDetails.ToQueryString()”

SELECT [o].[OrderID] AS [Order], COALESCE(SUM(CAST([o].[Quantity] AS money) * [o].[UnitPrice]), 0.0) AS [TotalPrice]
FROM [Order Details] AS [o]
WHERE [o].[OrderID] < 10255
GROUP BY [o].[OrderID]

With .Net 9.0, we will do the same thing with AggregateBy(): With “AggregateBy()” we will group elements with OrderId. “seed” is our beginning value of “totalPrice”. We will add every (Quantity * UntiPrice) result to the “TotalPrice”. So we will aggregate all of the product prices by OrderID which OrderId < 10255 and will get the TotalPrice result.

 var orderDetails = dbContext.OrderDetails
     .Where(orderDetail => orderDetail.OrderId < 10255).ToList()
     .AggregateBy(
     order => order.OrderId,
     seed:decimal.Zero,
     (totalPrice, order) => totalPrice + order.Quantity * order.UnitPrice);

 foreach (var orderDetail in orderDetails)
 {
     Console.WriteLine($"Order: {orderDetail.Key}, Total Price: {orderDetail.Value}");
 }

We will get the same “GroupBy” query result with AggregateBy() as seen below.

Result

Index

Actually, the “Index” is not a big feature. We can get an Index of any collection by foreach as seen below with most of the .Net version.

var top10CustomersByCity = dbContext.Customers.AsNoTracking()
    .Select(customer => new { Customer = customer.CustomerId, City = customer.City })
    .OrderBy(customer => customer.Customer)
    .Take(10).ToList();
foreach (var (index, customer) in top10CustomersByCity.Index())
{
    Console.WriteLine($"Index: {index}, {customer}");
}

Result

With .Net 9.0 we can get index every item with the “Select()” method as seen below.

foreach (var (item,index) in dbContext.Customers.AsNoTracking().Take(10).ToList()
    .Select((item,index) => (new { Customer = item.CustomerId, City = item.City }, index)))
{
    Console.WriteLine($"Index: {index}, {item}");
}

Conclusion:

We can already find these features with Superlinq. But soon, without a third-party tool, we would use these features natively. In the end, while using these functions, I will not worry about performance and the feature support :)

These features may not be a magic stick, but they will definitely make our jobs much easier in our daily lives.

See you until the next article.

“If you have read so far, first of all, thank you for your patience and support. I welcome all of you to my blog for more!”

Source:

.NET 9 Planning Milestone · dotnet/aspnetcore

Three new LINQ methods in .NET 9

.NET9 Alpha - LINQ updates