Part 2 - Getting Data, The Basics

In Part 1 we talked through getting connected. Now, let's try to read some data. We'll discuss how to get a records and a record as well as how to query in C#. It's similar to X++ but want to make sure we have a good foundation for when we move onto more advanced stuff. We'll also examine how some OOTB entities are built and what that means for a standard integration scenario with no custom entities. my BOOMSTICK!

Reading via oData is going to be the most common, useful and, in truth, time consuming part of any integration using OData. Reading will be the workhorse for most integrations. I say this because just consider how you use F&O in a normal end user experience. Let's say I am interested in Sales Orders, specifically if they have shipped or not. There are a few ways I can inquire on if something as shipped or not. I can look as a report or list page but I'm just looking for the most part. I'm asking the system to read data, apply some filters, and throw it on my screen. That will more than likely be 75% or more of any integration where F&O is providing the backend for an integrating app. We'll be doing something very similar. So first, let's take a look as a standard code snippet and pick it apart.

namespace ConsoleApp1
    class Program
        public static string ODataEntityPath = ClientConfiguration.Default.UriString + "data";

        static void Main(string[] args)
            //Get the URI path from our clientConfig
            Uri oDataUri = new Uri(ODataEntityPath, UriKind.Absolute);
            //Generate a new context. This handles communication for us
            var context = new Resources(oDataUri);

            //an addition trick to allow for querying against all Legal Entities
            context.BuildingRequest += (sender, e) =>
                var uriBuilder = new UriBuilder(e.RequestUri);
                // Requires a reference to System.Web and .NET 4.6.1
                var paramValues = HttpUtility.ParseQueryString(uriBuilder.Query);
                if (paramValues.GetValues("cross-company") == null)
                    paramValues.Add("cross-company", "true");
                    uriBuilder.Query = paramValues.ToString();
                    e.RequestUri = uriBuilder.Uri;

            //add authentiation / bearer token
            context.SendingRequest2 += new EventHandler<SendingRequest2EventArgs>(delegate (object sender, SendingRequest2EventArgs e)
                var authenticationHeader = OAuthHelper.GetAuthenticationHeader(useWebAppAuthentication: true);
                e.RequestMessage.SetHeader(OAuthHelper.OAuthHeader, authenticationHeader);

            SalesOrderHeaderV2 SalesOrderHeaderV2 = context.SalesOrderHeadersV2.Where(x => x.SalesOrderNumber == "asdf" && x.dataAreaId == "USMF").First();


Above is an example of of how to create a context and get the first record from entity SalesOrderHeaderV2 where the SalesOrderNumber is asdf in legal identity USMF. Everything else above that is just some pre-amble to connecting to F&O. Most settings will be in your ClientConfiguration.cs file if you are using the code provided by Microsoft. We're going to focus on the Where() portion for now.

Shop Smart. Shop Where-Mart.

How you build your Where() statements is critical, just like how you would in X++. If your Where() is poorly designed, you'll have slowness in data retrieval as well as processing it on the App side that requested the data. Also, we don't know the specific cardinality of what will, or won't, be returned and we have to interact with each scenario differently. For instance, consider this like from above

SalesOrderHeaderV2 SalesOrderHeaderV2 = context.SalesOrderHeadersV2.Where(x => x.SalesOrderNumber == "asdf" && x.dataAreaId == "USMF").First();

If no single Sales Order header is found, we'll get a "NotFound" error. So, I should always wrap my specific searches in a Try-Catch as what is returned will be either 0 with an error or 1 with no error. Next, the example above has a specific cardinality of either 0 or 1. What if I want or am expecting more than 1? We'll need to search for a set of Sales Order headers and expect that we are going to get more than 1.

IQueryable<SalesOrderHeaderV2> AllSalesOrdersV2 = context.SalesOrderHeadersV2.Where(x => x.dataAreaId == "USMF");

foreach(SalesOrderHeaderV2 salesOrderHeaderV2 in AllSalesOrdersV2)

This is a better example of how to query for data and handle whatever response you get. If USMF has no sales order header (or the entity isn't presenting any), this will loop from the first one to the last one. If there are zero, the loop never starts. But, how do I know what will be returned or what the underlying entity is doing? Great Question!

Gimme Some Sugar

All data entities in F&O have the capability to be an OData enabled entity. To check find an entity and entity and look at the "Public" section. Setting "Is Public" to yes will expose the data entity as an OData Data Entity. Public collection Name and Public Entity Name have to be unique. 

Not all entities in F&O are OData enabled by default and this cannot be changed with an extension. If you see an OOTB entity that you want to enable as an oData data entity, you have to copy it then enable that functionality. Also, not all entity AOT names match their OData version names. In the screenshot above, you can see that SalesOrderHeaderV2Entity has an oData name of SalesOrderHeaderV2. When you have identified which data entity you want to use for a specific data purpose or feed, you have to lookup the oData name. This is annoying as some names don't always line up very will. For instance, Entity SalesOrderLineV2Entity is named SalesOrderLines in oData. There is an entity called SalesOrderLineEntity in the AOT that isn't OData enabled so one might confuse the two if not looked up before hand. Also, all data entities are represented using SQL Views so if you want to see how or why some data are missing, want to check your data entity views performance or just poke around in SQL, you can do so. The view name will match the entity name, like so:

SELECT TOP (1000) *

Using the SQL query above, we can in SQL simulate what the C# OData call would bring back. And since it's SQL, can get take a look at how it is created some of the field that are represented by code. For instance the field SkipCreateAutoCharges, this refers to a function.

This uses:

    private static str skipCreateAutoChargesComputedColumnDefinition()
        return SysComputedColumn::returnLiteral(NoYes::Yes);

And in SQL looks like (after formatted with some cleanup):

      CAST ((1) AS INT)

So the view in SQL can give you an idea what is happening at the SQL level. We'll dig into this more in later articles. However, when getting into advanced workloads and workflows, you may need to make things faster, trim down a request or reduce the amount of data being examined or returned. All are valid requests and we have a few options to handle that. 

Blog Main Tag: