Skip to main content

Notifications

Microsoft Dynamics AX forum
Answered

Dynamic query works in AX job, not from C#

Posted on by Microsoft Employee

Hello Everyone! I'm new to AX (I'm on AX2012 r2) but have been programming for a few years. I'm writing a data layer in C# to make it easier to query data from AX and map to some business objects. I have this working with other entities in AX (vendors, items, etc) but am having trouble with Purchase Orders and Purchase Lines.

My C# code is calling ExecuteDynamicQuery on an X++ query builder that extends AifQueryBuilder. The query builder loads a query I modelled in the AOT and just adds one or more ranges to the appropriate datasource based on what parameters are passed in via a class that derives from AifQueryBuilderArgs.

All of this works fine, and purchase order header information is returned. The problem is that no purchase line rows are returned in my dataset in C# (I get DBBnull values). What is further confusing me is that I created a job and tested my dynamic query class in AX and it IS returning purchase order header info and the purchase line info. Below is in initialize method from my query builder class.

[SysEntryPointAttribute]
public void initialize()
{
    QueryBuildDataSource dataSource;
    QueryBuildDataSource purchLineDataSource;
    
    changeCompany('abc') {   
    
        // get predefined query from AOT
        query = new Query("AbcPurchaseOrderQuery");

        // get datasource to add Ranges to
        dataSource = query.dataSourceName("PurchTable");

        // filter by company
        if (args.parmDataAreaId() != "")
            dataSource.company(args.parmDataAreaId());       

        // filter by purchId
        if (args.parmPurchId() != "")
            SysQuery::findOrCreateRange(dataSource, fieldNum(PurchTable, PurchId)).value(args.parmPurchId());

        // filter by purchName
        if (args.parmPurchName() != "")
            SysQuery::findOrCreateRange(dataSource, fieldNum(PurchTable, PurchName)).value(args.parmPurchName());

        // filter by purchStatus
        if (args.parmPurchStatus() != 0)
            SysQuery::findOrCreateRange(dataSource, fieldNum(PurchTable, PurchStatus)).value(enum2str(args.parmPurchStatus()));

        queryRun = new QueryRun(query);
    }
}

Here is the test job I am using to verify the dynamic query.

static void AbcDynamicPurchaseOrderTest(Args _args)
{
    QueryBuildDataSource dataSource;
    AbcPurchaseOrderQueryBuilder abcPurchaseOrderQueryBuilder;
    QueryRun queryRun;
    PurchTable po;
    PurchLine pl;
    AbcPurchaseOrderQueryBuilderArgs args;
    PurchId purchId;
    
    changeCompany('abc') {    
    
        purchId = "XXXXXXXX";
        args = new AbcPurchaseOrderQueryBuilderArgs();
        args.parmPurchId(purchId);
        args.parmDataAreaId("abc");    
    
        abcPurchaseOrderQueryBuilder = new AbcPurchaseOrderQueryBuilder();
        abcPurchaseOrderQueryBuilder.setArgs(args);

        // Looping through query results.
        abcurchaseOrderQueryBuilder.initialize();
        queryRun = abcPurchaseOrderQueryBuilder.getQueryRun();

        while (queryRun.next())
        {
            // get tables
            po = queryRun.get(tableNum("PurchTable"));
            pl = queryRun.get(tableNum("PurchLine"));

            // show results
            info( strFmt ('%1 - %2',po.PurchId, po.PurchName));
            info( strFmt ('%1 %2', pl.Name, pl.LineAmount));
        }
    }  
}


