Chapter 3. AX 2012 and .NET

In this chapter

Introduction

Integrating AX 2012 with other systems

Using LINQ with AX 2012 R3

AX 2012 can be integrated with other systems in several ways. For example, your organization might need to harvest information from old Microsoft Excel files. To do this, you could write a simple add-in in Microsoft Visual Studio and easily integrate it with AX 2012. Or your organization might have an earlier system that is physically located in a distant location and that requires invoice information to be sent to it in a fail-safe manner. In this case, you could set up a message queue to perform the transfers. You could use the Microsoft .NET Framework to interact with the message queue from within AX 2012.

The first part of this chapter describes some of the techniques that you can use to integrate AX 2012 with other systems by taking advantage of managed code through X++ code. One way is to consume managed code directly from X++ code; another way is to author or extend existing business logic in managed code by using the Visual Studio environment. To facilitate this interoperability, AX 2012 provides the managed code with managed classes (called proxies) that represent X++ artifacts. This allows you to write managed code that uses the functionality these proxies provide in a typesafe and convenient manner.

Although it has long been possible to author managed code that can call back into X++, one piece has been missing, without which the story has been incomplete: there has been no easy way to access data in the Microsoft Dynamics AX data stack from managed code. AX 2012 R3 solves this problem by introducing a LINQ provider. LINQ, short for Language-Integrated Query, is a Microsoft technology that allows a developer to supply a back-end database to a data provider and then query the data from any managed language. The second part of this chapter describes how to use LINQ to retrieve and manipulate AX 2012 R3 data through managed code.

Strong names satisfy the following requirements:

Guarantee name uniqueness by relying on unique key pairs. No one can generate the same assembly name that you can, because an assembly generated with one private key has a different name than an assembly generated with another private key.

Protect the version lineage of an assembly. A strong name can ensure that no one can produce a subsequent version of your assembly. Users can be sure that the version of the assembly that they are loading comes from the same publisher that created the version the application was built with.

Provide a strong integrity check. Passing the .NET Framework security checks guarantees that the contents of the assembly have not been changed since it was built. Note, however, that by themselves, strong names do not imply a level of trust such as that provided by a digital signature and supporting certificate.

When you reference a strong-named assembly, you can expect certain benefits, such as versioning and naming protection. If the strong-named assembly references an assembly with a simple name, which does not have these benefits, you lose the benefits that you derive by using a strong-named assembly and open the door to possible DLL conflicts. Therefore, strong-named assemblies can reference only other strong-named assemblies.

If the assembly that you are consuming does not have a strong name, and is therefore not installed in the GAC, you must manually copy the assembly (and the assemblies it depends on, if applicable) to a directory where the .NET Framework can find it when it needs to load the assembly for execution. It is a good practice to place the assembly in the same directory as the executable that will ultimately load it (in other words, the folder on the client or the server in which the application is located). You might also want to store the assembly in the ClientBin directory (even if it is used on the server exclusively), so that the client can pick it up and use it for IntelliSense.

After you install the assembly on the client or server computer, you must add a reference to the assembly in AX 2012 so that you can program against it in X++. You do this by adding the assembly to the References node in the Application Object Tree (AOT): right-click the References node, and then click Add Reference. A dialog box like the one shown in Figure 3-1 appears.

The top pane of the dialog box shows the assemblies that are installed in the GAC. If your assembly is installed in the GAC, click Select to add the reference to the References node. If the assembly is located in either the ClientBin or the ServerBin binary directory, click Browse. A file browser dialog box will appear where you can select your assembly. After you choose your assembly, it will appear in the bottom pane and will be added when you click OK.

Note that there are some limitations to what you can achieve in X++ when calling managed code. One such limitation is that you cannot easily code against generic types (or execute generic methods). Another stems from the way the X++ interpreter works. Any managed object is represented as an instance of type ClrObject, and this has some surprising manifestations. For instance, consider the following code:

