SubSonic SELECT IN and BETWEEN
By admin on Aug 28, 2010 in .NET, Programming
For the SQL “select … from table where value in (v1, v2)”, in SubSonic you need to use the SqlQuery statement
1: // numbers is a List<string>
2: SqlQuery query = new Select().From("Pick44D").Where(Pick44DTable.MatchedNoColumn).In(numbers).OrderDesc(new string[] { "DrawDate" });
3: List<Pick44D> result = query.ExecuteTypedList<Pick44D>();
To add the BETWEEN clause
1: // SqlDateFormat = yyyy-MM-dd
2: SqlQuery query = new Select().From("Pick44D").Where(Pick44DTable.MatchedNoColumn).In(numbers).And(Pick44DTable.DrawDateColumn)
3: .IsBetweenAnd(dtpSearchFrom.Value.AddDays(-1).ToString(SqlDateFormat), dtpSearchTo.Value.AddDays(1).ToString(SqlDateFormat)).OrderDesc(new string[] { "DrawDate" });
4: List<Pick44D> result = query.ExecuteTypedList<Pick44D>();
