Subsonic 2.1 : Am i crazy?

Im working on a project using subsonic 2.1. For a simple search query i've the following code:

            DAL.ItemCollection coll = new DAL.ItemCollection();
        SubSonic.Select s = new SubSonic.Select();
        s.From(DAL.Item.Schema);
        s.Where("Title").Like("%" + q + "%").Or("Tags").Like("%" + q + "%").And("IsActive").IsEqualTo(true);

        if (fid > 0)
        {
            s.And("CategoryID").IsEqualTo(fid);
            Session["TotalSearchResults"] = null;
        }
        s.Top(maxitems);

        //We'll get the recordcount before paged results
        total = s.GetRecordCount();

        s.Paged(pageindex, pagesize);
        s.OrderDesc("Hits");
        s.OrderDesc("Points");
        s.OrderDesc("NumberOfVotes");
        coll = s.ExecuteAsCollection<DAL.ItemCollection>();

Now the thing is, when my fid (FilterId) is larger then 0, the CategoryID filter does not work. It hits the breakpoint no problem but it's not getting filtered. Does it have something do do with the LIKE query? It works perfectly if i remove the if part and current s.where and chan开发者_如何学Cge the query do this:

s.Where("CategoryID").IsEqualTo(fid);

Do I miss something important here?

Kind regards, Mark

It looks like you are querying:

SELECT * FROM Schema 
WHERE title LIKE ... 
   OR tags LIKE ... 
   AND isactive = true 
   AND categoryid = ...

but want you want is:

SELECT * FROM Schema 
WHERE (title LIKE ... OR tags LIKE ...)
   AND isactive = true 
   AND categoryid = ...

You can get brackets in subsonic 2.1 with the AndExpression/OrExpression syntax follwed by CloseExpression.

It's been a while since I've used 2.1 but I would expect the following to work:

    DAL.ItemCollection coll = new DAL.ItemCollection();
    SubSonic.Select s = new SubSonic.Select();
    s.From(DAL.Item.Schema)
        .Where("Title").Like("%" + q + "%")
        .Or("Tags").Like("%" + q + "%")
        .And("IsActive").IsEqualTo(true);

    if (fid > 0)
    {
        s = s.And("CategoryID").IsEqualTo(fid);
        Session["TotalSearchResults"] = null;
    }
    s = s.Top(maxitems);

    //We'll get the recordcount before paged results
    total = s.GetRecordCount();

    s = s.Paged(pageindex, pagesize);
        .OrderDesc("Hits");
        .OrderDesc("Points");
        .OrderDesc("NumberOfVotes");
    coll = s.ExecuteAsCollection<DAL.ItemCollection>();