Search all table fields in an Action

Question

Search all table fields in an Action

Navigation

#1 by (3 votes)

2

I would like to know how best to construct a query that looks in all the fields of a table.

Let's say I have a news site, and on this site I have input to Search in my template. When typing 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 to use IFs , but let's face it that it 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 this would be a "gambiarra . Then comes my question: How to search all fields in a single Action

c# asp.net-mvc-5 busca

asked by anonymous 29.04.2015 / 14:53

1 answer

How to update a listview from the action bar button Check if more site flaps X is open

score 3 · Accepted Answer

This is one of the classic problems of the service approach, which is unsuitable for the solution. Also, if your solution uses the Entity Framework, which already implements a repository, it is still more unsuitable.

In any case, since I do not know what you're using, I'll assume the approach by service itself.

The correct would be you to do a method that only searches for everything, for example:

var noticia = AppService.Pesquisar(texto);

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

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

For 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%';