InternetException

HLinq 0.13.0 released - grouping, cultures and database functions

· 597 words · ~2 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.13.0 is out on nuget.org and it is the biggest release since the beginning. Here is what is new.

Grouping

The big one: groupBy returns list of objects with keys and collection of rows for that key:

GET /data/persons?groupBy[x.Status]

[ { "Key": "New", "Rows": [ {"id": 1, ...}, {"id": 2, ...} ] },
  { "Key": "Paid", "Rows": [ {"id": 3, ...} ] } ]

A select used after groupBy limits data in rows of each group:

GET /data/persons?groupBy[x.Status].select[x.Name]

answers [{"Key": "New", "Rows": ["Ann", "Bob"]}, {"Key": "Paid", "Rows": ["Cat"]}].

take, skip, orderBy, where and count written after a groupBy work on groups, not on rows - take[2] takes two groups, count[] returns how many groups there are.

You can also use multiple properties for a key (groupBy[x.Vendor,x.Status]), and a second groupBy groups the rows of every primary group and result have nested groups:

GET /data/persons?groupBy[x.Status].groupBy[x.Type]

[{"Key": "New", "Rows": [{"Key": "A", "Rows": ["Ann"]}, {"Key": "B", "Rows": ["Bob"]}]}]

Setting values format

In previous version of HLinq numbers were always formatted in invariant culture, which is really English culture. So i.e. using fraction number like “1.200” with Polish translation of “where” would produce number 1.2 but for Polish person it would be expected to get 1_200 instead. A bit akward. In new version it is possible to specify culture inside a query:

GET /data?culture=pl-PL&gdzie[x.Decimal>"1,2"]
or
GET /data?culture=pl-PL&where[x.Decimal>"1,2"]

In above 1,2 is one and a fraction. Without the culture specified query is using the default, invariant culture (which gives “12” instead). culture parameter decides of format of numbers and dates and allows to use translated keywords.

Using translations

You can add translations to HLinq:

builder.Services.ConfigureHLinq(c => c.AddPolishToHLinq());

Then your users can use keywords that are easier to understand for them:

GET /data?culture=pl&gdzie[x.name==Jan]

The English words (default invariant culture) can be mixed along translated ones. The Polish package comes with gdzie, wybierz, grupujPo, wez, pomin, zlicz, sortujPo and others; HLinq does not have other built-in translations yet but you may add it yourself in pretty easy way: oneliner per keyword AddTranslatedKeyword<Where>("pl", "gdzie").

Database functions

Previous versions allowed to use limited set of DB functions:

  • only the ones that returns true/false
  • only one entity property per call
  • only one source class with basic functions was registered by default (no specialized string, json or other functions allowed)

Now there is possibility of comparing function call to value and using multiple properties of entity in one function:

GET /data?where[DateDiffDay(x.DateOfBirth, x.DateOfDeath)>36500]
GET /data?where[TrigramsAreSimilar(x.FirstName, x.LastName)]

Refer here for details.

More databases

Beside Postgres HLinq now have packages for SQL Server, MySQL, MariaDB and SQLite. To install it use just one-liner:

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

Warnings

HLinq tries its best to work out properties or methods from your query. But sometimes it is not possible. I.e. when your entity will have two properties: name and NAME (which is totally possible in C#) and you will write a query like:

GET /data/persons?select[x.Name]

engine will not know which property exactly you are refering too. It will use the one that is most probable, based on name and inheritance and comparison value (in where) and will emit warning in HTTP header.

Fixes worth mentioning

  • A quoted value reaches the type it is compared to (where[x.Int>"5"] compares a number, not a string).
  • An enum reads a number as a value, not as text.
  • Both DateTime and DateTimeOffset are answered with the serializer’s ISO text.

Try it

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

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