Saturday, January 30, 2010

Linq 2 SQL - where statement querying multiple columns with the same value

I have a search function that I need to be able to search for a piece of text against two columns in the database. In this case, I'm searching for the title of a book, but I also want to be able to search against the subtitle column with the same search value.

For example, the book title may be "The Smith's Christmas", with a subtitle of "First in the Xmas dinners series". I know that 'dinner' is somewhere in the name of the book. I input the term 'dinner', and need it to search against both the Title and the Subtitle (which are two separate fields).

This is some of my code.

// Because I have multiple search fields, I create 'matches' as the search criteria that changes depending on what I input. Allows it to be a bit dynamic
IQueryable<Book> matches = LibraryModelHelpers.dc.Books;

// This is the lambda statement. Basically translates to get me a match where the title contains my input text OR the subtitle contains my input text
matches = matches.Where(c => (c.Title.Contains(myInputText) || c.Subtitle.Contains(myInputText)));

 // Using the search statement, create an anonymous type which has a title, the author, the bookID and the cover information - I don't care about the rest for this operation
 var coverDetails = from c in matches
                               select new { c.Title, c.Author, c.BookID, c.Cover };

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.