Click here to view code image

static void TestClr(Args _args)
{
    if (System.Int32::Parse("0"))
    {
        print "Do not expect to get here";
    }
    pause;
}

Obviously, you wouldn’t expect the code in the if statement to execute because the result of the managed call is 0, which is interpreted as false. However, the code actually prints the string literal because the return value of the call is a ClrObject instance that is not null (in other words, true). You can solve these problems by storing results in variables before use. The assignment operator will correctly unpack the value, as shown in the following example:

Click here to view code image

static void TestClr(Args _args)
{
    int i = System.Int32::Parse("0");
    if (i)
    {
        print "Do not expect to get here";
    }
    pause;
}

This example shows how to create managed code in C# (Visual Basic .NET could also be used) that reads the contents of an Excel spreadsheet and inserts the contents into a table in AX 2012. This example is chosen to illustrate the concepts described in this chapter rather than for the functionality it provides.

The process is simple. You author the code in Visual Studio, and then add the solution to Application Explorer, which is just the name for the AOT in Visual Studio. Then, functionality from AX 2012 is made available for consumption by the C# code, which illustrates the proxy feature.

Assume that the Excel file contains the names of customers and the date that they registered as customers with your organization, as shown in Figure 3-2.

Also assume that you’ve defined a table (called, for example, CustomersFromExcel) in the AOT that will end up containing the information, subject to further processing. You could go about reading the information from the Excel files from X++ in several ways. One way is by using the Excel automation model; another is by manipulating the Office Open XML document by using the XML classes. However, because it is so easy to read the contents of Excel files by using ADO.NET, this is what you decide to do. You start Visual Studio, create a C# class library called ReadFromExcel, and then add the following code:

Click here to view code image

using System;
using System.Collections.Generic;
using System.Text;

namespace Contoso
{
    using System.Data;
    using System.Data.OleDb;
    public class ExcelReader
    {
        static public void ReadDataFromExcel(string filename)
        {
            string connectionString;
            OleDbDataAdapter adapter;
            connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;"
            + "Data Source=" + filename + ";"
            + "Extended Properties='Excel 12.0 Xml;"

            + "HDR=YES'"; // Since sheet has row with column titles

            adapter = new OleDbDataAdapter(
                "SELECT * FROM [sheet1$]",
                connectionString);
            DataSet ds = new DataSet();
            // Get the data from the spreadsheet:
            adapter.Fill(ds, "Customers");
            DataTable table = ds.Tables["Customers"];
            foreach (DataRow row in table.Rows)
            {
                string name = row["Name"] as string;
                DateTime d = (DateTime)row["Date"];
            }
        }
    }
}

The ReadDataFromExcel method reads the data from the Excel file given as a parameter, but it does not currently do anything with that data. You still need to establish a connection to the AX 2012 system to store the values in the table. There are several ways of doing this, but in this case, you will simply use the AX 2012 table from the C# code by using the proxy feature.

The first step is to make the Visual Studio project (that contains the code) an AX 2012 citizen. You do this by selecting the Add ReadFromExcel To AOT menu item on the Visual Studio project. After you do this, the project is stored in the AOT and can use all of the functionality that is available for nodes in the AOT. The project can be stored in separate layers, can be imported and exported, and so on. The project is stored in its entirety, and you can open Visual Studio to edit the project by clicking Edit on the context menu, as shown in Figure 3-3.

With that step out of the way, you can use the version of the AOT that is available in Application Explorer in Visual Studio to fetch the table to use in the C# code (see Figure 3-4). If the Application Explorer window is not already open, you can open it by clicking Application Explorer on the View menu.

You can then create a C# representation of the table by dragging the table node from Application Explorer into the project.

After you drag the table node into the Visual Studio project, you will find an entry in the project that represents the table. The items that you drag into the project in this way are now available to code against in C#, just as though they had been written in C#. This happens because the drag operation creates a proxy for the table under the covers; this proxy takes care of the plumbing required to communicate with the AX 2012 system, while presenting a high-fidelity managed interface to the developer.

