Patch: Alias a TableColumn

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Write a select query with an inner join that uses multiple columns from
different tables with the same names, ie 

new Select(Table1.IdColumn, Table2.IdColumn)
                .From(Table1.Schema)
                .InnerJoin(Table2.Table1IdColumn, Table1.IDColumn);

2. notice that the query result has two columns named id.
3. attempting to select a value by column name fails because the name is
ambiguous.

The attached patch adds an Alias property to the TableColumn, and an
AliasAs method that returns a TableColumn with the Alias property set. This
lets you rewrite the above query as:

new Select(Table1.IdColumn.AliasAs("table1ID"),
Table2.IdColumn.AliasAs("table2ID"))
                .From(Table1.Schema)
                .InnerJoin(Table2.Table1IdColumn, Table1.IDColumn);

Tested against SQL Server 2005.

Original issue reported on code.google.com by dave.nee...@gmail.com on 7 May 2009 at 8:00

Attachments:

• SubSonic_aliasColumnName.patch

[GoogleCodeExporter]

nice,

Select with Joins is always a bit tricky with Subsonic.

i.e.

Select().From<Table1>().InnerJoin(Table2.IdColumn, Table1.IdColumn)

selects all columns from Table1 instead of all colums from Table1 and Table2 as 
I
would suppose it.
So I used to write:

Select(
   Table1.Schema.TableName + "." + Table1.Columns.Id,
   Table2.Schema.TableName + "." + Table2.Columns.Id
)...

instead.

Will try your patch for the next query with join.

Original comment by j.steinblock@gmail.com on 13 May 2009 at 2:06

[GoogleCodeExporter]

I found an issue with my patch -- once the alias has been set, it is used in 
all the
queries that follow it (since it becomes a part of the static table column 
object).
This includes being used in the where clause (where aliases are generally 
invalid).

Original comment by dave.nee...@gmail.com on 13 Jul 2009 at 7:28