SQL Server is Crazy!

If you’ve ever used SQL server for a serious project, you’re already nodding your head at the title of this blog post. There are many aspects of SQL server that is a bit quirky. Overall I think SQL Server is a very good but expensive product. I have experience with Oracle as well, and Oracle has it’s own quirks and it is also an expensive product. But I’m not here to rate products, or decide which product I think you should use. I’m going to talk about one of the quirks of SQL Server that you might run into if you’re attempting to work with a legacy product that was built around SQL Server.

Field Names

One of the problems with using an ORM with MS SQL Server is that SQL allows characters in its field names that C# cannot handle. For example: ‘#’ and ‘@’ are allowed as a character in a field name (‘#’ is not allowed as the first character). Double-quotes can be in a field name (even the first character can be a quote). Fields can start with a number (Yikes!). Don’t believe me? Check this out:

Now you have to wonder, what would Entity Framework do with this mess? Let’s find out…

It looks like EF puts a “C” in front to prevent numbers from being the first character and then it turns all unacceptable symbols into underscores. That could cause a problem if another field name existed… let’s see what happens if another field is already named the name that EF wants to to rename a field name…

Now, let’s see what EF does…

OK, it appears that EF will make the second occurrence of “field_one” unique by adding an number to the end of the field name. Keeping the names unique.

Plural Table Names

I have a table in my example database named “person”. When you create a LINQ query of this table you end up with this:

using (var db = new sampledataEntities())
{
    var query = (from p in db.people select p).ToList();
}

The name “people” is the DBSet of the “person” object that represents the table “person”. Now, what if we named the table “persons”? It will name it “persons”. Now, let’s create a table named “people”, just to mess up EF.

Yikes! It named the record object “person” and it uses “people” as the table name. That would be confusing if you were working with the person table and your query is actually looking at the people table. Now let’s add “person”, “persons” and “people” into EF all at once.

The first thing that happened to me is that none of the tables show in the edmx model diagram. It did, however, create three objects for the tables:

using (var db = new sampledataEntities())
{
    var query = (from p in db.people select p).ToList();

    var query2 = (from p2 in db.people1 select p2).ToList();

    var query3 = (from p3 in db.persons select p3).ToList();
}

One of the table objects is named “people”, one is named “people1” and the last is named “persons”. In order to figure out which object points to which table, you’ll need to open your edmx file with an xml editor (right-click on the edmx file, select “open with”, then choose “automatic editor selector (XML)”). Then you can search for people, people1 or persons and reverse engineer which table is being accessed.

Summary

Keep this little exercise in mind when you design a database and choose field and table names. Avoid any symbols that are no acceptable in a C# variable name. Also, avoid using a plural name on a table name. Your goal is to choose a table name that will translate into an object name very similar to your table name. Otherwise, any developer that maintains your code will have to deal with an extra layer of confusion.

Leave a Reply