You can now proceed by putting the missing pieces into the C# code to write the data into the table. Modify the code as shown in the following example:

Click here to view code image

            DataTable table = ds.Tables["Customers"];
            var customers = new ReadFromExcel.CustomersFromExcel();
            foreach (DataRow row in table.Rows)
            {
                string name = row["Name"] as string;
                DateTime d = (DateTime)row["Date"];
                customers.Name = name;
                customers.Date = d;
                customers.Write();
            }

The data will be inserted into the CustomersFromExcel table as it is read from the ADO.NET table that represents the contents of the spreadsheet. However, before either the client or the server can use this code, you must deploy it. You can do this by setting the properties in the Properties window for the AX 2012 project in Visual Studio. In this case, the code will run on the client, so you set the Deploy to Client property to Yes. There is a catch, though: you cannot deploy the assembly to the client when the client is running, so you must close any AX 2012 clients prior to deployment.

To deploy the code, right-click the Visual Studio project, and then click Deploy. If all goes well, a Deploy Succeeded message will appear in the status line.

As soon as you deploy the assembly, you can code against it in X++. The following example illustrates a simple snippet in an X++ job:

Click here to view code image

static void ReadCustomers(Args _args)
{
    ttsBegin;
    Contoso.ExcelReader::ReadDataFromExcel(@"c:Testcustomers.xlsx");
    ttsCommit;
}

When this job runs, it calls into the managed code and inserts the records into the AX 2012 database.

An alternative to using this feature is to attach the Visual Studio debugger to the running AX 2012 client (by clicking Attach To Process on the Debug menu in Visual Studio). You can then set breakpoints and use all of the functionality of the debugger that you normally would. If you are running the Application Object Server (AOS) on your own computer, you can attach to that as well, but you must have administrator privileges to do so.

To see what the proxies look like, place the cursor on a proxy name in the code editor, such as CustomersFromExcel in the example, right-click, and then click Go To Definition (or use the convenient keyboard shortcut F12). All of the proxies are stored in the Obj/Debug folder for the project. If you look carefully, you will notice that the proxies use BC.NET to do the work of interfacing with the AX 2012 system. BC.NET has been completely rewritten from the previous version to support this scenario; in earlier versions of the product, BC.NET invariably created a new session through which the interaction occurred. This is not the case for the new version of BC.NET (at least when it is used as demonstrated here). That is why the transaction that was started in the job shown earlier is active when the records are inserted into the table. In fact, all aspects of the user’s session are available to the managed code. This is the crucial difference between authoring business logic in managed code and consuming the business logic from managed code. When you author business logic, the managed code becomes an extension to the X++ code, which means that you can crisscross between AX 2012 and managed code in a consistent environment. When consuming business logic, you are better off using the services framework that AX 2012 provides and then consuming the service from your application. This has big benefits in terms of scalability and deployment flexibility.

Figure 3-5 shows how BC.NET relates to AX 2012 and .NET application code.

To demonstrate the new role of BC.NET, the following example opens a form in the client that called the code:

Click here to view code image

using System;
using System.Collections.Generic;
using System.Text;

namespace OpenFormInClient
{
    public class OpenFormClass
    {
        public void DoOpenForm(string formName)
        {
            Args a = new Args();
            a.name = formName;
            var fr = new FormRun(a);
            fr.run();
            fr.detach();
        }
    }
}

In the following example, a job is used to call managed code to open the CustTable form:

Click here to view code image

static void OpenFormFromDotNet(Args _args)
{
    OpenFormInClient.OpenFormClass opener;
    opener = new OpenFormInClient.OpenFormClass();
    opener.DoOpenForm("CustTable");
}

