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.
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 ///
}
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