Subsonic Single WHERE clause

Question

Is it possible to apply a WHERE clause on a SubSonic query?

For example, I get get a single based on id...

db.Single<Storage>(id);

But how can I get a single based on a simple WHERE clause?

db.Single<Storage>(WHERE columnname == "value");

Answer 1

that's possible:

// Will return a Storage instance with property IsNew = true, if record does not exist
// since an object created with new never can be null
var storage1 = new Storage(1); // id = 1
var storage1 = new Storage(Storag.Columns.ColumnName, "value");

// Will return 0 if record not found (subsonic3 only)
var storage3 = (from s in Storage
               where s.ColumnName == "value"
               select s).SingleOrDefault();

// Will throw an exception if record not found  (subsonic3 only)
var storage3 = (from s in Storage
               where s.ColumnName == "value"
               select s).Single();   

Since db is a partial class you can extend it. Just create a new File within the same namespace (but another folder in your solution). This applies to subsonic 2 but will be similar to subsonic 3, I think.

public static partial class DB
{
    public static T Single<T>(String columName, Object columnValue) where T: RecordBase<T>, new()
    {
        return Select().From<T>()
                       .Where(columnName).IsEqualTo(columnValue)
                       .ExecuteSingle<T>();
    }
}

Answer 2

Thanks for the above, this was a help and eventually I simplified this to the below...

        db.Single<Storage>(s => s.ColumnName == "value");