LINQ to Dynamics AX - Update 2

Almost forgot,

I have started generation of the context class, or data objects.

Replace your DynamicsAXContext class with this one. You will need to use this for the AxaptaRecord binder to work, as we have moved on to getters and setters (properties not fields). As you can see, I bundled up my data in the context, to support the generation of code. You will need to delete your ADDRESSSTATE class, so it doesn't cause busts. If you have questions, just email them. I have had tons already lol.

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.Linq.Mapping;
using System.IO;
using System.Linq;
using System.Reflection;
using System.ComponentModel;


namespace LinqToAX
{
    public class DynamicsAXContext
    {

        public Query<ADDRESSSTATE> ADDRESSSTATES;

                 
       
        public DynamicsAXContext(DAXDataAccessLayer connection)
        {
            QueryProvider provider = new DAXQueryProvider(connection);
            this.ADDRESSSTATES = new Query<ADDRESSSTATE>(provider);
        }

    }

    [global::System.Data.Linq.Mapping.TableAttribute(Name = "dbo.ADDRESSSTATE")]
    public class ADDRESSSTATE : INotifyPropertyChanging, INotifyPropertyChanged
    {
        private static PropertyChangingEventArgs emptyChangingEventArgs = new PropertyChangingEventArgs(String.Empty);
        private string _NAME;
        private string _STATEID;
        private string _COUNTRYREGIONID;
        private string _DATAAREAID;
        private long _RECID;
        private int _RECVERSION;
        private int _TIMEZONE;
        private System.DateTime _MODIFIEDDATETIME;

        #region Extensibility Method Definitions

        #endregion