This would not have been possible with earlier versions of BC.NET because they were basically faceless clients that could not display any user interface. Now, BC.NET is actually part of the client (or server), and therefore it can do anything the client or server can. In AX 2012 R2, you can still use BC.NET as a stand-alone client, but that is not recommended because that functionality is now better implemented by using services (see Chapter 12). The Business Connector that is included with AX 2012 is built with .NET Framework 3.5. That means that it is easier to build the business logic with this version of .NET; if you cannot do that for some reason, you must add markup to the App.config file to compensate. If you are using a program that is running .NET Framework 4.0 and you need to use BC.NET through the proxies as described, you would typically add the following markup to the App.config file for your application:

Click here to view code image

<configuration>
  <startup useLegacyV2RuntimeActivationPolicy="true">
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/>
  </startup>
</configuration>

You designate managed code to run on the server by setting the Deploy to Server property for the project to Yes, as shown in Figure 3-6.

When you set this property as shown in Figure 3-6, the assembly is deployed to the server directory. If the server has been running for a while, it typically will have loaded the assemblies into the current application domain. If Visual Studio were to deploy a new version of an existing assembly, the deployment would fail because the assembly would already be loaded into the current application domain. To avoid this situation, the server has the option to start a new application domain in which it executes code from the new assembly. When a new client connects to the server, it will execute the updated code in a new application domain while already-connected clients continue to use the old version.

To use the hot-swapping feature, you must enable the option in the Microsoft Dynamics AX Server Configuration Utility by selecting the Allow Hot Swapping Of Assemblies When The Server Is Running check box, as shown in Figure 3-7. To open the Microsoft Dynamics AX Server Configuration Utility, on the Start menu, point to Administrative Tools, and then click Microsoft Dynamics AX 2012 Server Configuration.

LINQ has two parts:

Native support in the C# and Visual Basic .NET compilers, which makes many (but not all) queries easy to read and write.

Language features that make the extension and use of LINQ queries possible. These features provide a layer of syntactic sugar—shortcuts to achieving results that can be expressed in the language in other ways. The following sections discuss these features briefly because they are crucial to understanding how LINQ queries work and how to use them. Later sections describe how to go beyond the syntactic sugar when you need to so that you can achieve the results you want.

var i = 9;

As it turns out, using LINQ requires that you be able to return values of types that cannot be described in the source language (the so-called anonymous types), which is why the var keyword was introduced. You’ll see how this works in the “Anonymous types” section later in this chapter.

In the following code, a static class called MyExtensions is defined. (The name of the extension class is arbitrary.) This class features a public static method, RemoveUnderscores, which is an extension method of the string type. The type is defined through the use of the this keyword on the first parameter; subsequent parameters become parameters of the extension method.

Click here to view code image

static class MyExtensions
{
   public static string RemoveUnderscores(this string arg)
   {
       return arg.Replace("_", "");
   }
}

Because this method is a string extension method, you can use it just like any other string method:

Click here to view code image

void Main()
{
   Console.WriteLine("The_Rain_In_Spain".RemoveUnderscores());
}

You create anonymous types by using the new operator together with an object initializer. Anonymous types are typically used in the select clause of LINQ queries, but they don’t have to be. The following code contains an example of an anonymous type. Note that the type cannot be described in the language, so the var keyword is used.

Click here to view code image

public static void Test()
{
    var myValue = new { Name = "Jones", Age = 43 };
    Console.WriteLine("Name = {0}, Age = {1}", myValue.Name, myValue.Age);
}

Even though the type of myValue cannot be declared, the compiler and IntelliSense recognize the type, so the field names defined in the type can be used, as shown in the code.

(Argument,...) => body

This syntax makes lambda expressions easy to use. The following lambda function accepts an integer argument and returns an integer value:

Func<int,int> x = e => e + 4;

You will need lambda expressions when you use unsugared syntax for LINQ queries. In later sections, you will see several ways in which these lambda expressions are used.