So in summary, this dynamic query is working in AX but now when called from C#. I feel like I am missing a small detail somewhere and I just need more experienced eyes on this problem. Thank you!

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Dynamic query works in AX job, not from C#

    ievgen Miroshnikov,

    I think I missed the query.addCompanyRange(...) line earlier. That seems to do the trick!

    I also figured out a way to solve this by passing a CallContext with a dynamic query as explained in the article below.

    code.msdn.microsoft.com/Walkthrough-of-using-73c07391

    Thanks for your help everyone!

  • Verified answer
    Mea_ Profile Picture
    Mea_ 60,274 on at
    RE: Dynamic query works in AX job, not from C#

    Hi Mike,

    I gave you solution that does company change in a different way and highlighted that it could be a problem but you ignored it.

    Let me repeat it again. Please try it and let us know.

    [SysEntryPointAttribute]
    public void initialize()
    {
        QueryBuildDataSource qbds;
    
        query = new Query(queryStr(PurchUpdate));
        query.allowCrossCompany(true);
        query.addCompanyRange(queryValue(args.parmDataAreaId()));
    
        qbds = query.dataSourceTable(tableNum(PurchTable));
    
        if (args.parmPurchId())
        {
            SysQuery::findOrCreateRange(qbds, fieldNum(PurchTable, PurchId)).value(queryValue(args.parmPurchId()));
        }
    
        queryRun = new QueryRun(query);
    }
  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Dynamic query works in AX job, not from C#

    Vilmos,

    I was thinking the same thing but I don't see any way to set the call context when calling QueryServiceClient.ExecuteDynamicQuery().

  • Suggested answer
    Vilmos Kintera Profile Picture
    Vilmos Kintera 46,147 on at
    RE: Dynamic query works in AX job, not from C#

    Since you are trying to call the AIF service from C#, did you set the call context? That is where you could specify a company account.

    technet.microsoft.com/.../hh582244.aspx

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Dynamic query works in AX job, not from C#

    I discovered something this morning in AX.  The default company for my account is "def". I was trying to query a PO in company "abc", which wasn't giving me PurchLine info. When I created a new test PO in "def" I can the PO header and the line items.

    So now that I know the issue is related to companies, I'm not sure how to solve it. I tried wrapping my query inside of a changeQuery() like below but that doesn't seem to help. Anybody have suggestions?

        changeCompany(args.parmDataAreaId()) {
        
            // get predefined query from AOT
            query = new Query("abcPurchaseOrderQuery");
    
            // get datasource to add Ranges to
            dataSource = query.dataSourceName("PurchTable");
                         
            // filter by company
            if (args.parmDataAreaId() != "") {
                dataSource.company(args.parmDataAreaId());
            }
    
            // filter by purchId
            if (args.parmPurchId() != "") {
                SysQuery::findOrCreateRange(dataSource, fieldNum(PurchTable, PurchId)).value(args.parmPurchId());
            }
        
            // filter by purchName
            if (args.parmPurchName() != "") {
                SysQuery::findOrCreateRange(dataSource, fieldNum(PurchTable, PurchName)).value(args.parmPurchName());
            }
    
            // filter by purchStatus
            if (args.parmPurchStatus() != 0) {
                SysQuery::findOrCreateRange(dataSource, fieldNum(PurchTable, PurchStatus)).value(enum2str(args.parmPurchStatus()));
            }
        
            queryRun = new QueryRun(query);
        }


  • Mea_ Profile Picture
    Mea_ 60,274 on at
    RE: Dynamic query works in AX job, not from C#

    Maybe you want to try PurchUpdate query from my code to eliminate issues related to custom AOT query ?

    Another thing I can advice you is to use SQL server profiler or similar tool to trace actual SQL query. 

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Dynamic query works in AX job, not from C#

    ievgen Miroshnikov,

    Thanks for your efforts. I should re-emphasize that I'm not having trouble with the query in AX. If my query was incorrect then my test job wouldn't display POs and line items. It's only when I call the exact same dynamic query from C# via ExecuteDynamicQuery that I do not receive PurchLine data.

    As I mentioned I have this code working end to end with other entities like Customer. PurchOrder though is a bit different because of the one to many relationship with PurchLines. I'm starting to think that has something to do with it.

  • Mea_ Profile Picture
    Mea_ 60,274 on at
    RE: Dynamic query works in AX job, not from C#

    Hi MikeUnderwood,

    I tried to repro your issues in a simplified manner but it works for me.

    I created args class:

    [DataContractAttribute]
    class MyPurchaseOrderQueryBuilderArgs extends AifQueryBuilderArgs
    {
        PurchId     purchId;
        DataAreaId  dataAreaId;
    }
    [DataMemberAttribute]
    public PurchId parmPurchId(PurchId _purchId = purchId)
    {
        purchId = _purchId;
        return purchId;
    }
    [DataMemberAttribute]
    public DataAreaId parmDataAreaId(DataAreaId _dataAreaId = dataAreaId)
    {
        dataAreaId = _dataAreaId;
        return dataAreaId;
    }

    and query builder

    class MyPurchorderQueryBuilder extends AifQueryBuilder
    {
        MyPurchaseOrderQueryBuilderArgs args;
    }
    public void setArgs(AifQueryBuilderArgs _args)
    {
        args = _args;
    }
    public MyPurchaseOrderQueryBuilderArgs getArgs()
    {
        return args;
    }
    [SysEntryPointAttribute]
    public void initialize()
    {
        QueryBuildDataSource qbds;
    
        query = new Query(queryStr(PurchUpdate));
        query.allowCrossCompany(true);
        query.addCompanyRange(queryValue(args.parmDataAreaId()));
    
        qbds = query.dataSourceTable(tableNum(PurchTable));
    
        if (args.parmPurchId())
        {
            SysQuery::findOrCreateRange(qbds, fieldNum(PurchTable, PurchId)).value(queryValue(args.parmPurchId()));
        }
    
        queryRun = new QueryRun(query);
    }

    As you can see here I'm using standard query PurchUpdate and I'm setting company in a different way (that could be an issue).

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Dynamic query works in AX job, not from C#

    I did an incremental build in the AOT. The service I'm hitting is the QueryService, so I'm not sure how to "deploy" that since it's not listed with other services (I think because it's a built in service).

    The C# code is below and is a minimal example that recreates the problem. It is functionally identical to the X++ test job I created. I create a client, create and populate the queryArgs, and then execute the dynamic query. The problem is that the dataset does not contain values for the PurchLine table (it does however have the PurchTable data).

    var client = new QueryServiceClient();
                
    // set paging to null
    QueryService.Paging paging = null;
    
    // set args
    var queryArgs = new AbcPurchaseOrderQueryBuilderArgs();
    queryArgs.parmDataAreaId = "abc";
    queryArgs.parmPurchId = "XXXXXXX";
    
    
    var dataSet = client.ExecuteDynamicQuery("AbcPurchaseOrderQueryBuilder", queryArgs, ref paging);


  • Mea_ Profile Picture
    Mea_ 60,274 on at
    RE: Dynamic query works in AX job, not from C#

    Hi MikeUnderwood,

    You showed us code that works (x++ part) but did not showed c# that does not. Obviously you calling it in a different way and we cannot comment on it.

    Did you build CIL and deployed service after that ?

Helpful resources

Quick Links

Dynamics 365 Community Update

Welcome to the inaugural Community Platform Update. As part of our commitment to…

Dynamics 365 Community Newsletter - August 2024

Catch up on the latest D365 Community news

Community Spotlight of the Month

Kudos to Mohana Yadav!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,142 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 227,979 Super User 2024 Season 2

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans