Monday, September 04, 2017

Development tutorial: insert_recordset using the Query class

Introduction

I am sure most of you are familiar with set-based X++ CUD operators: insert_recordset, update_recordset and delete_from. They allow performing database operations with a large number of records in a single roundtrip to the server, instead of a row-by-row type of operation, which depends on the number of rows being processed. As a result, they can provide a very significant boost in overall performance of a selected flow.

If not familiar or just need a refresher, you can read more about it by following the link to MSDN.

Problem statement

There's however one significant drawback with these operators - they are all compile-time constructs, so they lack the flexibility of the flow modifying the database request before it is set based on runtime state of the flow.

And, once the application is sealed, that will mean there is no way to modify the request even at compile-time, as X++ statements are not extensible.

Solution

In Microsoft Dynamics AX 2012 R3 a static method was added on the Query class, that allows to solve the two problems above for insert_recordset. This is of course now also available in Microsoft Dynamics 365 for Finance and Operations: Enterprise edition.

Note: update_recordset and delete_from are still not supported through the Query class.

Example

Imagine that we need to write a function that would copy selected sales order line information into a sales order line history table for one or more orders.

1. Data model

Here's how the data model for this table is defined in this example:

DEV_SalesLineHistory data model diagram
DEV_SalesLineHistory data model diagram

2. New DEV_SalesLineHistory table in Visual Studio

And here's how the new table looks in Visual Studio (I created a new model for it in a separate package dependent on Application Suite):

DEV_SalesLineHistory table in Visual Studio
DEV_SalesLineHistory table
Note I skipped all stuff non-essential to this example

3. Copy function through regular X++ insert_recordset statement

Let's first write the statement for inserting the history records using the regular insert_recordset operator:

public class DEV_Tutorial_InsertRecordset
{
    public static Counter insertXppInsert_Recordset(SalesId _salesId)
    {
        DEV_SalesLineHistory    salesLineHistory;
        SalesLine               salesLine;
        InventDim               inventDim;

        JournalPostedDateTime   postedDateTime = DateTimeUtil::utcNow();
        JournalPostedUserId     postedBy = curUserId();
        SalesDocumentStatus     postingType = DocumentStatus::PackingSlip;

        insert_recordset salesLineHistory
        (
            SalesId,
            LineNum,
            InventTransId,
            SalesQty,
            SalesUnit,
            InventSiteId,
            InventLocationId,
            PostedDateTime,
            PostedBy,
            PostingType
        )
        select SalesId, LineNum, InventTransId, SalesQty, SalesUnit from salesLine
            where salesLine.SalesId == _salesId
            join InventSiteId, InventLocationId, postedDateTime, postedBy, postingType from inventDim
                where inventDim.InventDimId == salesLine.InventDimId;

        return any2int(salesLineHistory.RowCount());
    }
}

As you can see, we do a simple select from SalesLine, specifying the exact fields, joined to selected fields from InventDim, where the field list also contains a few local variables to populate into the rows being inserted.
This is the standard syntax used with X++ insert_recordset statement, which all of you are familiar with.

4. Method signature for Query::insert_recordset()

Now let's convert the above to a Query, and call Query::insert_recordset() instead.
This method accepts three arguments:
  • An instance of a table record. This is where data will be inserted into. We can then use this variable to ask how many rows were inserted, for example.
  • An instance of a Map(Types::String, Types::Container), which defines the mapping of the fields to copy. In X++ operator, this had to be based on the specific order in the field selection lists in the select statement. 
    • The map key is the target field name.
    • The value is a container, which defines a pair of values:
      • the unique identifier of the QueryBuildDataSource object points to the table to copy the value from
      • the field name on the above data source to copy the value from
  • An instance of a Query class, which defines the select statement for the data, similar to what you see in the X++ version above.

As you can see from the above, it does not account for literals, as we did with variables in the X++ operator example.
That is currently not supported with this API.
We can however solve this through a use of a "temporary" table, as suggested below.

5. Define a new table to store calculable literals

Let us define a new table that will store the data required by our insert statement. That means it needs to contain four fields:
  • PostedDateTime
  • PostedBy
  • PostingType
  • SalesId - we'll use this to join to SalesLine. This could be sessionId or whatever is required to ensure concurrency and uniqueness
Here's how the table would look in Visual Studio designer:

DEV_SalesLineHistoryPostingDataTmp table definition

We can now populate this table with the required values and join it to our query.
After executing the bulk insert we can then delete the inserted row (if necessary).


Another possible implementation here could be to use a View, with computed  columns for the different literal values needed. You could select from a table that has only 1 row, like InventParameters or the like. This is however less flexible, as it'll be compiled in, while with a "temporary" table you could determine the values at runtime.

6. Write up the code using the Query::insert_recordset() method

Now we are all set to write the necessary code. It would look like below:

public class DEV_Tutorial_InsertRecordset
{
    public static Counter insertQueryInsert_Recordset(SalesId _salesId)
    {
        DEV_SalesLineHistory    salesLineHistory;
        
        Query query = new Query();
        QueryBuildDataSource qbdsSalesLine = query.addDataSource(tableNum(SalesLine));
        qbdsSalesLine.addSelectionField(fieldNum(SalesLine, SalesId));
        qbdsSalesLine.addSelectionField(fieldNum(SalesLine, LineNum));
        qbdsSalesLine.addSelectionField(fieldNum(SalesLine, InventTransId));
        qbdsSalesLine.addSelectionField(fieldNum(SalesLine, SalesQty));
        qbdsSalesLine.addSelectionField(fieldNum(SalesLine, SalesUnit));
        qbdsSalesLine.addRange(fieldNum(SalesLine, SalesId)).value(queryValue(_salesId));
        QueryBuildDataSource qbdsInventDim = qbdsSalesLine.addDataSource(tableNum(InventDim));
        qbdsInventDim.addSelectionField(fieldNum(InventDim, InventLocationId));
        qbdsInventDim.addSelectionField(fieldNum(InventDim, InventSiteId));
        qbdsInventDim.relations(true);
        QueryBuildDataSource qbdsPostingData = qbdsInventDim.addDataSource(tableNum(DEV_SalesLineHistoryPostingDataTmp));
        qbdsPostingData.addLink(fieldNum(SalesLine, SalesId), fieldNum(DEV_SalesLineHistoryPostingDataTmp, SalesId), qbdsSalesLine.name());
        qbdsPostingData.addSelectionField(fieldNum(DEV_SalesLineHistoryPostingDataTmp, PostedDateTime));
        qbdsPostingData.addSelectionField(fieldNum(DEV_SalesLineHistoryPostingDataTmp, PostedBy));
        qbdsPostingData.addSelectionField(fieldNum(DEV_SalesLineHistoryPostingDataTmp, PostingType));

        Map targetToSourceMap = new Map(Types::String, Types::Container);
        targetToSourceMap.insert(fieldStr(DEV_SalesLineHistory, SalesId),           [qbdsSalesLine.uniqueId(), fieldStr(SalesLine, SalesId)]);
        targetToSourceMap.insert(fieldStr(DEV_SalesLineHistory, LineNum),           [qbdsSalesLine.uniqueId(), fieldStr(SalesLine, LineNum)]);
        targetToSourceMap.insert(fieldStr(DEV_SalesLineHistory, InventTransId),     [qbdsSalesLine.uniqueId(), fieldStr(SalesLine, InventTransId)]);
        targetToSourceMap.insert(fieldStr(DEV_SalesLineHistory, SalesQty),          [qbdsSalesLine.uniqueId(), fieldStr(SalesLine, SalesQty)]);
        targetToSourceMap.insert(fieldStr(DEV_SalesLineHistory, SalesUnit),         [qbdsSalesLine.uniqueId(), fieldStr(SalesLine, SalesUnit)]);
        targetToSourceMap.insert(fieldStr(DEV_SalesLineHistory, InventLocationId),  [qbdsInventDim.uniqueId(), fieldStr(InventDim, InventLocationId)]);
        targetToSourceMap.insert(fieldStr(DEV_SalesLineHistory, InventSiteId),      [qbdsInventDim.uniqueId(), fieldStr(InventDim, InventSiteId)]);
        targetToSourceMap.insert(fieldStr(DEV_SalesLineHistory, PostedDateTime),    [qbdsPostingData.uniqueId(), fieldStr(DEV_SalesLineHistoryPostingDataTmp, PostedDateTime)]);
        targetToSourceMap.insert(fieldStr(DEV_SalesLineHistory, PostedBy),          [qbdsPostingData.uniqueId(), fieldStr(DEV_SalesLineHistoryPostingDataTmp, PostedBy)]);
        targetToSourceMap.insert(fieldStr(DEV_SalesLineHistory, PostingType),       [qbdsPostingData.uniqueId(), fieldStr(DEV_SalesLineHistoryPostingDataTmp, PostingType)]);

        ttsbegin;
        
        DEV_SalesLineHistoryPostingDataTmp postingData;
        postingData.PostedDateTime = DateTimeUtil::utcNow();
        postingData.PostedBy = curUserId();
        postingData.PostingType = DocumentStatus::Invoice;
        postingData.SalesId = _salesId;
        postingData.insert();
        
        Query::insert_recordset(salesLineHistory, targetToSourceMap, query);

        delete_from postingData 
            where postingData.SalesId == _salesId;

        ttscommit;

        return any2int(salesLineHistory.RowCount());
    }
}

