Can't create composite index if field name alias has a space

Since v4.0.44, there was a change made to support this use case (commit 5489903):

This breaks indexes where the field has an alias containing a space.

[CompositeIndex("Date Created")]
public class BazSpace
{
    [AutoIncrement]
    [PrimaryKey]
    public int Id { get; set; }

    [Alias("Date Created")]
    public DateTime DateCreated { get; set; }
}

db.CreateTable<BazSpace> will throw a SqlException:

Result Message: System.Data.SqlClient.SqlException : Incorrect syntax near ‘Created’.

This is the code that causes the issue:

var parts = fieldName.SplitOnFirst(' ');
sb.Append(GetQuotedName(parts[0]))
  .Append(' ')
  .Append(parts.Length > 1 ? parts[1] : "ASC");

Correct behaviour would be to split on the last space, then check if the last part matches ASC or DESC. If not, all items in parts make up the field name.

I’m unable to submit a pull request now, so here’s the failing test:

using System;
using System.Collections.Generic;
using System.Linq;
using NUnit.Framework;
using ServiceStack.DataAnnotations;
using ServiceStack.Model;

namespace ServiceStack.OrmLite.SqlServerTests.UseCase
{
    [CompositeIndex("Date Created")]
    public class BazSpace
    {
        [AutoIncrement]
        [PrimaryKey]
        public int Id { get; set; }

        [Alias("Date Created")]
        public DateTime DateCreated { get; set; }
    }

    [TestFixture]
    public class CompositeIndexWithSpacesInAliasesTests : OrmLiteTestBase
    {
        [Test]
        public void Create_table_with_CompositeIndex_and_space_in_aliases()
        {
            using (var db = OpenDbConnection())
            {
                db.DropTable<BazSpace>();

                db.CreateTable<BazSpace>();
            }
        }
    }
}

Spaces in column names is madness! But composite indexes should now support them in this commit.

This change is available from v4.0.47 that’s now on MyGet

I know, I hate them as much as you.

But sometimes we have to work with badly designed existing databases :confounded:

1 Like