Now that you know what you need to know about how C# makes LINQ queries possible, you are ready to start using LINQ to access AX 2012 R3 data.

The Person table contains the following fields, in addition to the system fields:

FirstName: string

LastName: string

Age: real

Income: real

For good measure, put in an index on the RecId system field.

The Loan table is even simpler, consisting only of two fields:

Amount: real

PersonId: RefRecId

The table represents loans that are taken by people referenced in the PersonId field.

You might find it useful to add some sample data to the tables and check to ensure that the results are what you expect. Either you can create some simple forms to do this, or you can run the following X++ code to insert data to use when you run the examples:

Click here to view code image

static void PopulateLinqExampleData(Args _args)
{
    Person pTbl;
    Loan lTbl;
    int i;

    void InsertPerson(str fName, str lName, int age, real income)
    {
        Person p;
        p.FirstName = fName;
        p.LastName = lName;
        p.Age = age;
        p.Income = income;
        p.insert();
    }

    void InsertLoan(String30 personLastName, real loanAmount)
    {
        Person person;
        Loan l;
        int personID;

        select * from person where person.LastName == personLastName;

        l.PersonID = person.RecID;
        l.Amount = loanAmount;
        l.insert();
    }

    // Make sure there is no data in the table before insert
    delete_from pTbl;
    delete_from lTbl;


    //Add person data.
    for(i=0; i<20; i++)
    {
        InsertPerson('FirstName' + int2str(i), 'LastName' + int2str(i),
            25 + 2*i, 10000 + i * 1000);
    }


    //Insert a few loans.
    InsertLoan('LastName0', 6400);
    InsertLoan('LastName0', 5400);
    InsertLoan('LastName4', 13450);
    InsertLoan('LastName8', 100);
    InsertLoan('LastName10', 48000);
    InsertLoan('LastName8', 17850);
    InsertLoan('LastName15', 32000);
}

1. In Visual Studio, create a C# console application called LinqProviderSample, and add references to the DLLs necessary to run LINQ queries. These DLLs are located in the client’s in directory in your AX 2012 R3 installation:

• Microsoft.Dynamics.AX.Framework.Linq.Data.dll

• Microsoft.Dynamics.AX.Framework.Linq.Data.Interface.dll

• Microsoft.Dynamics.AX.Framework.Linq.Data.ManagedInteropLayer.dll

• Microsoft.Dynamics.AX.ManagedInterop.dll

• Microsoft.Dynamics.AX.ManagedInteropCore32.dll

2. To use the two tables you just created, you’ll need to add a proxy for each of them so that you can consume their data and methods in a typesafe manner. To do this, open Visual Studio Application Explorer and drag the two tables you created earlier into your project, as shown in Figure 3-8. This action causes tooling in Visual Studio to generate proxies for the tables, in addition to other proxies that are needed.

3. Now you are ready to add some code for your console application. The first example will just write the contents of the Person table out to the console.

Click here to view code image

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Dynamics.AX.Framework.Linq.Data;
using Microsoft.Dynamics.AX.ManagedInterop;
namespace Test
{
    class Program
    {
            static void Main(string[] args)
            {
                // Log on to AX 2012 R3. Create a session and log on.
                Session axSession = new Session();
                axSession.Logon(null, null, null, null);

                // Create a provider needed by LINQ and create a collection of customers.
                QueryProvider provider = new AXQueryProvider(null);

                var people = new QueryCollection<Person>(provider);
                var peopleQuery = from p in people select p;
                foreach (var person in peopleQuery)
                {
                    Console.WriteLine(person.LastName);
                }

                axSession.Logoff();
          }
    }
}

The Main method first creates a session that serves as the connection to the AX 2012 R3 system. In this case, you are logging on by using the default credentials for the computer. The query provider instance (called provider) is a handle that is used to provide a collection of objects that is then used in the query. The people variable holds such an instance, and it is used in the peopleQuery query. The query is used in a foreach loop to traverse the person records and show the name recorded on each one.

