Search query with Subsonic

Question

Ok,

Today I am trying to learn Subsonic. Pretty cool stuff.

I am trying to build some search functionality into my website but am struggling about how I might achieve this in Subsonic.

I have one search field that could contain multiple keywords. I want to return results that match all of the keywords. The target on the search is a single text column.

So far I have this (it runs but never returns results):

return new SubSonic.Select().From(Visit.Schema)
            .InnerJoin(InfopathArchive.VisitIdColumn, Visit.VisitIdColumn)
            .Where(InfopathArchive.XmlDocColumn).Like(keywords)
            .ExecuteTypedList<Visit>();

There is a one to one mapping between the Visit table and the InfoPathArchive table. I just want to return the collection of Visits that have the keywords in the related XMLDocColumn.

If I could get that working it would be great. Now the second problem is that if someone searches for 'australia processmodel' then obviously the above code should only return that exact phrase. How can I create a query that splits up my search term so that it must return documents that contain ALL of the individual search terms?

Any help appreciated.

Edit: Ok, so the basic search works, but the multiple keyword search doesnt. I did what Adam suggested but it seems Subsonic only uses one parameter for the query.

Here is the code:

        List<string> wordsInQueryList = keywords.Split(' ').ToList();

        SqlQuery q = Select.AllColumnsFrom<Visit>()
            .InnerJoin(InfopathArchive.VisitIdColumn, Visit.VisitIdColumn)
            .Where(Visit.IsDeletedColumn).IsEqualTo(false);

        foreach(string wordInQuery in wordsInQueryList)
        {
            q = q.And(InfopathArchive.XmlDocColumn).Like("%" + wordInQuery + "%");
        }

return q.ExecuteTypedList();

Then if I look at the query that Subsonic generates:

SELECT (bunch of columns)

 FROM [dbo].[Visit]
 INNER JOIN [dbo].[InfopathArchive] ON [dbo].[Visit].[VisitId] = [dbo].[InfopathArchive].[VisitId]
 WHERE [dbo].[Visit].[IsDeleted] = @IsDeleted
 AND [dbo].[InfopathArchive].[XmlDoc] LIKE @XmlDoc
 AND [dbo].[InfopathArchive].[XmlDoc] LIKE @XmlDoc

So it ends up that only the last keyword is being searched for.

Any ideas?

Answer 1

First question:

return new SubSonic.Select().From(Visit.Schema)
        .InnerJoin(InfopathArchive.VisitIdColumn, Visit.VisitIdColumn)
        .Where(InfopathArchive.XmlDocColumn).Like("%" + keywords + "%")
        .ExecuteTypedList<Visit>();

Second question:

Pass a List of words in your query to a function that builds a SubSonic query as follows

SqlQuery query = DB.Select().From(Visit.Schema)
        .InnerJoin(InfopathArchive.VisitIdColumn, Visit.VisitIdColumn)
        .Where("1=1");

foreach(string wordInQuery in wordsInQueryList)
{
  query = query.And(InfopathArchive.XmlDocColumn).Like("%" + wordInQuery + "%")
}

return query.ExecuteTypedList<Visit>();

Obviously this is untested but it should point you in the right direction.

Answer 2

You can do what Adam is suggesting or with 2.2 you can simply use "Contains()" instead of Like("%...%"). We also support StartsWith and EndsWith() :)