"Persistable queries" are queries that can be individually changed by the end user on the user interface and possibly also persisted for later reuse. The end user edits the query with the business object "gip.core.autocomponent.VBBSOQueryDialog ". It is opened either via the search icon in the ribbon bar or with the F3 key on a combo box.

The query dialog consists of four tabs, the following three of which are relevant:

The left half of the screen shows the properties that have been declared using the ACEntityProperty attribute in the partial entity class. The user can drag and drop the properties into the filter list to change the query. Logical connectives and relational operators can be set in the columns to formulate the query condition. With each additional line, a further query condition is formulated. With the button "New filter" additional lines can be added, for example to put brackets.

The sorting criteria can then be formulated in the sorting tab according to the same scheme:

Finally, a "WHERE" clause is formed from the filter list and an "ORDER BY" clause from the sorting list in the EntitySQL language, which can be viewed and adjusted in the last tab:

With the checkbox "Save configuration" the query can be persisted so that it will be restored the next time the business object is opened again. The four buttons next to the checkbox define whether the persistence applies to this computer, user or in general to everyone.

The following sections describe how persistable queries are declared, manipulated and used programmatically.

gip.core.datamodel.ACQueryDefinition

The cash register gip.core.datamodel.ACQueryDefinition is a serializable class. From its property values, it generates an Entity SQL expression that can be executed using "System.Data.Objects.ObjectQuery<T>" and expanded with additional LINQ commands. The ACQueryDefinition class has the following properties:

gip.core.datamodel.ACFilterItem:

 ACFilterItem corresponds to a filter line in the ACFilterColumns list.

T GetSearchValue<T>()

void SetSearchValue<T>(T value)

public enum LogicalOperators : short
{ 
// Undefined 
none = 0,
// Equal: = 
equal = 1,
// Not equal: != 
notEqual = 2,
// Less than: < 
lessThan = 3,
// The greater than: > 
greaterThan = 4,
// Less than or equal: <= 
lessThanOrEqual = 5,
// Greater than or equal: >= 
greaterThanOrEqual = 6,
// String.StartsWith(x); SQL: LIKE 'x%' 
startsWith = 7,
// String.EndsWith(x); SQL: LIKE '%x'
endsWith = 8,
// string.Contains(x); SQL: LIKE '%x%' 
contains = 9,
// The is null operator 
isNull = 10,
// The is not null operator 
isNotNull = 11,
// !string.Contains(x); SQL: NOT LIKE '%x%' 
notContains = 12,
}

public enum Operators : short
{
 none = 0,
 or = 1,
 and = 2,
}

gip.core.datamodel.ACSortItem:

ACSortItem corresponds to a sort line in the ACSortColumns list.

Before you can use or instantiate a storable query (ACQueryDefinition), a registration for the iPlus framework  must be carried out with the  ACQueryInfoPrimary - or ACQueryInfo - class. Use ACQueryInfoPrimary above the class declaration of an entity framework class because it defines the primary query for a database table. If you need further queries for the same database table, use the ACQueryInfo class, which is usually declared above a business object (derived from ACBSO).

Register an ACQueryDefinition with ACQueryInfo

The constructor has eight parameters:

Const.QueryPrefix + "Material"

During the registration process, new iPlus type definitions (table ACClass) are created in the database for the ACQueryInfo declaration as well as for ACClassInfo declarations for ACComponents. They appear in the development environment in the iPlus tree under the "Queries" section:

To instantiate an ACQueryDefintion, use the "CreateQuery" method of the Queries instance:

ACQueryDefinition acQueryDef = Root.Queries.CreateQuery(null, Const.QueryPrefix + "Material", ComponentClass.ACIdentifier);

The parent object is passed in the first parameter. Pass here null or the this pointer of the ACComponent in which you make this call. (For hierarchical queries, the parent query definition is passed in the child ACQueryDefinition).

The second parameter is the unique name of the query class, which was registered in the ACQueryInfo attribute class with the acName parameter.

The last parameter specifies the key name under which this ACQueryDefinition should be saved and read when it is used again (see the topic "Saving the configuration" in the first section). Usually you use the class name of the ACComponent in which you use this storable query (ComponentClass.ACIdentifier).

Primary storable queries

ACComponent or derivations of ACBSO (business objects) are made  known to the IPlus framework via the  ACClassInfo attribute. The seventh parameter "string qryConfig"  in the constructor specifies the name of the primary ACQueryDefinition to describe which primary table the business object works with. For a business object class that is used to manage the material master data, the parameter qryConfig is set with the value "QRYMaterial" (or Const.QueryPrefix + "Material").

For the instantiation of a primary ACQueryDefinition, Queries provides another, alternative method "CreateQuerybyClass()":

ACQueryDefinition acQueryDef = Root.Queries.CreateQuerybyClass(null, ComponentClass.PrimaryNavigationquery(), ComponentClass.ACIdentifier);

The ACClass of the primary storable query is passed directly by calling the auxiliary method PrimaryNavigationquery().

You are free to choose which CreateQuery method you use. This meta description, which is the primary ACQuery definition for an ACCompoent class, is primarily for the WPF controls

• VBComboBox,
• VBListBox,
• VBTreView
• and VBDataGrid

from the "gip.core.layoutengine" assembly necessary. VBComboBox is mainly used to set a navigation property (foreign key field in a table). An ACQueryDefinition instance is also generated automatically so that the data record selection in the combo box can be reduced and the user can search using the F3 key. By default, the ComboBox searches for the primary ACQueryDefinition, unless something else has been explicitly defined by the programmer.

ACAccess<T>

With the instantiation of an ACQueryDefinition, the description of the database query has now been made possible, but the data must still be queried in the database. The class  "gip.core.datamodel. ACAccess<T>" is responsible for executing the persistable query.

