How to using select with multi table in SubSonic query ?

Question

Hi,

I am new with SubSonic and have a problem with query. This is my query string

string sql = "SELECT *" +
             " FROM tbl_exrates, tbl_currency" +
             " WHERE date = " + d;
             " AND tbl_exrates.currency = tbl_currency.cid" +
             " AND (cash > 0 OR transfer > 0 OR sell > 0)";

How to convert it to SubSonic Query string ? Does SS have function support to do that ?

Thanks !

Answer 1

It appears you can do quick complicated queries in Subsonic:

http://subsonicproject.com/querying/select-queries/

Answer 2

You should follow this link. It has all the select queries you are looking for.

Answer 3

q = new Select("*").From(TblExrate.Schema)
            .InnerJoin(TblCurrency.Schema)
            .WhereExpression("date").IsEqualTo(d)
            .AndExpression("cash").IsGreaterThan(0)
            .Or("transfer").IsGreaterThan(0)
            .Or("cash").IsGreaterThan(0);

Please help me check the code above, is it correct ?

Thanks !

Answer 4

q = new Select().From(TblExrate.Schema)
        .InnerJoin(TblCurrency.Schema)
        .Where(tbl_exrates.date).IsEqualTo(d)
        .AndExpression(tbl_exrates.cash).IsGreaterThan(0)
        .Or(tbl_exrates.transfer).IsGreaterThan(0)
        .Or(tbl_exrates.cash).IsGreaterThan(0);

Answer 5

q = new Select().From(TblExrate.Schema)
                .InnerJoin(TblCurrency.Schema)
                .Where(TblExrate.Columns.DateX).IsEqualTo(d)
                .And(TblExrate.Columns.Currency).IsEqualTo(TblCurrency.Columns.Currency)
                .AndExpression(TblExrate.Columns.Cash).IsGreaterThan(0)
                .Or(TblExrate.Columns.Transfer).IsGreaterThan(0)
                .Or(TblExrate.Columns.Sell).IsGreaterThan(0);

I try another query like above and get error : "Input string was not in a correct format.". Please tell me where is error ?

Thanks !