SubSonic 3 and MySQL, removing underscore from column name in CleanUp() method causes exceptions when using property in linq-query.

Question

I've run into a problem when using SubSonic 3(.0.0.3) ActiveRecord with MySQL.

Since MySQL doesn't allow you to use uppercase letters in table or column names (or rather disregards it if you do) I decided to separate words using underscores, e.g. entity_id, and then use the CleanUp() method to add title casing and remove the underscores.
A friend wrote a ToTitleCase(string s) method that looks like this:

string ToTitleCase(string s)
{
    CultureInfo cultureInfo = Thread.CurrentThread.CurrentCulture;
    TextInfo textInfo = cultureInfo.TextInfo;
    return textInfo.ToTitleCase(s);
}

And the CleanUp() method looks like this:

string CleanUp(string tableName){
    string result=tableName;

    //strip blanks
    result=result.Replace(" ","");

    //put your logic here...
    result = ToTitleCase(result);
    result = result.Replace("_", "");

    return result;
}

If I then do:

var entity = Entity.All().Where(e => e.EntityName.Contains("John"));

I get a NotSupportedException, with the message "The member 'EntityName' is not supported."

If I remove

result = result.Replace("_", "");

Everything works just fine, only I get properties looking like Entity_Id which is not quite what I want.

If anyone knows why this happen, I would love to hear it. If it's possible to fix, even better! It's no showstopper but it's slightly annoying.

Answer 1

For many many months this was an issue for me and I just avoided underscores when working with SubSonic on any supported DB. Until yesterday when I had to support a legacy project that had underscores in its SQL Server database.

You'll have to fix it within the source code of SubSonic.Core (file: SubSonic.Core\Schema\DatabaseTable.cs):

Find this method:

public IColumn GetColumnByPropertyName(string PropertyName)
{
    return Columns.SingleOrDefault(x => x.Name.Equals(PropertyName, StringComparison.InvariantCultureIgnoreCase));
}

And change it to:

public IColumn GetColumnByPropertyName(string PropertyName)
{
    return Columns.SingleOrDefault(x => x.PropertyName.Equals(PropertyName, StringComparison.InvariantCultureIgnoreCase));
}

Next you'll have to modify your Structs.tt:

Find this near the top:

Columns.Add(new DatabaseColumn("<#=col.Name#>", this)
{
    IsPrimaryKey = <#=col.IsPK.ToString().ToLower()#>,
    DataType = DbType.<#=col.DbType.ToString()#>,
    IsNullable = <#=col.IsNullable.ToString().ToLower()#>,
    AutoIncrement = <#=col.AutoIncrement.ToString().ToLower()#>,
    IsForeignKey = <#=col.IsForeignKey.ToString().ToLower()#>,
    MaxLength = <#=col.MaxLength#>
});

And add this line:

    PropertyName = "<#=col.CleanName#>",

So that it becomes:

Columns.Add(new DatabaseColumn("<#=col.Name#>", this)
{
    PropertyName = "<#=col.CleanName#>",
    IsPrimaryKey = <#=col.IsPK.ToString().ToLower()#>,
    DataType = DbType.<#=col.DbType.ToString()#>,
    IsNullable = <#=col.IsNullable.ToString().ToLower()#>,
    AutoIncrement = <#=col.AutoIncrement.ToString().ToLower()#>,
    IsForeignKey = <#=col.IsForeignKey.ToString().ToLower()#>,
    MaxLength = <#=col.MaxLength#>
});

The problem is that once you cleaned up the column names, SubSonic tries the find the valid columns in your query by matching your SubSonic generated property names against the database's original column names.

These changes will make sure that SubSonic be matching them against the cleaned property name.

Answer 2

NoSynchro's answer does indeed solve the problem. I have implemented the above fix and successfully queried the database with underscores removed from the column names.