You can obtain an ACAccess instance by calling the "ACQueryDefinition.NewAccess()" method:

ACAccess<Material> acAccess = acQueryDef.NewAccess<Material>(Material.ClassName, this);

Then call one of the overloaded methods "NavSearch(...)" and pass a database context on which the query is to be executed:

bool succ = acAccess.NavSearch(dbContext);
bool succ = acAccess.NavSearch(dbContext, MergeOption.OverwriteChanges);

The query result is written in the property  public IList<T> NavList :

 foreach (Material mat in AccessPrimary.NavList)
 {
    // do something
 }

ACAccessNav<T>

The derived class ACAccessNav<T> extends the ACAccess<T> by navigation methods:

• public void NavigateFirst();
• public void NavigateLast();
• public void NavigateNext();
• public void NavigatePrev();
• T Current { get; }

ACAccessNav<T> is used for navigable business objects that are derived from the base class ACBSONav. You get an instance by calling the NewAccessNav method:

ACAccessNav<Material> _AccessPrimary = acQueryDef.NewAccessNav<Material>(Material.ClassName, this);

In your derivation, override the AccessNav property to return your instance of ACAccessNav<T>:

public override IAccessNav AccessNav { get { return AccessPrimary; } }

If your business object is opened and displayed on the surface, the section on navigation with the navigation keys is visible in the VBRibbonBar, since the overwritten property AccessNav now returns an instance of the interface IAccessNav that implements ACAccessNav<T>. The navigation buttons are automatically bound to the four navigation methods via CommandBinding.

Global search word

If the Enter key is pressed in the search text box in the ribbon bar, another CommandBinding is executed, which is bound to the "Search()" method of the business object. You must explicitly declare this method and implement it according to the following example:

[ACMethodCommand(Material.ClassName, "en{'Search'}de{'Suchen'}", (short)MISort.Search)]
public void Search()
{
   AccessPrimary.NavSearch(DatabaseApp, DatabaseApp.RecommendedMergeOption);
   OnPropertyChanged("MaterialList");
}

[ACPropertyList(9999, Material.ClassName)]
public IList<Material> MaterialList
{
 get
 {
   return AccessPrimary.NavList;
 }
}

The MaterialList contains the query result and is usually bound to a VBDataGrid in the Explorer window. In order for the VBDataGrid to display the query result, trigger the PropertyChanged event by calling the OnPropertyChanged (...) method.

Change filter conditions

Example of adding filter conditions:

// Open bracket:
acQueryDef.ACFilterColumns.Add(new ACFilterItem(Global.FilterTypes.parenthesisOpen, null, Global.LogicalOperators.none, Global.Operators.and, null, true));

// Filter with empty searchword (null):
acQueryDef.ACFilterColumns.Add(new ACFilterItem(Global.FilterTypes.filter, "MaterialNo", Global.LogicalOperators.contains, Global.Operators.or, null, true, true));

// Filter with default-searchword "ABC":
acQueryDef.ACFilterColumns.Add(new ACFilterItem(Global.FilterTypes.filter, "MaterialName1", Global.LogicalOperators.contains, Global.Operators.or, "ABC", true, true));

// Close bracket:
acQueryDef.ACFilterColumns.Add(new ACFilterItem(Global.FilterTypes.parenthesisClose, null, Global.LogicalOperators.none, Global.Operators.and, null, true));

Example of adding a sort condition:

acQueryDef.ACSortColumns.Add(new ACSortItem("MaterialNo", Global.SortDirections.ascending, true));

Saving an ACQueryDefintion:

acQueryDef.SaveConfig();

Compare filter conditions

If the user has changed and saved a persistable query so that the actual basic function of the business object is no longer guaranteed, it may be necessary to restore the basic filter setting. For this purpose, the ACQueryDefinition provides several comparison methods with which the current filter setting can be compared with the basic setting:

• bool CompareFilterColumns ( IEnumerable < ACFilterItem > filterItems, ...) ; Compares the passed filter list with the current one (ACFilterColumns). If these are different, true is returned.
• bool CompareSortColumns ( IEnumerable < ACSortItem > sortItems, ...) ; Compares the passed sort list with the current one (ACFilterColumns). If these are different, true is returned.
• bool CompareColumns ( IEnumerable < ACFilterItem > filterItems, IEnumerable < ACSortItem > sortItems, ...) ; Compares the filter list and the sort list. If these are different, true is returned.
• bool CheckAndReplaceFilterColumnsIfDifferent ( IEnumerable < ACFilterItem > filterItems, ...) ; Compares the passed filter list with the current one (ACFilterColumns). If these were different, the current one is replaced with the passed one and true is returned.
• bool CheckAndReplaceSortColumnsIfDifferent ( IEnumerable < ACSortItem > sortItems, ...) ; Compares the passed sort list with the current one (ACFilterColumns). If these were different, the current one is replaced with the passed one and true is returned.
• bool CheckAndReplaceColumnsIfDifferent ( IEnumerable < ACFilterItem > filterItems, IEnumerable < ACSortItem > sortItems, ...) ; Compares the filter list and the sort list and replaces if necessary.

We recommend declaring the basic filter and sorting settings as private properties in the business object and carrying out the comparison after instantiating the ACQueryDefinition: 

ACAccess<T> provides the event "event NavSearchEventHandler NavSearchExecuting" to extend the query with additional filter conditions via LINQ. Before this event is triggered, an expression tree is first created from the ACQueryDefinition and passed to the event as IQueryable<T>. In the event handler, you can then expand the expression tree structure using LINQ as usual:

In the event handler "_AccessPrimary_NavSearchExecuting" you can cast the parameter result into an ObjectQuery<T> class to insert additional include statements, for example, to avoid later lazy loading and to improve the performance of your application.