# Query

When performing OData queries with additional filtering,sorting, expanding, skiping etc., the powerGateServer will take care about returning the correct result.\
In case you additionally want to take care about handling those, the IExpression offers you all the information needed.

On almost all the interfaces we have a property "Base" of type Expression (Expression Tree).\
This property holds all the information about the request but its quite difficult to understand and use properly. Therfore we created a more easy usable API around it.

```{image} /img/plugins/creating_a_custom_plugin/uml_pgs.png
:width: 900px
```

## Filter

In case the query contains a \$filter Query option, the powerGateServer will pass the filter operations in "the IExpression\<T>.Where" property.\
Each of the filter clause has a PropertyName (e.g. FirstName), a Rule (And,Or) an Operator (Contains, Equals, IsEmpty) and a Value.

The property *Function* in the *WhereToken* exposes the used OData function in the *\$filter*.

:::{note}
The OData functions **substringof**, **endswith** and **startwith** can be used in the *IExpression* as [Operators](<plugins/creating_a_custom_plugin/query_and_iexpression:operator>).\
Therefore the function is not always set!
:::

**Examples**:

For the request *\$filter=substringof('A', FirstName) eq true* we could check following:

```csharp
public override IEnumerable<Person> Query(IExpression<Person> expression)
{
	return from whereToken in expression.Where
	where whereToken.PropertyName == "FirstName" &&
	        whereToken.Operator.Value == OperatorType.Contains &&
	        whereToken.Value == "A"
	select new Person {FirstName = "Tim"};

}
```

Following request *\$filter=toupper(Language) eq 'ENGLISH'* can be checked like this:

```csharp
public override IEnumerable<Language> Query(IExpression<Language> expression)
{
	return from whereToken in expression.Where
	where whereToken.PropertyName == "Language" &&
	                whereToken.Function.Name == "ToUpper" &&
	        whereToken.Operator.Value == OperatorType.Equals &&
	        whereToken.Value == "ENGLISH"
	select new Language("ENGLISH");
}
```

In case u need to *execute the filter* and check if an entity matches the filter criteria, use the Execute function:

```csharp
public override IEnumerable<Person> Query(IExpression<Person> expression) {
var angela = new Person();
	if(expression.Where.Execute(angela))
	...
```

## Operator

The IExpression\<T> supports following OData Operator Types:

| Operator                                   | Description           | Example                                                            |
|--------------------------------------------|-----------------------|--------------------------------------------------------------------|
| Eq                                         | Equal                 | /Suppliers?\$filter=Address/City eq ‘Redmond’                    |
| Ne                                         | Not equal             | /Suppliers?\$filter=Address/City ne ‘London’                     |
| Gt                                         | Greater than          | /Products?\$filter=Price gt 20                                   |
| Ge                                         | Greater than or equal | /Products?\$filter=Price ge 10                                   |
| Lt                                         | Less than             | /Products?\$filter=Price lt 10                                   |
| Le                                         | Less than or equal    | /Products?\$filter=Price le 100                                  |
| bool substringof(string po, string p1)     | Contains              | /Customers?\$filter=substringof(‘Alfreds’, CompanyName) eq true     |
| not bool substringof(string po, string p1) | DoesNotContain        | /Customers?\$filter=substringof(‘Alfreds’, CompanyName) eq false    |
| bool endswith(string p0, string p1)        | EndsWith              | /Customers?\$filter=endswith(CompanyName, ‘Futterkiste’) eq true    |
| not bool endswith(string p0, string p1)    | DoesNotEndsWith       | /Customers?\$filter=endswith(CompanyName, ‘Futterkiste’) eq false   |
| bool startswith(string p0, string p1)      | StartsWith            | /Customers?\$filter=startswith(CompanyName, ‘Alfr’) eq true         |
| not bool startswith(string p0, string p1)  | DoesNotStartsWith     | /Customers?\$filter=startswith(CompanyName, ‘Alfr’) eq false        |

## Rule

The IExpression\<T> supports following OData Rules:

| Rule | Description | Example                                          |
|------|-------------|--------------------------------------------------|
| And  | Logical and | /Products?\$filter=Price le 200 and Price gt 3.5 |
| Or   | Logical or  | /Products?\$filter=Price le 3.5 or Price gt 200  |

## Function

The IExpression\<T> supports following OData Functions:

| OperaFunctiontor          | Function.Name | Example                                                         |
|---------------------------|---------------|-----------------------------------------------------------------|
| string toupper(string p0) | ToTupper      | /Company?\$filter=toupper(CompanyName) eq 'ALFREDS FUTTERKISTE' |
| string tolower(string p0) | ToLower       | /Company?\$filter=tolower(CompanyName) eq 'alfreds futterkiste' |

## Expand

When the request contains the \$expand option the information can be retrieved from the Expand property on the expression.\
Expanding multiple properties is possible: Multiple ExpandToken´s will be created.\
When expanding Navigation Properties with a multi-level relationship, PropertyNames of the ExpandToken will return the navigation property path separated with a forward slash (e.g Products/Suppliers).

**Examples**:

Following request *\$expand=Address* can be checked like this:

```csharp
public override IEnumerable<PersonDetail> Query(IExpression<PersonDetail> expression)
{
	var person = new PersonDetail();
	var expandToken = expression.Expand.First();
	if(expandToken.PropertyNames == "Address")
	    person.Address = new Address{ ZipCode = "39011", Country = "Italy"};
	...
}
```

Following request *\$expand=PersonDetail/Address* can be checked like this:

```csharp
public override IEnumerable<Person> Query(IExpression<Person> expression)
{
	var person = new Person();
	var expandToken = expression.Expand.First();
	if(expandToken.PropertyNames == "PersonDetail/Address") {
	    person.PersonDetail = new PersonDetail {
	        Age = 24,
	        Address = new Address{ ZipCode = "39011", Country = "Italy" }
	    }
	}
	...
}
```

## OrderBy

When the request contains the \$orderby option, he information can be retrieved from the OrderBy property on the expression.\
The OrderBy clause is a collection of IOrderTokens, because a request could contain a chain of orderings (e.g.\$orderby=FirstName,LastName)\
The IOrderByToken holds a property OrderingMethod which also has functions for Descending sorting.\
Note that "\$orderby=FirstName asc" and "\$orderby=FirstName desc" can be used in the request.

### Examples

Passing a list of Person and executing OrderBy.\
The OrderBy function will be executed for every person and the sorted list will be returned:

```csharp
public override IEnumerable<Person> Query(IExpression<Person> expression)
{
	var persons = ...
	return expression.OrderBy.Execute(persons);
```

OrderBy is a collection of IOrderTokens, because a request can contain a chain of orderings: \$orderby=FirstName,LastName\
The OrderingMethod has also functions for Descening sorting. Not that "\$orderby=FirstName asc" and "\$orderby=FirstName desc" can be used in the request.

## Skip

When the request contains \$skip, the value can be retrieved from the SkipCount property on the expression.

## Top

When the request contains \$top, the value can be retrieved from the TopCount property on the expression.