CODEWITHSUNDEEP
c#sqlsubsonic
Mark
Mark

Reputation: 1

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

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

Do I miss something important here?

Kind regards, Mark

Upvotes: 0

Views: 254

Answers (2)

J&#252;rgen Steinblock
J&#252;rgen Steinblock

Reputation: 31723

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.

Upvotes: 2

Adam Cooper
Adam Cooper

Reputation: 8677

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

Upvotes: 0

Related Questions