Today I decided to investigate and blog about a feature I haven’t tried since the beta of AX 2012 R2: the Linq connector. I do a quite a bit of C# work regularly, not related to AX, and Linq (along with WPF) is one of my favorite frameworks. So combining that with AX, seems like a perfect match to me.

For this example, I decided to do something basic. WPF allows easy binding, Linq allows querying and returns an IQueryable that you can bind to. In the past I’ve blogged about using WPF to bind to AIF services in my 10-minute app series. This time however, we’ll call our code from within AX and stay inside the AX client process and connection, which is where the Linq connector is working. You could use this over the business connector as well, just keep in mind that the BC technology is announced to be deprecated in the next release.

Unfortunately, there are several limitations and issues using Linq, and I’ll talk about those here.

So, let’s dive right into it. Open Visual Studio 2010 (with the VS extensions for AX 2012 R2 installed). Again, this ONLY works on R2 and higher. In Visual Studio, create a new class library project, I’ve called my example “AX62Linq”. Once it has opened, add the project to the AOT as shown below.

Once it’s added to the AOT, right-click on the project and select properties. In the properties window, make sure to set the “Deploy to Client” property to “Yes”. If you want to run and debug directly from Visual Studio, set the “Debug Target” to “Client”.

Next, we’ll create a WPF window control which we’ll call from within AX. On the project, right-click and click Add > New Item. Select WPF and create a new User Control (WPF) - don’t take the Windows Forms user control! I named my user control “CustomerSearch”.

Instead of a user control though, we’ll make this a full-on window. In the code CustomerSearch.xaml.cs code (expand the CustomerSearch.xaml in your solution explorer and double-click the CustomerSearch.xaml.cs file), change the inheritance from UserControl to inherit from Window instead.

Original:<pre>public partial class CustomerSearch : UserControl { public CustomerSearch() { InitializeComponent(); } }</pre> New:<pre>public partial class CustomerSearch : Window { public CustomerSearch() { InitializeComponent(); } }</pre>

To support this, you also need to add a reference to System.Xaml.

In the designer of the CustomerSearch.xaml, let’s change the “UserControl” tag to “Window”.

Original:<pre><UserControl x:Class="AX62Linq.CustomerSearch" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:d="http://schemas.microsoft.com/expression/blend/2008" mc:Ignorable="d" d:DesignHeight="300" d:DesignWidth="300"> <Grid>

&lt;/Grid&gt; &lt;/UserControl&gt;</pre></code> <code>New:<pre>&lt;Window x:Class="AX62Linq.CustomerSearch"
         xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
         xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
         xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" 
         xmlns:d="http://schemas.microsoft.com/expression/blend/2008" 
         mc:Ignorable="d" 
         d:DesignHeight="300" d:DesignWidth="300"&gt;
&lt;Grid&gt;
        
&lt;/Grid&gt; &lt;/Window&gt;</pre></code>

Next, we’ll add the references to the AX Linq libraries. Right-click references again and click “add a reference”, but this time we’ll have to browse to the DLLs we need. These DLLs are in the AX client’s bin directory, which by default on an 64-bit system is in c:\Program Files (x86)\Microsoft Dynamics AX\60\Client\Bin. Add the files Microsoft.Dynamics.AX.Framework.Linq*.dll On my current test system which is AX 2012 R2 CU6 I have three files, other versions may have different files or different number of files. The reason I say this is because the code example on MSDN seems to show a difference with my system.

Alright, that’s a lot of “blah” for the little tiny bit of code we’re going to write, but here goes. First, we need to instantiate a query provider for AX. then, we create a query collection object for the table we want to query. That table should be a proxy to the table we’re interested in, so first, open the Application Explorer toolbar (from the Visual Studio menu: View > Application Explorer). Expand Data Dictionary / Tables and find CustTable. Right-click on CustTable and select “Add to project”. That create the proxy for you. You’ll need to declare some using statements at the top. Again I have a difference on my system versus the code example from MSDN referenced above. On my 2012 R2 CU6, here’s the using statements I added:

<pre>using Microsoft.Dynamics.AX.Framework.Linq.Data; using Microsoft.Dynamics.AX.Framework.Linq.Data.Common; using Microsoft.Dynamics.AX.Framework.Linq.Data.ManagedInteropLayer; using Microsoft.Dynamics.AX.ManagedInterop;</pre>

That allows us to declare the query provider and query collection:

<pre>QueryProvider provider = new AXQueryProvider(null); QueryCollection<CustTable> custTableCollection = new QueryCollection<CustTable>(provider);</pre>

Next, we can perform our query. If your familiar with Linq, it’s pretty much regular Linq, but there are a few restrictions. But, at least the basics work. For example, here I’m querying for all customers in a given customer group (specified in a string variable named “customerGroup”):

<pre>var customers = from c in custTableCollection where c.CustGroup == customerGroup select c;</pre>

This is where it gets a little bad. There’s an issue when both a method and a field have the same name. For example, the CustTable table we are using has both a field named “Blocked” and a method named “Blocked”. Now, the proxy generator for AX avoids this issue by naming the field “Blocked_” with an underscore. However, the Linq provider seems to not pick up on this correctly. So as soon as you try to use the customers list from the Linq query, you will receive an exception: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> System.ArgumentException: The supplied method arguments are not valid. If you look through the stack, you’ll see it go down to the interop layer and linq libraries, starting with Microsoft.Dynamics.AX.ManagedInterop.Record.createFieldExpressionNode(String fieldName). Luckily, C# features anonymous types, and as handy as they are with Linq in general, they are a must to solve our problem here. So, let’s change our Linq query to not return CustTable types, but rather a new anonymous types containing only the fields we want (and… only fields that don’t have a method with the same name). As for the ugly part: if anyone ever decides to add a method with the same name as one of your fields, I guess you’re done. Below I create a new anonymous type containing the account number and the delivery mode fields).

