Subsonic: Comparing two columns instead of an input parameter

Question

s it possible to do the following in subsonic.

SELECT * FROM TABLE1

WHERE Column1 > Column2 or Column1 < Colum3

All examples that I've seen assume you now a value to pass to the where clause. I'm trying to do this without creating a view.

Thanks

Answer 1

If it's in our stack I can't find it :). It would be a good thing to add though :). You can, for now, use an Inline query to simply execute the statement you wrote (it takes straight SQL). I know it's fugly but...

Rick - if you did get that to work I'd be interested in how. "Col2" will try and be parsed to a type and your query will fail.

Answer 2

It appears this functionality is not in the current version but has been submitted into the code for the next release.

Answer 3

If you are using SubSonic 2.1/2.2 and you have access to the source you can apply the following:

SubSonic/SqlQuery/Constraint.cs
(Add a new property)

public bool ParameterIsTableColumn
{
    get { return ParameterValue is TableSchema.TableColumn ;  }
}

SubSonic/SqlQuery/SqlQuery.cs
(Inside SetConstraintParams method)

foreach(Constraint c in qry.Constraints)
{
    if (c.ConstructionFragment == "##" || c.ParameterIsTableColumn)
        continue;

SubSonic/SqlQuery/SqlGenerators/ANSISqlGenerator.cs
(Inside BuildConstraintSQL method)

//add this at the top of the method
int currentConstraintIndex = query.Constraints.IndexOf(c);

///the statement 'c.ParameterName = ' occurs four times in this method
///use this line the first three times, and a slight variation of it on the fourth
c.ParameterName = (c.ParameterIsTableColumn ? ((TableSchema.TableColumn)c.ParameterValue).QualifiedName : String.Concat(col.ParameterName, currentConstraintIndex));

Answer 4

Yes it is.

Dim TableList As Generic.List(Of Database.Table1) = _
 New SubSonic.Select().From("Table1"). _
 Where("Col1").IsGreaterThan("Col2"). _
 Or("Col1").IsLessThan("Col3").ExecuteTypedList(Of Database.Table1)()