5 Advanced Queries

To illustrate some concepts, let's take the following database : a car belongs to a brand which belongs to a country. A country can have several brands and a brand can have multiple cars.

+--------------+ 1     1..n  +-------------+ 1    1..n   +--------------+         
|     Car      |------------ |    Brand    | ----------- |  Country     |         
|              |             |             |             |              |         
| Id           |             | Name        |             |  Id          |         
| Name         |             | Logo        |             |  CountryCode |         
| ReleaseYear  |             | CountryId   |             |  Name        |         
| BrandId      |             |             |             +--------------+         
| CreationDate |             +-------------+                                      
|              |                                                                  
+--------------+                                                                  

Include

Query can specify if it expects to get children from the request

await carService.QueryAsync(new BaseCarQuery() { IncludeStrings = new List<string>() { "Brand.Country" } });

will produce

SELECT "c"."ID", "c"."BrandId", "c"."CreationDate", "c"."Name", "c"."ReleaseYear", "b"."ID", "b"."CountryID", , "b"."Logo", "b"."Name", "c0"."ID", "c0"."CountryCode", "c0"."Name"
FROM "Car" AS "c"
INNER JOIN "Brand" AS "b" ON "c"."BrandId" = "b"."ID"
INNER JOIN "Country" AS "c0" ON "b"."CountryID" = "c0"."ID"

Projection

Projection is used for mapping a database entity to another object. Projection will only take properties that are useful for the mapping, ignoring the other.

SmallCarInfo is a class with only ID and Name as properties. So the call to this code

carService.QueryWithProjectionAsync<SmallCarInfo>(new BaseCarQuery() { });

wil generate the following query :

SELECT "c"."ID", "c"."Name" FROM "Car" AS "c"

Note that only mapped properties are part of the select.

Projection with functions

As explained above, projection optimize the generated sql, mainly on the returned properties. We can go a little bit further with the use of functions. On the Car object, we add a property, automatically computed : if the release year of the car is above 2000, then the bool IsCurrentCentury is true. Because it's not just a mapping, we must add the attribute Computed so that code knows he has to work smarter.

[Computed]
public bool CurrentCentury => ReleaseYear >= 2000;

The generated sql for this projection with a computed field is and the projected object will have the bool set.

SELECT "c"."ReleaseYear" >= 2000 AS "CurrentCentury", "c"."ID", "c"."Name"
FROM "Car" AS "c"

This only works on mapping a property based on simple function.

Filtering on a function is a little bit more tricky. On the entity object, we must define a function and a property. The following example add a new property IsModernCar based on a function that checks if the release year is greather than 2000.

public partial class Car : IBaseEntity
{
    public object DatabaseID => Id;

    public static Expression<Func<Car, bool>> ModernCarFunction => (x) => x.ReleaseYear > 2000;

    public bool IsModernCar => ModernCarFunction.Compile()(this);
}

The function is now usable on a query :

await carService.QueryAsync(QueryBuilder.Create<BaseCarQuery, Car>().Where(Car.ModernCarFunction).Build());

and the sql result will be

SELECT "c"."ID", "c"."BrandId", "c"."CreationDate", "c"."LastModificationDate", "c"."Name", "c"."ReleaseYear"
FROM "Car" AS "c"
WHERE "c"."ReleaseYear" > 2000

This is useful to keep at one place some entity logic

Counting

await carService.QueryAsync(new BaseCarQuery(), includeCount: true);

The request will produce 2 sql requests, one for count, the other for data

SELECT COUNT(*) FROM "Car" AS "c"
SELECT "c"."ID", "c"."BrandId", "c"."CreationDate", "c"."LastModificationDate", "c"."Name", "c"."ReleaseYear" FROM "Car" AS "c"