        [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_NAME", DbType = "NVarChar(32) NOT NULL", CanBeNull = false)]
        public string NAME
        {
            get
            {
                return this._NAME;
            }
            set
            {
                if ((this._NAME != value))
                {
                    // this.OnNAMEChanging(value);
                    this.SendPropertyChanging();
                    this._NAME = value;
                    this.SendPropertyChanged("NAME");
                    // this.OnNAMEChanged();
                }
            }
        }
        [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_STATEID", DbType = "NVarChar(15) NOT NULL", CanBeNull = false, IsPrimaryKey = true)]
        public string STATEID
        {
            get
            {
                return this._STATEID;
            }
            set
            {
                if ((this._STATEID != value))
                {
                    // this.OnSTATEIDChanging(value);
                    this.SendPropertyChanging();
                    this._STATEID = value;
                    this.SendPropertyChanged("STATEID");
                    // this.OnSTATEIDChanged();
                }
            }
        }

        [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_COUNTRYREGIONID", DbType = "NVarChar(15) NOT NULL", CanBeNull = false, IsPrimaryKey = true)]
        public string COUNTRYREGIONID
        {
            get
            {
                return this._COUNTRYREGIONID;
            }
            set
            {
                if ((this._COUNTRYREGIONID != value))
                {
                    //this.OnCOUNTRYREGIONIDChanging(value);
                    this.SendPropertyChanging();
                    this._COUNTRYREGIONID = value;
                    this.SendPropertyChanged("COUNTRYREGIONID");
                    //this.OnCOUNTRYREGIONIDChanged();
                }
            }
        }

        [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_DATAAREAID", DbType = "NVarChar(4) NOT NULL", CanBeNull = false, IsPrimaryKey = true)]
        public string DATAAREAID
        {
            get
            {
                return this._DATAAREAID;
            }
            set
            {
                if ((this._DATAAREAID != value))
                {
                    //this.OnDATAAREAIDChanging(value);
                    this.SendPropertyChanging();
                    this._DATAAREAID = value;
                    this.SendPropertyChanged("DATAAREAID");
                    //this.OnDATAAREAIDChanged();
                }
            }
        }

        [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_RECID", DbType = "BigInt NOT NULL")]
        public long RECID
        {
            get
            {
                return this._RECID;
            }
            set
            {
                if ((this._RECID != value))
                {
                    //this.OnRECIDChanging(value);
                    this.SendPropertyChanging();
                    this._RECID = value;
                    this.SendPropertyChanged("RECID");
                    //this.OnRECIDChanged();
                }
            }
        }

        [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_RECVERSION", DbType = "Int NOT NULL")]
        public int RECVERSION
        {
            get
            {
                return this._RECVERSION;
            }
            set
            {
                if ((this._RECVERSION != value))
                {
                    //this.OnRECVERSIONChanging(value);
                    this.SendPropertyChanging();
                    this._RECVERSION = value;
                    this.SendPropertyChanged("RECVERSION");
                    //this.OnRECVERSIONChanged();
                }
            }
        }

        [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_TIMEZONE", DbType = "Int NOT NULL")]
        public int TIMEZONE
        {
            get
            {
                return this._TIMEZONE;
            }
            set
            {
                if ((this._TIMEZONE != value))
                {
                    //this.OnTIMEZONEChanging(value);
                    this.SendPropertyChanging();
                    this._TIMEZONE = value;
                    this.SendPropertyChanged("TIMEZONE");
                    //this.OnTIMEZONEChanged();
                }
            }
        }

        [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_MODIFIEDDATETIME", DbType = "DateTime NOT NULL")]
        public System.DateTime MODIFIEDDATETIME
        {
            get
            {
                return this._MODIFIEDDATETIME;
            }
            set
            {
                if ((this._MODIFIEDDATETIME != value))
                {
                    //this.OnMODIFIEDDATETIMEChanging(value);
                    this.SendPropertyChanging();
                    this._MODIFIEDDATETIME = value;
                    this.SendPropertyChanged("MODIFIEDDATETIME");
                    //this.OnMODIFIEDDATETIMEChanged();
                }
            }

        }

        public event PropertyChangingEventHandler PropertyChanging;

        public event PropertyChangedEventHandler PropertyChanged;

        protected virtual void SendPropertyChanging()
        {
            if ((this.PropertyChanging != null))
            {
                this.PropertyChanging(this, emptyChangingEventArgs);
            }
        }

        protected virtual void SendPropertyChanged(String propertyName)
        {
            if ((this.PropertyChanged != null))
            {
                this.PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
            }
        }

    }

   
   
   

}

LINQ to Dynamics AX - Update

Hi again.

As I mentioned before, I will be making changes and updates to this project on a regular basis, and last night I made some decent progress toward being generic and reusable when it comes to populating the data class. Of course I will share.

We are going to rework our Data Access Layer (DAXDataAccessLayer), and remove a good amount of redundancy out of our data class object, AddressState.

First off, add a new class to your project, and name it DAXRecordReader. Add the following code..

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Reflection;
using System.Data.Common;
using Microsoft.Dynamics.BusinessConnectorNet;

namespace LinqToAX
{
    internal class DAXRecordReader<T> : IEnumerable<T>, IEnumerable where T : class, new()
    {

        Enumerator enumerator;


        internal DAXRecordReader(AxaptaRecord aRecord)
        {
            this.enumerator = new Enumerator(aRecord);
        }

        public IEnumerator<T> GetEnumerator()
        {
            Enumerator e = this.enumerator;
            if (e == null)
            {
                throw new InvalidOperationException("Cannot enumerate more than once");
            }
            this.enumerator = null;
            return e;
        }


        IEnumerator IEnumerable.GetEnumerator()
        {
            return this.GetEnumerator();
        }


        class Enumerator : IEnumerator<T>, IEnumerator, IDisposable
        {
            AxaptaRecord aRecord;
            PropertyInfo[] fields;
            T current;

            internal Enumerator(AxaptaRecord aRecord)
            {
                this.aRecord = aRecord;
                this.fields = typeof(T).GetProperties();
            }

            public T Current
            {
                get { return this.current; }
            }

            object IEnumerator.Current
            {
                get { return this.current; }
            }

            
            public bool MoveNext()
            {

                if (this.aRecord.Found)
                {
                    if (this.fields == null)
                    {
                        this.InitFieldLookup();
                    }

                    T instance = new T();
                    for (int i = 0; i < fields.Count(); i++)
                    {
                        PropertyInfo pi = fields[i];
                        pi.SetValue(instance, this.aRecord.get_Field(fields[i].Name),null);
                    }
                    this.current = instance;
                    aRecord.Next();
                    return true;
                }
                return false;
            }
                 
            public void Reset()
            {
            }

            public void Dispose()
            {
                this.aRecord.Dispose();
            }

            private void InitFieldLookup()
            {
                fields = this.current.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public);
            }
        }
    }
}

What we are doing here is using reflection to get the fields collection off of our Type, removing the need to hard code it, or generate it. It also removes the need to create an instance of our type on the fly and invoke a method on it to do the same work. This class exists at design and is of course generic. This meets our need better...

Oh well, now to make the change to our DAXDataAccessLayer.AxExecQuery method to use it.

 public AxaptaRecord AxExecQuery(string query, Type objectType)
        {

            AxaptaRecord ar;

            lock (lockAxObject)
            {
                try
                {
                    ar = staticAxapta.CreateAxaptaRecord(objectType.Name);
                    ar.ExecuteStmt(query);
                    return ar;
                }
                catch (Exception e)
                {
                    DAXUtils.LogEvent("DAXDataAccessLayer", "Business Connector execQuery Failed! " + e.Message, EventLogEntryType.Error);
                    ResetStaticLogon();
                    throw e;
                }
            }
        }


 
 And finally, lets change our DAXQueryProvider.Execute method to get rid of the data reader stuff and use our AxaptaRecord object filler-upper instead...

 public override object Execute(Expression expression)
        {
          
            AxaptaRecord aRecord;
            string query = this.Translate(expression);
            Type elementType = TypeSystem.GetElementType(expression.Type);
            aRecord = connection.AxExecQuery(query, elementType);
            return Activator.CreateInstance(typeof(DAXRecordReader<>).MakeGenericType(elementType), BindingFlags.Instance | BindingFlags.NonPublic, null, new object[] { aRecord }, null);
        }




There, much cleaner, less overhead, all better. Oh, you can remove the MakeDataTable method out of your test data class, and any others you have probably built by now, it's no longer used of course.
More later, be good.
H

LINQ to Dynamics AX - Test Harness (4)

This is the easy part...

Create a new 4.0 console application named whatever, and add it to your solution.

Add a reference to your LINQTest provider to the new project.

Add and application configuration to your project and add these settings. Remember, your provider uses a configuration supplied from your consuming application..

<?xml version="1.0"?>
<configuration>
<startup useLegacyV2RuntimeActivationPolicy="true">
  <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/>
</startup>
  <appSettings>
    <add key="AxBCUser" value="BusConUser"/>
    <add key="AxBCPassword" value="BusConPassword!"/>
    <add key="AxBCDomain" value="yourdomain"/>
    <add key="AxConfigFile" value="C:\YourAXConfig.axc"/>
    <add key="AxApplicationName" value="YourAppName"/>
    </appSettings>
  </configuration>

Add this code to the Program.cs file

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using LINQTest;
using System.Data.SqlClient;


namespace TestBench
{
    class Program
    {
        static void Main(string[] args)
        {

            int val = 0;

            DAXDataAccessLayer con = new DAXDataAccessLayer();
            DynamicsAX db = new DynamicsAX(con);
                           
       
            IQueryable<AddressState> query = (from e in db.AddressState
                                                where e.RECID > val
                                                select e);
          

           Console.WriteLine("Query:\n{0}\n", query);


            var list = query.ToList();
            foreach (var item in list)
            {
                Console.WriteLine("{0},{1}", item.NAME, item.STATEID);
            }

          
            Console.ReadLine();
        }

    }
}

Run it and viola, a fully IQueryable interface to Dynamics AX via the business connector.

Hope you had fun. I am currently working on SELECT, ORDER, JOIN, and UPDATE capability..

Stay tuned... and hope you learned something and had as much fun as I had figuring it out.

Harold

LINQ to Dynamics AX - Object Classes and Utilities (3)

Ok,
In my last post I covered the Data Access Layer, Query Provider, Query Translator, Expression, and a bunch of other assorted things we need to get our new provider going. In this section we are going to cover the objects and utilities for populating them, as well as the actual context provider itself. I can cover any questions in the comments section later, as I have less time to actually blog, I want to spend most of it giving you the code to get started with your own implementations. So to waste no time, lets jump in.


Create a class in our LINQTest project called ObjectReader, and copy in the following code.


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Reflection;
using System.Data.Common;

namespace LINQTest
{
    internal class ObjectReader<T> : IEnumerable<T>, IEnumerable where T : class, new()
    {
        Enumerator enumerator;


        internal ObjectReader(DbDataReader reader)
        {
            this.enumerator = new Enumerator(reader);
        }


        public IEnumerator<T> GetEnumerator()
        {
            Enumerator e = this.enumerator;
            if (e == null)
            {
                throw new InvalidOperationException("Cannot enumerate more than once");
            }
            this.enumerator = null;
            return e;
        }


        IEnumerator IEnumerable.GetEnumerator()
        {
            return this.GetEnumerator();
        }


        class Enumerator : IEnumerator<T>, IEnumerator, IDisposable
        {
            DbDataReader reader;
            FieldInfo[] fields;
            int[] fieldLookup;
            T current;


            internal Enumerator(DbDataReader reader)
            {
                this.reader = reader;
                this.fields = typeof(T).GetFields();
            }


            public T Current
            {
                get { return this.current; }
            }


            object IEnumerator.Current
            {
                get { return this.current; }
            }


            public bool MoveNext()
            {
                if (this.reader.Read())
                {
                    if (this.fieldLookup == null)
                    {
                        this.InitFieldLookup();
                    }
                    T instance = new T();
                    for (int i = 0, n = this.fields.Length; i < n; i++)
                    {
                        int index = this.fieldLookup[i];
                        if (index >= 0)
                        {
                            FieldInfo fi = this.fields[i];
                            if (this.reader.IsDBNull(index))
                            {
                                fi.SetValue(instance, null);
                            }
                            else
                            {
                                fi.SetValue(instance, this.reader.GetValue(index));
                            }
                        }
                    }
                    this.current = instance;
                    return true;
                }
                return false;
            }


            public void Reset()
            {
            }


            public void Dispose()
            {
                this.reader.Dispose();
            }


            private void InitFieldLookup()
            {
                Dictionary<string, int> map = new Dictionary<string, int>(StringComparer.InvariantCultureIgnoreCase);
                for (int i = 0, n = this.reader.FieldCount; i < n; i++)
                {
                    map.Add(this.reader.GetName(i), i);
                }
                this.fieldLookup = new int[this.fields.Length];
                for (int i = 0, n = this.fields.Length; i < n; i++)
                {
                    int index;
                    if (map.TryGetValue(this.fields[i].Name, out index))
                    {
                        this.fieldLookup[i] = index;
                    }
                    else
                    {
                        this.fieldLookup[i] = -1;
                    }
                }
            }
        }
    }
}

Now, because we have to support the SELECT implementation I am not done with, create a class called ProjectionReader, and copy in this code.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data.Common;

namespace LINQTest
{
    internal class ProjectionReader<T> : IEnumerable<T>, IEnumerable
    {
        Enumerator enumerator;


        internal ProjectionReader(DbDataReader reader, Func<ProjectionRow, T> projector)
        {
            this.enumerator = new Enumerator(reader, projector);
        }


        public IEnumerator<T> GetEnumerator()
        {
            Enumerator e = this.enumerator;
            if (e == null)
            {
                throw new InvalidOperationException("Cannot enumerate more than once");
            }
            this.enumerator = null;
            return e;
        }


        IEnumerator IEnumerable.GetEnumerator()
        {
            return this.GetEnumerator();
        }


        class Enumerator : ProjectionRow, IEnumerator<T>, IEnumerator, IDisposable
        {
            DbDataReader reader;
            T current;
            Func<ProjectionRow, T> projector;


            internal Enumerator(DbDataReader reader, Func<ProjectionRow, T> projector)
            {
                this.reader = reader;
                this.projector = projector;
            }


            public override object GetValue(int index)
            {
                if (index >= 0)
                {
                    if (this.reader.IsDBNull(index))
                    {
                        return null;
                    }
                    else
                    {
                        return this.reader.GetValue(index);
                    }
                }
                throw new IndexOutOfRangeException();
            }


            public T Current
            {
                get { return this.current; }
            }


            object IEnumerator.Current
            {
                get { return this.current; }
            }


            public bool MoveNext()
            {
                if (this.reader.Read())
                {
                    this.current = this.projector(this);
                    return true;
                }
                return false;
            }


            public void Reset()
            {
            }


            public void Dispose()
            {
                this.reader.Dispose();
            }
        }
    }
}
 
 Now, we are going to add our LINQ data class, named AddressState. Remember, this is a hand coded representation of the AddressState table from AX. It is an easy candidate for code generation via SQLDictionary, but we can cover that later.

using System;
using System.Collections.Generic;
using Microsoft.Dynamics.BusinessConnectorNet;
using System.Linq;
using System.Text;
using System.Data;

namespace LINQTest
{
    public class AddressState
    {
        // Properties.
        public string NAME;
        public string STATEID;
        public string COUNTRYREGIONID;
        public string DATAAREAID;
        public int TIMEZONE;
        public int RECVERSION;
        public long RECID;


        public DataTable MakeDataTable(AxaptaRecord ar)
        {
            DataTable dt = new DataTable("AddressState");
            DataColumn dc;
            DataRow dr;

            dc = new DataColumn();
            dc.ColumnName = "NAME";
            dc.DataType = typeof(string);
            dt.Columns.Add(dc);

            dc = new DataColumn();
            dc.ColumnName = "STATEID";
            dc.DataType = typeof(string);
            dt.Columns.Add(dc);

            dc = new DataColumn();
            dc.ColumnName = "COUNTRYREGIONID";
            dc.DataType = typeof(string);
            dt.Columns.Add(dc);

            dc = new DataColumn();
            dc.ColumnName = "DATAAREAID";
            dc.DataType = typeof(string);
            dt.Columns.Add(dc);

            dc = new DataColumn();
            dc.ColumnName = "TIMEZONE";
            dc.DataType = typeof(int);
            dt.Columns.Add(dc);

            dc = new DataColumn();
            dc.ColumnName = "RECVERSION";
            dc.DataType = typeof(int);
            dt.Columns.Add(dc);

            dc = new DataColumn();
            dc.ColumnName = "RECID";
            dc.DataType = typeof(long);
            dt.Columns.Add(dc);


            while (ar.Found)
            {
                dr = dt.NewRow();
                if (ar.get_Field("NAME") != null)
                {
                    dr["NAME"] = (string)ar.get_Field("NAME");
                }
                if (ar.get_Field("STATEID") != null)
                {
                    dr["STATEID"] = (string)ar.get_Field("STATEID");
                }
                if (ar.get_Field("COUNTRYREGIONID") != null)
                {
                    dr["COUNTRYREGIONID"] = (string)ar.get_Field("COUNTRYREGIONID");
                }
                if (ar.get_Field("DATAAREAID") != null)
                {
                    dr["DATAAREAID"] = (string)ar.get_Field("DATAAREAID");
                }
                if (ar.get_Field("DATAAREAID") != null)
                {
                    dr["DATAAREAID"] = (string)ar.get_Field("DATAAREAID");
                }
                if (ar.get_Field("TIMEZONE") != null)
                {
                    dr["TIMEZONE"] = (int)ar.get_Field("TIMEZONE");
                }
                if (ar.get_Field("RECVERSION") != null)
                {
                    dr["RECVERSION"] = (int)ar.get_Field("RECVERSION");
                }
                if (ar.get_Field("RECID") != null)
                {
                    dr["RECID"] = (long)ar.get_Field("RECID");
                }
                dt.Rows.Add(dr);
                ar.Next();

            }

            return dt;
        }
    }
}
 


OK, notice the method MakeDataTable that takes the AxaptaRecord as an argument, and has a return type of DataTable. It's a very important part of this project, as it creates an set that supports enumeration and iteration. The point is here is that we are going to have hundreds of these classes, so the MakeDataTable method has to be implemented on each of them. Again, this will be done via code generation.


The last thing to do in this section is create our context class, so add a class named DynamicsAX to your project, and add the following code to it.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;


namespace LINQTest
{
    public class DynamicsAX
    {
      
        public Query<AddressState> AddressState;

        public DynamicsAX(DAXDataAccessLayer connection)
        {
            QueryProvider provider = new DAXQueryProvider(connection);
            this.AddressState = new Query<AddressState>(provider);
         }

    }
}





That completes this section. You should now have a pretty slick LINQ to Dynamics AX provider.


In the next session, I will show you how to use it, if you haven't figured it out already :)