Subsonic: comparing two columns instead of an input parameter

Question

s can do the following in subsonic.

SELECT * FROM TABLE1

WHERE Column1> Column2 or Column1 <Colum3

All the examples I've seen assume that you are now moving on to the where clause. I am trying to do this without creating a view.

thank

Answer 1

If it's on our stack, I can't find it :). It would be nice to add though :). For now, you can use the Inline query to simply execute the expression you wrote (requires direct SQL). I know this awkwardly, but ...

Rick - if you did, I would be wondering how to do it. "Col2" will try to sort out the type and your request will fail.

Answer 2

, , ​​ .

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 New Property)

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

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

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

SubSonic / SQLQuery / SqlGenerators / ANSISqlGenerator.cs
(Inside the 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)()