The Curious Case of the Sneaky Spaces

database

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.

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.

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

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…

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:

1
2
SELECT  * 
FROM    ORDER_STATUS

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

1
2
3
SELECT  ORDER_STATUS_NAME, LEN(ORDER_STATUS_NAME) AS LENGTH
FROM    ORDER_STATUS
WHERE   ORDER_STATUS_NAME LIKE 'Requested%'

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 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.

Hi! Did you find this useful or interesting? I have an email list coming soon, but in the meantime, if you ready anything you fancy chatting about, I would love to hear from you. You can contact me here or at stephen ‘at’ logicalmoon.com