InternetException

HLinq 0.14.0 released - aggregates, nested selects and methods in select

· 654 words · ~3 min read · in .NET, ASP.NET, Hamster Wheel

#HLinq #LINQ #NuGet #API design

HLinq is a library that turns a query string into a LINQ expression and runs it against your database. Version 0.14.0 is out on nuget.org . Grouping got aggregates, select got nested selects and method calls, and a value written in a query finally reaches the parameters of a database function. Here is what is new.

Aggregates over groups

A groupBy from the previous release returns Key and Rows per group. Now it can return numbers too! The same five names LINQ folds a collection with:

GET /data/persons?groupBy[x.Status].where[x.Count()>5]
GET /data/persons?groupBy[x.Status].orderBy[x.Max(x.Age)]
GET /data/persons?groupBy[x.Status].select[Count=x.Count(),Max=x.Max(x.Age)]

The first is translated to SQL HAVING and returns groups with at least 5 items, the second (SQL ORDER BY) orders groups over the aggregate of their rows and the third returns the values beside Key and Rows. It is happening in the db entirely, nothing is computed in memory. Count() and Any() can be used without an argument, the others can be run over the property they fold (x.Max(x.Age)).

Similarly you can use aggregates on a collection property without any grouping. It returns the result of the aggregate function over this collection:

GET /data/persons?select[N=x.Addresses.Count()]

It answers the question “how many addresses does each person have” in one query, one round trip, no N+1.

Select can now shape sub-entities and sub-collections instead of returning them as-is:

GET /data/persons?select[x.Name,addresses=x.Addresses.select(a.Street,a.City)]

[{ "Name": "Ann", "addresses": [ {"Street": "Main 1", "City": "Warsaw"} ] }]

There is a depth limit (country=a.Country.select(c.Name)) and the database still answers it in one statement - a LEFT JOIN. The first letter of an entity inside the brackets says which row a value comes from: a.City is the address’s city, x.Name is still the person’s name, so you can mix both levels. There is no way to give a sub-entity or a sub-collection its own letter for now. Using where or orderBy on sub-collections is also not supported for now.

Methods in select

Selection can be done with the use of a method, exactly the way a filter could already (giving them a new name also works):

GET /data/persons?select[U=x.Name.ToUpper(),S=x.Name.Substring(0,2)]
GET /data/persons?select[x.Name.ToUpper()]
GET /data/persons?where[Reverse(x.Name)==ylliB]

A method called on a property is an instance method of that property’s type; a method called without a source is a database function. An unnamed call is named after the method - x.Name.ToUpper() property name is ToUpper - unless you assign something else. The server translates what it can, so ToUpper becomes SELECT upper(...), and what cannot be translated fails at the database exactly like the same call written by hand in LINQ does.

Writing values into function parameters

You can use values in functions now, not just properties of the row:

GET /data?where[DateDiffDay(x.DateOfBirth, 2020-01-01)>3650]
GET /data?where[Distance(x.DateTime, 2020-01-01)>00:02:00]
GET /data?where[ToDate(x.BornText,yyyy-MM-dd)>2020-01-01]

A written date, moment or TimeSpan is parsed using the culture of the request (and is automatically converted to destination type). When a function has several overloads, the way the value is written is used to pick between them: 2020-01-01 goes to the DateOnly overload, 2020-01-01T14:30 to the DateTime one.

One MySQL package

There used to be a pack for MySQL and a second one for MariaDB. They collapsed into one - HamsterWheel.HLinq.MySql serves both servers:

builder.Services.AddHLinq().AddMySqlToHLinq();

The MariaDB pack is gone from nuget.org with this release; its functions live in the MySql one now. Each pack’s documentation (docs/db-functions ) is now generated from tests of what the DB engine actually supports, so the tables say what is possible and what is not and why.

Fixes worth mentioning

  • Negative paging bounds are refused by HLinq (take[-5], skip[-1]) instead of traveling to the database and coming back as a 500.
  • A quoted value of two quotes ("") is those quotes, not an empty value.
  • A written moment keeps its own text against moment-typed parameters.

Try it

You can try it now yourself:

<PackageReference Include="HamsterWheel.HLinq.AspNet" Version="0.14.0" />

The source lives in mirror on GitHub , and the README documents every feature shown above.

Happy Querying!