As you can see, the first part of the code builds a query using the QueryBuild* class hierarchy. The query is identical to the above select statement, with the addition of another join to our "tmp" table to retrieve the literals.
The second part populates the target to source Map object, which maps the fields to insert into to their source.
The third part actually invokes the operation, making sure we have the record populated in our "tmp" table beforehand.

Note. Because the first argument is the record we insert into, we can use it to get the RowCount(), telling us how many records have been inserted.

7. Extensibility aspect

Leaving the code just like it is does not actually make it extensible, as the partners would not be able to add additional fields to copy, or additional conditions/joins to the query. To accomplish this, we'd need to break the logic out into smaller methods with specific responsibilities, or add inline delegates to do the same. Generally speaking, you should always favor breaking the code down into simpler methods over delegates.
I've not done this in the example, as that's not the purpose, but you should follow these guidelines in production code.

8. Execute and test the code

We can execute these methods now, but first we need to ensure we have a test subject, aka a Sales order to practice with. Here's the one I used in the example:

A sales order with 3 order lines
And here's the Runnable Class that invokes the two methods above:

class DEV_Tutorial_InsertRecordset_Runner
{        
    public static void main(Args _args)
    {        
        const SalesId SalesId = '000810';

        if (SalesTable::exist(SalesId))
        {
            Counter xppInsert = DEV_Tutorial_InsertRecordset::insertXppInsert_Recordset(SalesId);
            Counter queryInsert = DEV_Tutorial_InsertRecordset::insertQueryInsert_Recordset(SalesId);

            strFmt('Tutorial Insert_Recordset');
            info(strFmt('Inserted using X++ insert_recordset = %1', xppInsert));
            info(strFmt('Inserted using Query::insert_recordset() = %1', queryInsert));
        }
    }

}

You can download the full project here.


Hope this helps! Let me know if you have any comments.

Thursday, August 10, 2017

Announcement: Plan of record design for InventDim extensibility

As you all hopefully know by now, we are on a journey towards overlayering-free solutions.
One big roadblock on this path was the Inventory dimension concept we have in AX, or, more specifically, providing a way for partners to add new inventory dimensions that would not require overlayering.

