SqlQuery Where WIth Join And Same Columname Getting Parsed Incorrectly

[lski]

Hi,

This is the first time Ive put one of these things so apologies about any errors in etiquette.

I have been using Subsonic for a short while and think I might have found a bug when using fluent query with ActiveRecord and a Join, with an SQL2005 database, where the fully qualified columnname is becoming confused. An example of my code is:

var qry = db.Select.From()
.InnerJoin(DocumentHeadersTable.DocumentHeadersIDColumn, ProposedSubmissionDocsTable.DocumentHeadersIDColumn)
.Where(ProposedSubmissionDocsTable.IsDeleted).IsEqualTo(false)
.And(ProposedSubmissionDocsTable.IsHeldColumn).IsEqualTo(false)
.And(DocumentHeadersTable.PurchaseVatRateColumn).IsEqualTo(cboVatrate.SelectedItem);

In the above code am trying to do a join on two tables, then do a check on a column in the second table on a column called 'IsDeleted' however when the sql is generated it is returned as:

.. WHERE [dbo].[t_DocumentHeaders].[IsDeleted] = @0 AND [dbo].[t_ProposedSubmissionDocs].[IsHeld] = @1 AND ...

Which shows the wrong table for isDeleted because it matches a column in the first DocumentHeader table. I tried a suggestion from Rob to another person on Stackoverflow and tried passing an IColumn reference rather than the string name, however this did not work.

I downloaded the source code and step through each section to find where it was converting it, I first tried stepping through the stages of creating the SqlQuery but at all times the qualified name was correct, so all ok so far.

I then tried stepping the ToString() overload in SqlQuery to work out where the qualified name got lost. The sequence went:

SqlQuery:
Line 416: BuildSqlStatement()
Line 446: generator.BuildSelectStatement()

ANSISqlGenerator:
Line 613: GenerateConstraints()
Line 335: Loop through the constraints
Line 339: An If statement: if (c.ConstructionFragment == c.ColumnName && c.ConstructionFragment != "##")

This if statement is evaluated to true, as each of the parts evalute to 'IsDeleted'

Line 341: FindColumn is then called, using just the column name and not the fully qualified column name, or the tablename
Line 151: A loop of the attached tables is looped to find the column name, however as stated at the beginning this is in fact located within the first table...

A possible fix would be to add an overload to the findcolumn method could in fact include an overloaded version where the tableName could also be passed in? E.g.

/// Finds the column.
///

/// Name of the column.
/// The name of the table the column belongs too
///
public IColumn FindColumn(string columnName, string tableName) {

IColumn result = null;
var t = query.FromTables.SingleOrDefault(x => x.Name == tableName);
if(t != null)
    return t.GetColumn(columnName);

return result;

}

and call it on line 341 (Now 357): IColumn col = FindColumn(c.ColumnName, c.TableName);

Which gave me:

...WHERE [dbo].[t_ProposedSubmissionDocs].[IsDeleted] = @0 AND [dbo].[t_ProposedSubmissionDocs].[IsHeld] = @1...

Im relatively new to Subsonic and not been with .Net and programming for more than 2 years, so I could be missing something obvious, apologies if I am.

Thanks,
Lee Cooper