Subsonic 2.1 : Am i crazy??

Question

Hi all,

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 change the query do this:

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

Do I miss something important here?

Kind regards, Mark

Answer 1

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>();

Answer 2

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.