Search all table fields in an Action

Question

I would like to know how best to prepare a query that searches in all fields of a table.

Let’s say I own a news site, and on this site I have a input for Search in my template. When entering the information in this template, it needs to search in several columns in the table, such as title, description, author, etc. What is the best way to do this?

One way to do this search is by using IFs, but let’s face it that is not good to use. And from this premise comes my doubt.

An example would be:

    public ActionResult Buscar(string texto)
        {
            var titulo= AppService.ObterPorTitulo(texto);

            if (!titulo.Any())
            {
                var descricao= AppService.ObterPorDescricao(texto);

                if (!titulo.Any())
                {
                    var autor= AppService.ObterPorAutor(texto);
                        return View("index", autor);
                }
                return View("index", descricao);
            }
            return View("Index", titulo);
        }

I think that would be a gambiarra. Then comes my doubt: How to search in all fields in a single Action

Answer 1

This is one of the classic problems of the service, that there is inadequate for the solution. Still, if your solution uses Entity Framework, which already implements a repository, it is even more inadequate.

In any case, since I don’t know what you’re using, I’ll assume the approach by service even.

The correct thing would be for you to do is just to search for everything, for example:

var noticia = AppService.Pesquisar(texto);

Pesquisar will have to have a call to a sentence that searches together in all desired fields. In Entity Framework would look like this:

var noticia = contexto.Noticias
                      .FirstOrDefault(n => n.Titulo.Contains(texto) ||
                                           n.Descricao.Contains(texto) ||
                                           n.Autor.Nome.Contains(texto));

Why SQL would look like this:

SELECT * 
FROM NOTICIAS N
INNER JOIN AUTOR A ON N.AUTORID = A.AUTORID
WHERE N.TITULO LIKE '%:TEXTO%'
OR N.DESCRICAO LIKE '%:TEXTO%'
OR A.NOME LIKE '%TEXTO%';