Michael has documented the current design we have in mind in a blog post you can read below:
https://blogs.msdn.microsoft.com/mfp/2017/08/10/extensible-inventory-dimensions/

Let me know if you have any questions!

Tuesday, July 18, 2017

Announcement: Extensibility documentation is now available

With the next release of Dynamics 365 for Finance and Operations Enterprise edition we plan to soft seal the Application Suite model.
For anyone not yet familiar with this agenda, please read through the previous announcement

The partner channel need to be educated for them to successfully migrate their development to using extensions instead of overlayering.

In order to help with that we have created a dedicated section on Extensibility in our documentation, which has a number of details about our plans, the process of migrating to extensions, as well as specific How to articles around platform capabilities, as well as specific application frameworks and how to extend them (coming soon...)

Home page for Extensibility documentation

If you cannot find a particular topic, let us know, either directly on the docs page, or here through comments to this post.

Hope this helps!

Thanks

Saturday, April 15, 2017

Development tutorial link: Extending the Warehouse management functionality after application seal

Background


At the Dynamics 365 for Operations Technical Conference earlier this year, Microsoft announced its plans around overlayering going forward. If you have not heard it yet, here's the tweet I posted on it:


 AppSuite will be "soft sealed" in Fall 2017 release and "hard sealed" in Spring 2018 - move to use  in your solutions

However the application SYS code still has a number of areas which are difficult to impossible to customize without overlayering. One of such areas is the Warehouse management area.

Improvements

In the SCM team we have been focusing on improving our Extensibility story in the application, which also includes the above mentioned warehousing area.

Below are a few posts Michael posted recently describing some of the changes we have done in the Warehouse management area, which are now available as part of Microsoft Dynamics for Operations Spring 2017 preview.
This should allow customizing the warehousing functionality to a large degree without the need to overlayer the code.

Extending WHS – Adding a new flow
Extending WHS – Adding a new control type
Extending WHS – Changing behavior of existing control types
Extending WHS – Adding a new custom work type
Extending WHS – Adding a new location directive strategy

Please look through these changes and let us know if YOUR functional enhancements are still impossible, so we can get them addressed.

Thanks

Friday, March 31, 2017

Development tutorial: SysExtension framework with SysExtensionIAttribute and an Instantiation strategy

Problem statement

This post will continue from where we left off in my previous blog post about the SysExtension framework: Development tutorial: SysExtension framework in factory methods where the constructor requires one or more arguments

In the above blog post I described how to use the SysExtension framework in combination with an Instantiation strategy, which applies in many cases where the class being instantiated requires input arguments in the constructor.

At the end of the post, however, I mentioned there is one flaw with that implementation. That problem is performance.