Note that the query is not actually executed before the first data is requested. In other words, the query declaration does not cause any requests to be made to SQL Server before the foreach statement executes. This fact allows you to build composable queries—that is, you can build queries in steps, adding criteria as needed, until the first data is requested. You will see examples of composable queries in the following walkthroughs.

Suppose that you want to return a list of people who are older than 70. You can get this set of people by adding a where clause to the query:

Click here to view code image

QueryCollection<Person> people = new QueryCollection<Person>(provider);
...
var query = from p in people
    where p.Age > 70
    select p;

The expression provided after the where keyword can be any valid C# expression that evaluates to a Boolean value (true or false). Often, the where clause will contain references to a record instance (in this case, type Person). The name of this variable (p in this case) is provided in the from clause.

var query = from p in people
    where p.Age > 70
    orderby p.AccountNum
    select p;

You can also specify whether the order is ascending (the default) or descending by using the proper keyword:

Click here to view code image

var query = from p in people
    where p.Age > 70
    orderby p.AccountNum descending
    select p;

Click here to view code image

QueryCollection<Person> people = new QueryCollection<Person>(provider);
QueryCollection<Loan> loans = new QueryCollection<Loan>(provider);

var personWithLargeLoan =
        from l in loans
        join p in people on l.PersonID equals p.RecId
        where l.Amount > 20000
        select new { Name = p.LastName + " " + p.FirstName, Amount = l.Amount };

When this query executes, it returns a value that contains the name and loan amount of every person who has a loan with an amount greater than 20,000. The person and the loan are tied together by the join ... on ... clause. The value returned is the first example in this chapter where anonymous types are used. The type of the following expression is anonymous and cannot be declared in C#:

Click here to view code image

new { Name = p.LastName + " " + p.FirstName, Amount = l.Amount };

The following query calculates the average age of the population represented in the database:

Click here to view code image

var averageAge = personCollection.Average(pers => pers.Age);
Console.WriteLine(averageAge);

This example uses the extension method approach to LINQ and moves beyond relying on the syntactic sugar that C# provides. Notice that the field to be averaged (Age) is provided in the form of a lambda expression, taking a person instance (called pers in this example, but the name is immaterial) and mapping it onto the age of that person.

The following example expands on that theme. This code contains another lambda expression to specify the criterion for records to be included in the count:

Click here to view code image

// Cost of salaries per month
var costOfSalaries = personCollection.Sum(pers => pers.Income);
Console.WriteLine(costOfSalaries);

// Get the number of persons with income greater than 20K
var noOfRecords = personCollection.Where(pers => pers.Income > 20000).Count();
Console.WriteLine(noOfRecords);

Click here to view code image

var allLoans = from l in loanCollection
               join p in personCollection on l.PersonID equals p.RecId
               select new {Name = p.LastName + " " + p.FirstName, Amount = l.Amount};

However, the expressions are not limited to anonymous types. In this case, the expression simply concatenates strings containing a first and last name into a full name, returning a sequence of strings:

Click here to view code image

var nameList = from pers in personCollection
               select string.Format("{0} {1}", pers.FirstName, pers.LastName);

foreach (var fullName in nameList)
{
    Console.WriteLine(fullName);
}

Click here to view code image

var take5 = personCollection.OrderBy(p => p.Age).Take(5);

Take is a generalization of the FirstOnly hints that are available in X++. However, FirstOnly hints exist only for 1, 10, 100, and 1,000 records, whereas you can provide any expression in the Take extension method.

It is often useful to check whether any records satisfy a particular constraint. You can use the Any extension method for these scenarios:

Click here to view code image

var anyone = personCollection.Any();

This statement returns a value of true if there are any records in the Person table. Often, you might want to check whether some criterion is satisfied by any of the records in the table. For this purpose, you can apply a lambda expression specifying the criterion in the Any extension method:

