SubSonic 3 Linq Joining error after using CleanUp

Question

I am using the ActiveRecord SubSonic3 templates. Everything was working just dandy, then I decided I wanted things to look a bit better. See my database has field that look like this:

SomeKey_id
SomeOtherKey_id

I wanted to clean it up so that it would instead be

SomeKeyID
SomeOtherKeyID

when accessing it through SubSonic.

So I added this small thing to the CleanUp function of settings.ttinclude

if(Regex.IsMatch(result,".*_id",RegexOptions.IgnoreCase)){
  result=result.Substring(0,result.Length-3);
  result+="ID";
}

Now though everywhere I have joins on an ID field it no longer works.

For example:

var data=from f in Foo
         join b in Bar on f.FooID equals b.FooID
         select new{FooValue=f, BarValue=b};

Does not work.

It will yield this exception:

The member 'FooID' is not supported

However before I did the _id fix it worked perfectly. What is wrong? How do I work around this?

Stacktrace is at pastebin

Answer 1

What does the CleanUp function run on? Does it run on the output of the code templates, i.e. the auto-generated C# code that SubSonic generates? Surely you need this auto-generated code to refer to the correct database columns that you are accessing. If your database column is actually called SomeKey_id and you change that to SomeKeyID in the code, then of course SubSonic will be unable to locate that database column. In other words, how are you making sure that you are replacing only the identifiers that you will be using in C# code, but not the identifiers that refer to the database columns?

Answer 2

The problem was in SubSonic.Core as I suspected. I downloaded the source for it and in Schema/DatabaseTable.cs I had to change the GetColumnByPropertyName function to something like this:

public IColumn GetColumnByPropertyName(string PropertyName)
{
    var c = Columns.SingleOrDefault(x => x.Name.Equals(PropertyName, StringComparison.InvariantCultureIgnoreCase));
    if (c == null)
    {
        c=Columns.SingleOrDefault(x => CleanUpColumnName(x.Name).Equals(PropertyName, StringComparison.InvariantCultureIgnoreCase));
        if (c == null)
        {
            throw new NotSupportedException("Couldn't find column name");
        }
    }
    return c;
}
private string CleanUpColumnName(string name)
{
    //don't forget to change Settings.ttinclude:CleanUp when changing this function! 
    string result = name;
    if (result.EndsWith("_id", StringComparison.OrdinalIgnoreCase))
    {
        result = result.Substring(0, result.Length - 3);
        result += "RID";
    }
    return result;
}

The reason for this is that when it tries to resolve the column name to an IColumn in the appropriate table all it has is something like "FooID" and all the columns have a .Name of "Foo_id". So, I made it so that if it doesn't find the column the first time then it will run the CleanUp function the second time on each of the column names so that it can find a match.

If someone from SubSonic reads this, I'd love if you somehow patched this bug or at least gave notice of it. Note the version I'm using(and the source I'm using) is 3.0.0.4

EDIT:

Actually it's more complicated than this. I ended up having to add a CleanName field to IColumn and DataColumn in SubSonic and then fill in the CleanName from when the T4 templates run. Then I changed the line above to something like

var c = Columns.SingleOrDefault(x => x.CleanName.Equals(PropertyName, StringComparison.InvariantCultureIgnoreCase));

Then in lots of random places I had to change code like this:

ParameterValue = settings[tbl.PrimaryKey.Name],

to code like this:

ParameterValue = settings[tbl.PrimaryKey.CleanName],

I'm still not sure that I've gotten all the changes complete but I can update, insert, select, and join so I'd say I'm close if not.