Microsoft Dynamics AX Support

This blog contains posts by the Microsoft Dynamics AX Support teams Worldwide

Calling the Query Service

Calling the Query Service

Rate This
  • Comments 3

In AX 2012 some system services were added to perform generic functions like retrieving security or AOT node information.  To me the most interesting system service is the Query service.  This service allows you to perform a query against whatever tables you want from a service call.  On the call you can specify a field list, range, sort, etc.  The classes replicate the Query classes used on forms and queries inside of AX.  Most notably, the query service has paging built into it.  If you have a data intensive query you need to perform via a service call this is probably the right option for you.  Service calls tend to timeout on the client when you retrieve a lot of data and don't have the ability to do paging.  With that plug for the query service here are some examples I wrote that demonstrates how to use it:

This one has ranges and a sort and builds the whole query programmatically:

            QueryServiceClient client = new QueryServiceClient();

            DataSet dataSet;

            //Set up paging so that 1000 records are retrieved at a time

            Paging paging = new ValueBasedPaging() { RecordLimit = 1000 };

            QueryMetadata query;

            QueryDataSourceMetadata customerDataSource;

            QueryDataRangeMetadata range, range2;

            QueryDataOrderByMetadata sort;

            QueryDataFieldMetadata accountNum, bankAccount, commissionGroup, cashDisc, custGroup;

           

 

           
            query = new QueryMetadata();

            // Set the properties of the query.

           query.QueryType = QueryType.Join;

           
query.DataSources = new QueryDataSourceMetadata[1];

 

            // Set the properties of the Customers data source.

           
customerDataSource = new QueryDataSourceMetadata();

           
customerDataSource.Name = "Customers";

           
customerDataSource.Enabled = true;

           
customerDataSource.Table = "CustTable";

            

            //Add the data source to the query.

           
query.DataSources[0] = customerDataSource;

           

            // Setting DynamicFieldList property to false so I can specify only a few fields

           
customerDataSource.DynamicFieldList = false;

           
customerDataSource.Fields = new QueryDataFieldMetadata[5];

            //Add my fields

           
accountNum = new QueryDataFieldMetadata();

           
accountNum.FieldName = "AccountNum";

           
accountNum.SelectionField = SelectionField.Database;

           
customerDataSource.Fields[0] = accountNum;

 

           
bankAccount = new QueryDataFieldMetadata();

           
bankAccount.FieldName = "BankAccount";

           
bankAccount.SelectionField = SelectionField.Database;

           
customerDataSource.Fields[1] = bankAccount;

 

           
commissionGroup = new QueryDataFieldMetadata();

           
commissionGroup.FieldName = "CommissionGroup";

           
commissionGroup.SelectionField = SelectionField.Database;

           
customerDataSource.Fields[2] = commissionGroup;

 

           
cashDisc = new QueryDataFieldMetadata();

           
cashDisc.FieldName = "CashDisc";

           
cashDisc.SelectionField = SelectionField.Database;

            customerDataSource.Fields[3] = cashDisc;

 

           
custGroup = new QueryDataFieldMetadata();

           
custGroup.FieldName = "CustGroup";

           
custGroup.SelectionField = SelectionField.Database;

           
customerDataSource.Fields[4] = custGroup;  

 

            //Put a range restriction on the query, in this case they will act as an or

            //so the CustGroup == 20 ||  CustGroup == 30

           
range = new QueryDataRangeMetadata();

           
range.TableName = "CustTable";

           
range.FieldName = "CustGroup";

           
range.Value = "20";

           
range.Enabled = true;

 

           
range2 = new QueryDataRangeMetadata();

           
range2.TableName = "CustTable";

           
range2.FieldName = "CustGroup";

            range2.Value = "30";

           
range2.Enabled = true;

 

           
customerDataSource.Ranges = new QueryDataRangeMetadata[2];

           
customerDataSource.Ranges[0] = range;

           
customerDataSource.Ranges[1] = range2;

 

            //Add a sort

           
sort = new QueryDataOrderByMetadata();

           
sort.DataSource = "Customers";

           
sort.FieldName = "AccountNum";

           
sort.SortOrder = SortOrder.Ascending;

           
query.OrderByFields = new QueryOrderByMetadata[1];

           
query.OrderByFields[0] = sort;

 

 

            // Execute the query

            int i = 0;

            do

            {

               
dataSet = client.ExecuteQuery(query, ref paging);

               
Console.WriteLine("Query service call: " + i.ToString());

               
Console.WriteLine("Number of Records in CustTable: " + dataSet.Tables[0].Rows.Count.ToString());

               
DataRow firstReturnedRow = dataSet.Tables[0].Rows[0];

               
i++;

 

            }

            while (((ValueBasedPaging)paging).Bookmark != null);

 

            Console.ReadLine();

 

This one executes a query modelled in the AOT:

     
       QueryServiceClient client = new
       QueryServiceClient();

            Paging paging = new ValueBasedPaging() { RecordLimit = 10 }; 

            DataSet dataset;

 

            int i = 0;

            do

            {

               
dataset = client.ExecuteStaticQuery("CustTable", ref paging);

               
DataRow row = dataset.Tables[0].Rows[0];

               
i++;

            }

            while (((ValueBasedPaging)paging).Bookmark != null);

 

 For more information on the query service and the other system services check out msdn:

 http://msdn.microsoft.com/en-us/library/gg879657.aspx

 

Leave a Comment
  • Please add 8 and 8 and type the answer here:
  • Post
  • We're using the query service to return data from a static query in the AOT.  When we create a C# application to read the data, we get all of the data except for fields on a custom table that is part of the query.  I read that the query service uses Windows integrated security and the accounts calling the query are system admin accounts in AX.  Any ideas why the data from the custom table is not being returned?

  • Have you rebuilt IL code and taken security out of the pictures?  I am able to create a custom table, populate it with data, add it to the CustTable query and then call that static query from C#.  The table and the row I put in the table is returned to my calling code.  I didn't do anything special with security.  As an admin I created the table, added a foreign key relation to CustTable, add the table to the query, build IL code and called the query.

  • How can I implement a (AND) filter? In this case is X || Y in the filter.

    What if I wanted to do a X && Y?

    Thanks.

Page 1 of 1 (3 items)