Click here to view code image

var anyone = personCollection.Any(p => p.Income > 100000);

Note that the results in this case are not enumerable values; they simply evaluate to a Boolean value.

You can also check whether a specified criterion is satisfied for all records:

Click here to view code image

var adults = personCollection(p => p.Age >= 18);

This example returns a Boolean value that is true if there are any people in the CEC company who are in an age group that is likely to apply for a home loan:

Click here to view code image

var people = new QueryCollection<Person>(provider);
var mayLookForHouseLoan = from pers in people
            where 30 <= pers.Age && pers.Age <= 40
            select pers;

if (onlyInCEC)
{
    mayLookForHouseLoan = mayLookForHouseLoan .CrossCompany("CEC").Any();
}

var b = mayLookForHouseLoan .Any(); // Force selection in the database.

You can call the CrossCompany extension method with up to seven strings designating different companies from which to include data. If you use this extension method without parameters, data from all companies is included. If you do not include the CrossCompany extension method in your query, you will get data from the current company only.

This code illustrates the composability of LINQ queries. Because the query is not actually executed before the first record is requested (typically in a foreach loop, but here by the usage of the Any predicate), you can add to it as required by the code. This is not possible in X++.

Basically, this means that the queries you make against these tables are relative to today’s date unless you use the validTimeState keyword. You can use the validTimeState extension method to query for values on the specified date or within the specified range, as shown in the following example:

Click here to view code image