<pre>var customers = from c in custTableCollection where c.CustGroup == customerGroup select new { c.AccountNum, c.DlvMode };</pre>

An option to this whole mess would be to remove the methods that are causing the conflicts from the generated proxy file. Unfortunately, by default a rebuild of your project will regenerate the proxy code. If you want to try that out anyway, right click on a declaration of “CustTable” in your code (for example in the QueryCollection declaration we have) and click "go to definition".

Anyway, back to the good! Let’s create our XAML window and try this out, shall we? Open the CustomerSearch.xaml file by double clicking on it. In the XAML code, inside our <Grid> tag which is currently empty, we’ll define some rows and columns for layout, but most importantly we’ll then add a TextBox named “CustGroup” and a button to perform the search. Finally, we add a ListView named “CustomerList” with a GridView inside of it. The GridView we bind to “AccountNum” and “DlvMode” which are the two fields we are returning from our Linq query. The final xaml code looks like this (note that if you named your project and XAML control differently, the x:Class declaration at the top in the Window tag should be preserved as you have it!):

<pre><Window x:Class="AX62Linq.CustomerSearch" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:d="http://schemas.microsoft.com/expression/blend/2008" mc:Ignorable="d" Title="Customer Search" Width="300" Height="300" d:DesignHeight="300" d:DesignWidth="300"> <Grid Margin="5"> <Grid.ColumnDefinitions> <ColumnDefinition Width="Auto" /> <ColumnDefinition Width="*" /> <ColumnDefinition Width="Auto" /> </Grid.ColumnDefinitions> <Grid.RowDefinitions> <RowDefinition Height="Auto" /> <RowDefinition Height="*" /> </Grid.RowDefinitions>

    <TextBlock Grid.Row="0" Grid.Column="0" Text="Customer Group Search: " />
    <TextBox Grid.Row="0" Grid.Column="1" Name="CustGroup" />
    <Button Grid.Row="0" Grid.Column="2" Content="Search" Name="SearchBtn" Click="SearchBtn_Click" />

    <ListView Name="CustomerList" Grid.Row="1" Grid.ColumnSpan="3">
        <ListView.View>
            <GridView>
                <GridViewColumn Header="Account Number" DisplayMemberBinding="{Binding AccountNum}" />
                <GridViewColumn Header="Delivery Mode" DisplayMemberBinding="{Binding DlvMode}" />
            </GridView>
        </ListView.View>
    </ListView>
&lt;/Grid&gt; &lt;/Window&gt;</pre></code>

Now, note that the SearchBtn button has a click event handler called SearchBtn_Click, which we need to create. So let’s move on to the code. Open up the CustomerSearch.xaml.cs again. First, we’ll add a method that takes a customer group string, performs the Linq query and sets the result set as the item source for our list view (the control we named “CustomerList”). <pre>public void LoadCustomers(string customerGroup) { QueryProvider provider = new AXQueryProvider(null); QueryCollection<CustTable> custTableCollection = new QueryCollection<CustTable>(provider);

var customers = from c in custTableCollection where c.CustGroup == customerGroup select new { c.AccountNum, c.DlvMode };

CustomerList.ItemsSource = customers; }</pre></code>

Finally, we create another method for the button click, which we have to name the same as what we put in the XAML, “SearchBtn_Click” in the example from above. All we’ll do is grab the text from the “CustGroup” textbox and pass it into our method for Linq:

<pre>private void SearchBtn_Click(object sender, RoutedEventArgs e) { LoadCustomers(CustGroup.Text); }</pre>

At this point, we’re all done. You can do Build > Rebuild Solution and Build > Deploy Solution and we can go into AX to call our code. For the purpose of speeding this up, I just created a quick and dirty job:

<pre>static void Job1(Args _args) { AX62Linq.CustomerSearch myWindow;

myWindow = new AX62Linq.CustomerSearch();
myWindow.ShowDialog(); // this waits for exit }</pre></code>

If all went well, here’s what you should get. I entered “10” as a filter which should give you some customers in the standard CU6 demo data. If you want to go advanced you can go back to Visual Studio and from your Application Explorer make the job you create your “Startup Object” (and make sure you have debug target “client” set on your project, as explained in the beginning). This will allow you to just hit F5 from within Visual Studio which will start AX and run the code (and allow you to debug easily without needing to manually attach the debugger to the ax client). You can go back to my Developer Resources page and find some of the Visual Studio articles if you want to know more about those features.

Ok, so what have we learned today.

The good: Getting the linq queries to work is pretty easy, just add the reference, a proxy and get started. Basic linq queries work well and perform as expected. Since it’s IQueryable you can use the linq results as datasources for binding etc. Although I didn’t demonstrate that here, joins between tables work just as well. Look at the MSDN code example if you’d like to see that.

The bad: Some linq query syntax is not available. For example, looking for customer account numbers that contain “abc” doesn’t work. In Linq you would filter in the where clause: where c.AccountNum.Contains(“abc”) but that won’t fly for the AX proxies. Normal filters where a field equals a string, or where a number is larger or smaller than another number work just fine.

The ugly: A lot of standard tables have fields and methods with the same names. This causes major issues and you won’t be able to query these fields at all unless you copy/paste the proxy code into your own CS file (and remove the proxy) and remove all the methods that cause the issues. Of course then you’re disabling the benefit of the rebuild refreshing your proxy with new code and fields, so you’re on your own for maintaining the proxy code.

There is no comment section here, but I would love to hear your thoughts! Get in touch!