If you remember a blog post by mfp from a while back (https://blogs.msdn.microsoft.com/mfp/2014/05/08/x-pedal-to-the-metal/), in it he describes the problems with the SysExtension framework in AX 2012 R2, where there were two main issues:
  • A heavy use of reflection to build the cacheKey used to look up the types
  • Interop impact when needing to make Native AOS calls instead of pure IL
The second problem is not really relevant in Dynamics 365 for Operations, as everything runs in IL now by default.

And the first problem was resolved through introduction of an interface, SysExtensionIAttribute, which would ensure the cache is built by the attribute itself and does not require reflection calls, which immediately improved the performance by more than 10x.

Well, if you were paying attention to the example in my previous blog post, you noticed that my attribute did not implement the above-mentioned interface. That is because using an instantiation strategy in combination with the SysExtensionIAttribute attribute was not supported.

It becomes obvious if you look at the comments in the below code snippet of the SysExtension framework:
public class SysExtensionAppClassFactory extends SysExtensionElementFactory
{
    ...
    public static Object getClassFromSysAttribute(
        ClassName       _baseClassName,
        SysAttribute    _sysAttribute,
        SysExtAppClassDefaultInstantiation  _defaultInstantiation = null
        )
    {
        SysExtensionISearchStrategy         searchStrategy;
        SysExtensionCacheValue              cachedResult;
        SysExtModelAttributeInstance        attributeInstance;
        Object                              classInstance;
        SysExtensionIAttribute              sysExtensionIAttribute = _sysAttribute as SysExtensionIAttribute;

        // The attribute implements SysExtensionIAttribute, and no instantiation strategy is specified
        // Use the much faster implementation in getClassFromSysExtAttribute().
        if (sysExtensionIAttribute && !_defaultInstantiation)
        {
            return SysExtensionAppClassFactory::getClassFromSysExtAttribute(_baseClassName, sysExtensionIAttribute);
        }
        ...
    }
    ...
}

So if we were to use an Instantiation strategy we would fall back to the "old" way that goes through reflection. Moreover, it would actually not work even then, as it would confuse the two ways of getting the cache key.

That left you with one of two options:

  • Not implement the SysExtensionIAttribute on the attribute and rip the benefits of using an instantiation strategy, but suffer the significant performance hit it brings with it, or
  • Use the SysExtensionIAttribute, but as a result not be able to use the instantiation strategy, which limited the places where it was applicable

No more! 

We have updated the SysExtension framework in Platform Update 5, so now you can rip the benefits of both worlds, using an instantiation strategy and implementing the SysExtensionIAttribute interface on the attribute.

Let us walk through the changes required to our project for that:

1.

First off, let's implement the interface on the attribute definition. We can now also get rid of the parm* method, which was only necessary when the "old" approach with reflection was used, as that was how the framework would retrieve the attribute value to build up the cache key. 

class NoYesUnchangedFactoryAttribute extends SysAttribute implements SysExtensionIAttribute
{
    NoYesUnchanged noYesUnchangedValue;

    public void new(NoYesUnchanged _noYesUnchangedValue)
    {
        noYesUnchangedValue = _noYesUnchangedValue;
    }

    public str parmCacheKey()
    {
        return classStr(NoYesUnchangedFactoryAttribute)+';'+int2str(enum2int(noYesUnchangedValue));
    }

    public boolean useSingleton()
    {
        return true;
    }

}

As part of implementing the interface we needed to provide the implementation of a parmCacheKey() method, which returns the cache key taking into account the attribute value. We also need to implement the useSingleton() method, which determines if the same instance should be returned by the extension framework for a given extension.

The framework will now rely on the parmCacheKey() method instead of needing to browse through the parm methods on the attribute class.

2.

Let's now also change the Instantiation strategy class we created, and implement the SysExtensionIInstantiationStrategy interface instead of extending from SysExtAppClassDefaultInstantiation. That is not necessary now and is cleaner this way.

public class InstantiationStrategyForClassWithArg implements SysExtensionIInstantiationStrategy
{
...
}

The implementation should stay the same.

3. 

Finally, let's change the construct() method on the base class to use the new API, by calling the getClassFromSysAttributeWithInstantiationStrategy() method instead of getClassFromSysAttribute() (which is still there for backward compatibility):

public class BaseClassWithArgInConstructor
{
...
    public static BaseClassWithArgInConstructor construct(NoYesUnchanged _factoryType, str _argument)
    {
        NoYesUnchangedFactoryAttribute attr = new NoYesUnchangedFactoryAttribute(_factoryType);
        BaseClassWithArgInConstructor inst = SysExtensionAppClassFactory::getClassFromSysAttributeWithInstantiationStrategy(
            classStr(BaseClassWithArgInConstructor), attr, InstantiationStrategyForClassWithArg::construct(_argument));
        
        return inst;
    }
}

Result

Running the test now will produce the following result in infolog:

The derived class was returned with the argument populated in

Download

You can download the full project for the updated example from my OneDrive.


Hope this helps!