Path Expressions
Introduction
In Starcounter SQL you can refer to any property of a database classes as long as there is a path from the database class specified in the
FROM clause to that property.A path expression is an arbitrary long sequence of identifiers separated by dots. The first identifier in the sequence should be an alias to an database class defined in the
FROM clause of the SELECT statement. The following identifiers except the last one should have object references as return values. The last identifier may return any supported datatype. For example:1
SELECT e.Manager.Department.Location.Street FROM Employee e
Copied!
If an identifier in a path expression returns
null then the complete path expression will return null.Path expression can also be used in
WHERE and ORDER BY clauses:1
SELECT e FROM Employee e WHERE e.Manager.Department.Name = 'sales'
2
SELECT e FROM Employee e ORDER BY e.Manager.Department.Profit
Copied!
Wildcards
You can use a wildcard (*) to select all properties of a database class:
1
SELECT * FROM Employee
2
SELECT e.* FROM Employee e
Copied!
The above queries return all the properties of the
Employee objects, while the below query returns references to the Employee objects themselves.1
SELECT e FROM Employee e
Copied!
Cast Operation
In some path expressions you need to cast the type of a property.
For example, consider a data model like this:
1
[Database]
2
public class Person
3
{
4
public Person Father { get; set; }
5
}
6
​
7
[Database]
8
public class Employee : Person
9
{
10
public Employee Manager { get; set; }
11
}
Copied!
If you want to select the manager of each person's father whenever such manager exists, then this is incorrect since
Father is of type Person and Person has no Manager property:Incorrect
1
SELECT p.Father.Manager FROM Person p
Copied!
However, if you cast
Father to type Employee then you can continue the path expression with Manager:Correct
1
SELECT CAST(p.Father AS Employee).Manager FROM Person p
Copied!
If the object reference
Father for some objects in the extent Person is not of type, or subtype of Employee , then this object reference can't be cast to Employee and the operation returns null.The cast operation only supports casts between different types of database objects and not between different value types.
Last modified 4yr ago
Copy link