var endOfYearExchangeQueryToday = rates
    .Where(rate => rate.From.Compare("USD") && rate.To.Compare("DKK");

var endOfYearExchangeQueryYearEnd = rates
    .Where(rate => rate.From.Compare("USD") && rate.To.Compare("DKK")
    .Where(rate => rate.ValidFrom.Equal(new DateTime(2013, 12, 31))

Note the second example, in which multiple where clauses are provided to enhance readability. The system will create the resulting expression at run time.

All of the following examples execute on the client tier; they are not set-based operations. This is one of the restrictions of the LINQ provider: there is currently no way to collect changes and then deliver them to the database for batch execution.

Click here to view code image

RuntimeContext c = RuntimeContext.Current;

c.TTSBegin();

var updateAblePersonCollection = personCollection.ForUpdate().Take(4);

foreach (var person in updateAblePersonCollection)
{
    person.Income = person.Income + 1000;
    person.Update();
}

c.TTSCommit();

The code is straightforward: you get the current run-time context from the static class called RuntimeContext. This instance can manage transactions, so you start a transaction, get four records to update (by using the ForUpdate extension method), and traverse them, adding 1,000 to the income of each person. When you are finished updating the fields in the table, you call the Update method on the table instance. Finally, you save the values by committing the transaction.

Click here to view code image

RuntimeContext c = RuntimeContext.Current;

c.TTSBegin();
var newPers = new Person();

newPers.FirstName = "NewPersonFirstName";
newPers.LastName = "NewPersonLastName";
newPers.Age = 50;
newPers.Income = 56000;
newPers.Insert();

c.TTSCommit();

Click here to view code image

c.TTSBegin();

var loansToDelete = loans.ForUpdate().Where(p => p.Amount == 0.0m);
foreach (var loan in loansToDelete )
{
    loan.Delete();
}

c.TTSCommit();

The deletion is performed by calling the Delete method on the table instance inside a transaction. Note that the records must be selected by using the ForUpdate extension method, as shown.

Overhead is exacted before records are fetched, because the LINQ provider must convert the query from C# to a data structure that is known to the AOS. For information about how to limit this overhead, see the next section, “Advanced: Limiting overhead.”

Some queries can be represented perfectly in C# but fail at run time because of limitations of the back-end data provider. This is a result of the architecture of LINQ. For example, the AX 2012 R3 data access stack does not support HAVING clauses (criteria used to select groups introduced with GROUP BY clauses). You might be tempted to write a query in C# such as the following:

Click here to view code image

var ages = from person in personCollection
        group person by person.Age into ageGroup
        where ageGroup.Count() > 4
        select ageGroup;

However, this query would fail at run time because the back end does not support it.

The C# compiler compiles the lambdas that are used as arguments to the extension methods into code that generates a tree structure at run time. As described earlier, this tree must be converted to the data structure that the AOS can use every time a query executes. This happens to be another tree structure—the same tree that the X++ compiler would have built for the same query. The AOS does not know whether the query it is executing comes from X++ or through the LINQ provider. This transformation from one tree structure to another means that LINQ queries exact a certain amount of overhead. For some scenarios, it is valuable to limit this overhead so that it occurs only once. You can accomplish this by compiling the queries before they are used. The key to understanding why this works lies in the difference between lambda functions expressed as functions that can be executed and their expression trees.

When lambdas were introduced earlier, the type of the lambda was specified as Func<int, int>:

Func<int, int> l = e => e + 4;

This is obviously a function taking an integer argument and returning an integer value. The convention taken by the C# compiler and .NET is that a lambda function returning a value of type T and taking multiple parameters of type P_i has the following type:

Func<P₁, P₂, ..., P_n, T>

These values can be used to call the lambda function:

Click here to view code image

Console.WriteLine("The value is {0}", l(9)); //Returns 9 + 4 = 13.

However, there is another way to interpret lambda functions. Instead of being treated as functions, they can be treated directly as the trees they form. This is accomplished by introducing the type Expression<>:

Click here to view code image

Expression<Func<int, int>> expression = e => e + 4;

Values of this type cannot be invoked to calculate a value, but you can certainly inspect them in the debugger, as shown in Figure 3-9.

This provides a glimpse into how the LINQ provider works: the expression is a value containing the lambda function broken down into its constituent parts. This tree is what is converted into a value that can be interpreted by the AOS.

Such expression values have an important property. They can be compiled into a delegate by using the .Compile() method:

Click here to view code image

var compiledexpression = expression.Compile();

The compiled expression is now executable, so you can do the following:

Click here to view code image

Console.WriteLine("The value is {0}", compiledExpression(9)); // returns 9 + 4 = 13.

Because the compilation has now taken place, no further processing is required every time the query is called.

An example will make this easier to understand. Reconsider the example shown earlier in the section about the CrossCompany extension method, where the records representing people in the age range of 30 through 40 were selected. For the purposes of this example, this selection has been generalized to a query that takes two parameters that hold the ages at the top and bottom of the range that is being requested, as shown in the following code:

Click here to view code image

int fromAge, toAge;

var personBetweenAges = from pers in personCollection
            where fromAge <= pers.Age && pers.Age <= toAge
            select pers;

foreach (var person in personBetweenAges)
{
    Console.WriteLine("{0}", person.Age);
}

The next step is to wrap the query into a function that takes the required parameters:

The query collection from which to pick the people

The from age

The to age

The ternary function returns an instance of the IQueryable<Person> interface. The code looks like this:

Click here to view code image

Func<QueryCollection<Person>, int, int, IQueryable<Person>> generator =
    (collection, f, t) => collection.Where(p => (f <= p.Age && p.Age <= t));

This can readily be transformed into the corresponding tree:

Click here to view code image

Expression< Func<QueryCollection<Person>, int, int, IQueryable<Person>>> tree =
    (collection, f, t) => collection.Where(p => p.Age > f && p.Age < t);

This, in turn, can be compiled into a delegate:

Click here to view code image

var compiledQuery = tree.Compile();

The end result is a delegate that can be called with the parameters you want:

Click here to view code image

foreach (var p in compiledQuery(personCollection, 30, 40))
{
    Console.WriteLine("{0}", p.Age);
}

You can repeat this call any number of times without incurring the cost of the compilation.