How can this be done? Spaces in db columns mapping to ExecuteTypedList

Hi there,

I was wondering if someone could help me with the following problem regarding inner join and a column with a space.

I have setup a test product such as:

class TestProduct {
    private int _id;
    public int ProductID {
        get { return _id; }
        set { _id = value; }
    }
    private string _name;
    public string ProductName {
        get { return _name; }
        set { _name = value; }
    }
    private decimal _price;
    public decimal UnitPrice {
        get { return _price; }
        set { _price = value; }
    }
}

I use the following to get a result ( imagine that the Unit Price column in fact contained a space)

List<TestProduct> result = new
    Select( Icon.IDColumn.QualifiedName, Icon.NameColumn.QualifiedName, Icon.UnitPriceColumn.QualifiedName)
    .From(Northwind.Product.Schema)
    .InnerJoin(Orders)
    .ExecuteTypedList<TestProduct>();

With the above example say the unit price column contained a space [Unit Price]

Question:
How do I map that column name to my TestProduct class UnitPrice. At the moment this column always returns null.
TestProduct.UnitPrice = null? 

Obviously my TestProduct property can't contain a space so I'm wondering how they can actually be mapped? 

Regards Dotnetshadow 

I'm a bit confused here... It looks like your specifying different columns than the table you're selecting from... Can you clarify?

Feel free to post a schema script for tables referenced here... Definitely easiest to diagnose if I can recreate the exact structure and generated classes...

I think he means doing something like this

SubSonic\SqlQuery\SqlQuery.cs - BuildTypedResult<T>(IDataReader rdr)

 

               for (int i = 0; i < rdr.FieldCount; i++)
                {
                    cachedProps = iType.GetProperty(rdr.GetName(i).Replace(" ", ""), BindingFlags.Public | BindingFlags.Instance | BindingFlags.IgnoreCase);
                }
 

I think yitzchok could be onto something here,

Basically my class expects this:

TestProduct.UnitPrice but the table column name is really [Unit Price] as a result when you set the mapping back to the property because there are spaces then the property just returns null because technically the property should be TestPriduct.Unit Price butyou can't have spaces in property names. 

So what yitzchok suggested looks like the way to go to make sure spaces get eliminated so they do get binded back to the property. Do you think this should go in codeplex?

Also:

Eric Kemp:

'm a bit confused here... It looks like your specifying different columns than the table you're selecting from... Can you clarify?

Aren't you allowed to specify various columns from different tables in the select when joining tables? For example I got TABLE_A and TABLE_B and I want say 2 columns from TABLE_A and 1 column from TABLE_B in my result set surely I can specify in the select all the columns I need from the 2 tables such as:

 Select( TABLE_A.Col1Column.QualifiedName, TABLE_A.Col2Column.QualifiedName, TABLE_B.Col1Column.QualifiedName) 

(Please note in my original post there was a problem with how I specified the SELECT because the last column should of been coming from my second table)

Regards DotnetShadow

With apologies and all due respect - a lot of our code is in place because of naming issues. One of our core conventions is to 1) have a PK and 2) no spaces in column names. I'm going No Action on this.

Thanks for the reply, that's cool but I did notice that in your conventions it never state not to use spaces in columns:

http://subsonicproject.com/setup/subsonic-conventions/

I do understand using spaces in column names isn't really a good idea, I will change my database tables but maybe you can update the webpage to state not to have column names with spaces? 

Also as a side note is it in the Quicktable / Scaffolding table control that allows column names to be renamed?  e.g. UnitPrice => Unit Price? I can't find where it was mentioned 

Regards DotnetShadow

What about underscores? My column names have underscores and thus don't match.

It's not a massive fix to place a regex or a replace in there as Yitzchock demonstrated.

I fully understand the convention mantra and I agree that there's very little excuse for having spaces in column names but underscores, hardly a cardinal sin, is it?!