ASP.NET Core and EF Core with Dynamics GP: Trim trailing spaces from char fields

By Steve Endow

UPDATE: A kind soul read this post, nodded his head in commiseration, and then offered a better solution.  See option 5 below for the best approach I've seen so far.


Anyone who has written SQL queries, integrated, or otherwise had to deal with Dynamics GP data certainly has warm feelings about the colonial era use of char data type for all string fields.

This has the lovely side effect of returning string values with trailing spaces that you invariably have to deal with in your query, report, application, XML, JSON, etc.

In the world of SQL queries, you can spot a Dynamics GP consultant a mile away by their prolific use of the RTRIM function in SQL queries.  .NET developers will similarly have Trim() statements thoroughly coating their data access code.

But in this bold new age of Microsoft development tools, where everything you have spent years learning and mastering is thrown out the window, those very simple solutions aren't readily available.

I am developing an ASP.NET Core web API for Dynamics GP, and being a sucker for punishment, I'm also using EF Core for data access.  In one sense, EF Core is like magic--you just create some entities, point it to your database, and presto, you've got data.  Zero SQL.  That's great and all if you have a nice, modern, clean, well designed database that might actually use the space age varchar data type.

But when you're dealing with a relic like a Dynamics GP database, EF Core has some shortcomings.  It isn't really designed to speak to a prehistoric database.  Skipping past the obvious hassles, like exposing the cryptic Dynamics GP field names, one thing you'll notice is that it dutifully spits out the char field values with trailing spaces in all of their glory.

When you convert that to JSON, you get this impolite response:

"itemnmbr": "100XLG                         ",

"itemdesc": "Green Phone                                                                                          ",

"itmshnam": "Phone          "

Yes, they're just spaces, and it's JSON--not a report output, so it's not the end of the world.  But in addition to looking like a mess, the spaces are useless, bloat the response, and may have to be trimmed by the consumer to ensure no issues on the other end.

So I just spent a few hours trying to figure out how to deal with this.  Yes, SpaceX is able to land freaking rockets on a floating barge in the middle of the ocean, while I'm having to figure out how to get rid of trailing spaces.  Sadly, I'm not the only one--this is a common issue for many people.

So how can we potentially deal with this?

1. Tell EF Core to trim the trailing spaces.  As far as I can tell, this isn't possible as of June 2017 (v1.1.1).  EF Core apparently doesn't have a mechanism to call a trim function, or any function, at the field level. It looks like even the full EF 6.1+ framework didn't support this, and you had to write your own code to handle it--and that code doesn't appear to work in EF Core as far as I can tell.

2. Tell ASP.NET Core to trim the trailing spaces, somewhere, somehow.  There may be a way to do this in some JSON formatter option, but I couldn't find any clues as to how.  If someone has a clever way to do this, I'm all ears, and I'll buy you a round at the next GP conference.

3. Use the Trim function in your class properties.  Ugh.  No.  This would involve using the old school method of adding backer fields to your DTO class properties and using the Trim function on every field. This is annoying in any situation, but to even propose this with ASP.NET Core and EF Core seems like sacrilege.  And if you have used scaffolding to build out your classes from an existing database, this is just crazy talk.  I'm not going to add hundreds of backer fields to hundreds of string properties and add hundreds of Trim calls.  Nope.

4. Use an extension method or a helper class.  This is what I ended up doing trying initially.  (see option 5 for a better solution)  This solution may seem somewhat obvious, but in the world of ASP.NET Core and EF Core, this feels like putting wagon wheels on a Tesla.  It's one step up from adding Trim in your classes, but looping through object properties and trimming every field is far from high tech.  Fortunately it was relatively painless, requires very minimal code changes, and is very easy to rip out if a better method comes along.

There are many ways to implement this, but I used the code from this post:

https://stackoverflow.com/questions/7726714/trim-all-string-properties


I created a new static class called TrimString, and I added the static method to the class.

    public static class TrimStrings

    {

        //https://stackoverflow.com/questions/7726714/trim-all-string-properties

        public static TSelf TrimStringProperties<TSelf>(this TSelf input)

        {

            var stringProperties = input.GetType().GetProperties()

                .Where(p => p.PropertyType == typeof(string));

            foreach (var stringProperty in stringProperties)

            {

                string currentValue = (string)stringProperty.GetValue(input, null);

                if (currentValue != null)

                    stringProperty.SetValue(input, currentValue.Trim(), null);

            }

            return input;

        }

    }

I then modified my controller to call TrimStringProperties before returning my DTO object.

    var item = _itemRepository.GetItem(Itemnmbr);

    if (item == null)

    {

        return NotFound();

    }

    var itemResult = Mapper.Map<ItemDto>(item);

    itemResult = TrimStrings.TrimStringProperties(itemResult);

    return Ok(itemResult);

And the new JSON output:

{

  "itemnmbr": "100XLG",

  "itemdesc": "Green Phone",

  "itmshnam": "Phone",

  "itemtype": 1,

  "itmgedsc": "Phone",

Fortunately this works, it's simple, and it's easy.  I guess that's all that I can ask for.

When using ASP.NET Core and EF Core, you end up using a lot of List<> objects, so I created an overload of TrimStringProperties that accepts a List.

    public static List<TSelf> TrimStringProperties<TSelf>(this List<TSelf> input)

    {

        List<TSelf> result = new List<TSelf>();

        TSelf trimmed;

        foreach (var obj in input)

        {

            trimmed = TrimStringProperties(obj);

            result.Add(trimmed);

        }

        return result;

    }

AND THE WINNER

5.  Use AutoMapper!  Less than 24 hours after posting this article, kind reader Daniel Doyle recognized the problem I was trying to solve and keenly observed that I was using AutoMapper.  AutoMapper is a brilliant NuGet package that automates the mapping and transfer of data from one object to another similar object.  I have highlighted the call in green above.

Daniel posted a comment below and suggested using AutoMapper to trim the string values as it performed its mapping operation--something I would have never thought of, as this is the first time I've used it.  After some Googling, it looks like AutoMapper is commonly used for this type of data cleanup, and it seems like it is well suited to the task.  It's processing all of the object data as it maps from one class to another, so it seems like a great time to clean up the trailing spaces on the strings.

Daniel suggested a syntax that uses the C# "null coalescing operator" (double question marks), which makes the statement an extremely compact single line of code.

And with a single line added to my AutoMapper configuration block in Startup.cs, all of my objects will have the trailing spaces trimmed automatically.  No extra looping code, no extra method calls in each of my Controllers every time I want to return data.

    AutoMapper.Mapper.Initialize(cfg =>

    {

        cfg.CreateMapItem, Models.ItemDto>();

        cfg.CreateMapItemSite, Models.ItemSiteDto>();

        cfg.CreateMapSalesOrder, Models.SalesOrderDto>();

        cfg.CreateMapSalesOrderLine, Models.SalesOrderLineDto>();

        cfg.CreateMapSalesOrderResponse, Models.SalesOrderResponseDto>();

               

        //Trim strings

        cfg.CreateMap<string, string>().ConvertUsing(str => (str ?? string.Empty).Trim());

    });

This is super clean, and very easy.  No more wagon wheels on the Tesla!

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Twitter, YouTube, and Google+

http://www.precipioservices.com