Subsonic 3 query like clause

Question

This may be another of those "should be simple" things, but I can't find anything on it so here I am asking what will most likely be a stupid question :$

I'm using SubSonic 3 and I want to execute a query with a like clause but can't find that as an option. My straight SQL is like so:

select * from table where column like %value% or column like %anothervalue%

Thanks for any help.

Jon

Answer 1

You can do this with the fluent interface as follows:

List<Product> products = new MyDB().Select
            .From(ProductTable)
            .Where(ProductTable.CategoryColumn).Like("%searchstring%")
            .ExecuteTypedList<Product>();

Or using Contains:

List<Product> products = new MyDB().Select
            .From(ProductTable)
            .Where(ProductTable.CategoryColumn).Contains("searchstring")
            .ExecuteTypedList<Product>();

MyDB is your generated DB name

Or using linq:

List<Product> products = from p in Product.All()
                         where p.Category.Contains("searchstring")
                         select p;

Answer 2

If you're using Linq you can use StartsWith(), EndsWith(), and Contains()

Answer 3

I haven't looked at this project in a while so I just got around to implementing these suggestions. A single clause seems to work, but what if I want to do multiple checks. My example is

var searchQueryNum = from b in number.all() where b.number.Contains(Request.Params["t"])

At this point I want to also have a contains check on another column in the same table. Basically my search is searching for the string in one of 4 columns and I want to do a like compare on each in the same query. SQL would be someting like

select * from numbertable where number like '%blah%' OR person like '%blah%'

etc etc.

Since I'm using linq when I get to the end of the first contains I try to do a OR or .or but I don't see those as options. Is what I want to do possible? Thanks again!