Example for Dynamics AX 2012 QueryService through C# - Relations and JoinMode usage

Question

Trying to access AX 2012 QueryService through c# application and would like to get CustomerID and Customername fields only. Problem is, CustomerID is in CustTable and CustomerName is in DirPartyTable.

Any help to article or code samples would be appreciated.

1. Reference to QueryDataSourceMetadata to put join in these tables and get data.
2. What are the options and when to use Relations and JoinMode?

I searched in MSDN and it just lists the property names and methods and not much help in the form of code samples.

Answer 1

According to my knowledge, you put in the two tables you want to query into your QueryDataSourceMetadata. The resulting dataset should contain both tables. (Example code for creating a QueryDataSourceMetadataObject is here: http://msdn.microsoft.com/EN-US/library/gg844682.aspx)

The property you need, for the tables to be joined, is ReturnFlatDataSet. (this may help: http://msdn.microsoft.com/EN-US/library/gg841671.aspx)

Hope i could help you or point you in the right direction!

Answer 2

Its too late to answer and I think you already might have found some solutions. Anyway here is the link to my response on AX community:

https://community.dynamics.com/ax/f/33/p/212065/573674#573674

---

I found 2 ways to add the relations from c#. I have commented the first approach based on AOT table relation. In the code below QueryServiceReference is the service reference name in VS. You can remove it in all the lines if you have only QueryService reference but no MetaDataService reference. Here is the code :

query.DataSources = new QueryServiceReference.QueryDataSourceMetadata[1];

// Set the properties on Customers data source.
 customerDataSource = new QueryServiceReference.QueryDataSourceMetadata();
 customerDataSource.Name = "Customers";
 customerDataSource.Enabled = true;
 customerDataSource.FetchMode = QueryServiceReference.FetchMode.OneToOne;
 customerDataSource.Table = "CustTable";
 //customerDataSource.DynamicFieldList = false;

query.DataSources[0] = customerDataSource;


 QueryServiceReference.QueryDataSourceMetadata dirPartyTableDataSource = new QueryServiceReference.QueryDataSourceMetadata();
 dirPartyTableDataSource.Name = "DirPartyTable";
 dirPartyTableDataSource.Table = "DirPartyTable";
 dirPartyTableDataSource.Enabled = true;
 dirPartyTableDataSource.DynamicFieldList = true;


 customerDataSource.DataSources = new QueryServiceReference.QueryDataSourceMetadata[1] { dirPartyTableDataSource };
 QueryServiceReference.QueryRelationMetadata relation = new QueryServiceReference.QueryRelationMetadata();

 //this is also one way of setting the relation 
 //relation.JoinRelation = "DirPartyTable_FK"; //table relation defined in AOT
 //relation.JoinDataSource = customerDataSource.Name; //parent datasource name

relation.Table = "CustTable";//Parent table
 relation.Field = "Party"; 
 relation.RelatedTable = "DirPartyTable"; // child table
 relation.RelatedField = "RecId";
 relation.JoinDataSource = customerDataSource.Name; 
 dirPartyTableDataSource.Relations = new QueryServiceReference.QueryRelationMetadata[1] { relation };