Linq search a list and a linked property

Question

I have a list of Books, each book has a linked Author. My Api allows a user to search either by Title or Author.

In the Api i have a search Model

public class SearchOptions
{
    public string Title { get; set; }
    public string Author { get; set; }
}

The user may use both fields or just Title or just Author.

Here are my my Books and Author Models

public class Book
{        
    public string Title { get; set; }
    public string Description { get; set; }
    public Author LinkedAuthor { get; set; }      
}

public class AuthorEntity
{
        public string FirstName { get; set; }
        public string LastName { get; set; }
}

I've created the following Linq query but its not quite right because when the author is not completed i.e. it's a blank string i get all the results back.

I almost want to say if the string is not null or empty then apply the filter for author (and the same with the title) but I'm not sure how to do this in Linq?

var returnedBooks = _bookRepository.GetAll()
            .Where(x => x.Title.Contains(title) ||
            (x.LinkedAuthor.FirstName + " " + x.LinkedAuthor.LastName).Contains(authorName)).ToList();

Possible duplicate of [Linq optional parameters](http://stackoverflow.com/questions/10031067/linq-optional-parameters) — ASh, Sep 21 '16 at 07:49
I don't understand, if the search-title or search-autor are String.Empty you don't want to find anything? If so, why don't you handle this in a plain `if` before the query? You can return `Enumerable.Empty()`. — Tim Schmelter, Sep 21 '16 at 07:50
What is `_bookRepository` and what does `GetAll()` return? Unless it's an IQueryable, you have a nasty bug that loads everything from the database into memory instead of executing the query that will only return the relevant results. Second, why not search each author field individually? As it is, you generate a new temporary string for each author. If the query hits the database, you'll lose any indexing benefit too — Panagiotis Kanavos, Sep 21 '16 at 08:09

Farhad Jabiyev · Accepted Answer · 2016-09-21T08:14:41.907

2

You can create where part of the query by checking values of the properties one by one. If user entered any title, then add filter for this property. If user entered name of the author, then add filter and so on...

var books = _bookRepository.GetAll();

if (!String.IsNullOrEmpty(title))
    books = books.Where(x => x.Title.Contains(title));

if (!String.IsNullOrEmpty(authorName))
{
    books = books.Where(x => x.LinkedAuthor.FirstName.Contains(authorName) || x.LinkedAuthor.LastName.Contains(authorName));
}

edited Sep 21 '16 at 08:14

answered Sep 21 '16 at 07:53

Farhad Jabiyev

26,014
8
72
98

The double `Where` calls result in an `AND` instead of an `OR`. You are asking that the author's name appears in the FirstName *and* the LastName. You should use `x.LinkedAuthor.FirstName.Contains(authorName) || x.LinkedAuthor.LastName.Contains(authorName)` – Panagiotis Kanavos Sep 21 '16 at 08:13

score 0 · Answer 2 · edited Sep 21 '16 at 08:05

0

var returnedBooks = 
       from a in _bookRepository.GetAll() 
       where (
                  a.Title.Contains(title)) || 
                  ((a.LinkedAuthor.FirstName + " " + a.LinkedAuthor.LastName) != null 
                  && (a.LinkedAuthor.FirstName + " " + a.LinkedAuthor.LastName).Contains(authorName)
               ) 
         select a)
         .ToList();

edited Sep 21 '16 at 08:05

Panagiotis Kanavos

120,703
13
188
236

answered Sep 21 '16 at 07:52

Balagurunathan Marimuthu

2,927
4
31
44

1

First, answers without any code that explains what the answer does, aren't very helpful. Second, the code should be formatted so that others can actually read it. Finally you can get rid of the concatenation with a `let fullName = a.LinkedAuthor.FirstName + " " + a.LinkedAuthor.LastName` clause before `where`. This also avoid generating the same strings twice – Panagiotis Kanavos Sep 21 '16 at 08:11

score 0 · Answer 3 · edited May 23 '17 at 12:32

You can check each query term and construct where clause based on them.

I assume you receive a request model - SearchOptions (with your query terms) : searchOptions

var books = _bookRepository.GetAll();

if (!string.IsNullOrEmpty(searchOptions.Title))
    books = books.Where(x.Title.Contains(searchOptions.Title));

if (!string.IsNullOrEmpty(searchOptions.AuthorName))
    books = books.Where((x.LinkedAuthor.FirstName + " " + x.LinkedAuthor.LastName).Contains(searchOptions.AuthorName));

Also make sure that you implement a method in your repository _bookRepository.GetQueryable() returning IQueryable for this type of constructed where clauses.

And what you'll implement then is the following:

var query = _bookRepository.GetQueryable();

if (!string.IsNullOrEmpty(searchOptions.Title))
   query = query.Where(x.Title.Contains(searchOptions.Title));

if (!string.IsNullOrEmpty(searchOptions.AuthorName))
   query = query.Where((x.LinkedAuthor.FirstName + " " + x.LinkedAuthor.LastName).Contains(searchOptions.AuthorName));

var books = query.ToList(); // or i don't know, just enumerate

You can read more here: https://softwareengineering.stackexchange.com/questions/192044/should-repositories-return-iqueryable

And one more reference for dynamic where clauses: How do I implement a dynamic 'where' clause in LINQ?

Linq search a list and a linked property

3 Answers3