Subsonic 3.0: How to use LIKE on objects?

Question

New to Subsonic 3.0 and wondering how to perform LIKE operators on object attributes. Given the following class, how would I perform a LIKE operation using Subsonic 3.0. For example SELECT * FROM categories WHERE name LIKE '%foo%';

public class Category
{
    private String categoryID;
    private String name;

    public Category()
    {
        //  Do Nothing
    }

    public Category(String name)
    {
        this.Name = name;
    }

    public string CategoryID
    {
        get { return this.categoryID; }
        set { this.categoryID = value; }
    }

    public string Name
    {
        get { return this.name; }
        set { this.name = value; }
    }

}

Answer 1

You can construct the like by using something similar to the following:

var keyword = "foo";

return new SubSonic.Select().From(Categories.Schema)
   .Where(Categories.name).Like("%" + keyword + "%")
   .ExecuteTypedList<Categories>();

Not exactly sure if it is exact correct code, but you should get the gist.

Answer 2

A better way to do it would be:

return new SubSonic.Select().From(Categories.Schema)
   .Where(Categories.name).Contains("foo")
   .ExecuteTypedList<Categories>();

This will use provider-specific wildcards. You can also use StartsWith and EndWith.

Answer 3

LIKE '%foo%' is for TSQL; for objects we have to use LIKE '*foo'

Note: Just replace % with a *;

Answer 4

I think Robs response holds the most water as it is provider independant - one of the huge selling points of having a DAL in general is not being locked to one database engine.

Stick with:

.Contains()