Subsonic - Simple Query

Question

Hi

I have a table has 2 columns

Val1 int

Val2 int

My query very simple.

I want to get collection of record have condition (val1=Val2) ,

equivalent to (Select * from table where Val1=Val2)

I try

IDataReader rdr = new Query("Table").WHERE("Val1=Val2").ExecuteReader(); tableColl.LoadAndCloseReader(rdr); rdr.Close();

and

  ..WHERE (" 'Val1=Val2' ")
  ..WHERE (Table.Columns.Val1,IsEqualTo,Table.Columns.Val2) //This not reguler I know
  ..WHERE ("Val"+'='+"Val2") 
  .....

any help be more apricated.

Thanks.

Answer 1

Unfortunately you'll need to do this as an inline query as far as I know:

TableCollection tableCollection = new InlineQuery()  
  .ExecuteAsCollection<TableCollection>(
    "SELECT * FROM " + Table.Schema.TableName " WHERE " + Table.Columns.Val1 + " = " + Table.Columns.Val2);

Answer 2

was in same situation recently and came up with this:

TableCollection tablecollection = new TableCollection;
Comparison comp = Comparison.Equals;
tablecollection.Where(Table.Columns.Val1, comp, Table.Columns.Val2);
tablecollection.Load();

i found this better because i don't like inline queries. and it gives more flexibility if you wish to allow for adhoc querying in you app.

Answer 3

private void CreateDynamicControls()
{


 panGvHolder.Controls.Clear();

 Query qry = Northwind.Product.CreateQuery();
 qry.Columns.AddRange(Northwind.Product.Schema.Columns);
 qry.WHERE("UnitPrice > 15").AND("UnitsInStock < 20 ");
 //WHERE("UnitPrice > 15").AND("UnitsInStock < 30 ");



 using (IDataReader rdr = qry.ExecuteReader())
 {
  Response.Write("<table>");
  while (rdr.Read())
  {
   Response.Write("<tr>");
   for (int i = 0; i < rdr.FieldCount; i++)
   {
    Response.Write("<td>");
    Response.Write(rdr[i].ToString() + " ");
    Response.Write("<td>");
   } //eof for 
   Response.Write("</br>");
   Response.Write("</tr>");
  }
  Response.Write("<table>");
 }
} //eof method