The Curious Case of the Sneaky Spaces

One of the things I like best about my job is the puzzle solving aspect of it. As a huge fan of the Arthur Conan Doyle books, I often think of what I do as a case and that I need¬†to try to solve it. I know. Furtive imagination ūüôā But to be honest, that’s exactly what it is like and probably much like Holmes did, I get a huge sense of satisfaction at solving a problem.

That leads me on to a strange thing that I noticed when porting an existing Web Forms/SQL Server application to MVC using Entity Framework. My plan was to use the existing data-model (plus data) and re-write the application using a more up-to-date architecture. Keep that in mind as we consider the evidence, Watson.

The Link

This is the View where we click on the link, which allows us to edit the ‘Requested’ record¬†in the ORDER_STATUS table. It’s worth pointing out that the primary key (PK) for that table is in fact that field – not your usual style of key, I know, but perfectly OK (if not my preference) provided it meets the needs of PKs.

The Page Link

The Page Link

Then, when you click on it, you get the following:

Error Message

Error Message

That’s odd.¬†It’s pointing to the ‘Requested’ record, right? Let’s check.

The URL

By hovering the mouse over the link, we can see the URL at the bottom. Magnifying glass¬†at the ready…

The URL

The URL

Now, %20 is how you encode spaces. What on earth are there spaces there for? Time to take a carriage to the SQL Server. Hurry up Watson; this is becoming most unusual.

The Data

Let’s look at the data in the table:

All the table data

All the table data

Seems OK. Hold on…are there any spaces in the data?

The record and its length

The record and its length

No. Hmm [Puffs on pipe]. It’s definitely referring to this table and digging deeper, all seems well.

The Confusion

Have you worked out what¬†could be the the problem, yet? Is there anything else we haven’t inspected? What about the table definition?

The Data Model

The Data Model

The Aha Moment

The problem lies in the initial choice of using NCHAR for the field type. NCHAR uses a fixed length for field data and pads it with spaces should you use less. Curiously (I had to use that word somewhere) when you ask for the length of the field, it ignores the trailing spaces, hence ‘Requested’ having a length¬†of 9. Entity Framework however, sticks to the rules, and translates the field data, spaces and all, as it should.

There are a number of solutions to this:

  1. Place a Trim() on the view
    1. @Html.ActionLink("Edit", "Edit", new { id=item.OrderStatusName.Trim() })
    2. Comment: Much too¬†repetitive¬†and bothersome. What if I forget? It’s also so inefficient.
  2. Use interceptors
    1. Comment: Looks interesting, but to be honest, I feel like I am having to compensate for a model that I’m not entirely happy about.
  3. Change the data model to use VARCHAR
    1. Comment: You can guess what I think…:-)

That, my dear Watson, is case-closed.

twittergoogle_plusredditpinteresttumblrmail

Written by Stephen Moon
email: stephen at logicalmoon.com
www: https://www.logicalmoon.com


Leave a Reply

Your email address will not be published. Required fields are marked *