Search Filter

Question

I have an Items entity that relates to the product table and it is already in production and is working perfectly! Now I need to create a search report in which the user can determine through filters the conditions of this query. The query fields the user can choose are:

• Date - Date Request To
• Shop
• Item
• Brand
• Forma Pagamento.

The report is very simple and the result will be displayed through a View with List. The report has already been created without the use of fields previously informed. I am using Entityframework in the queries and for this report I am currently using this query:

return Db.TabCadStatusOrdemServicoItem.ToList().OrderByDescending(x => x.Descricao);

The question is: how do I include the fields in the above query knowing that for some fields the user can select the all option (e.g. store)?

Grateful for the strength!

Answer 1

If I understand the question correctly, you can do something like this:

var result = Db.TabCadStatusOrdemServicoItem.ToList();

//Para cada campo do filtro, adicionar um bloco If igual ao abaixo
if (!modelFiltro.Foo.IsNullOrWhiteSpace())
{
    result = result.Where(x => x.Foo.Equals(modelFiltro.Foo))
}


return result.OrderByDescending(x => x.Descricao);

Of course, if the user selects "All" in the store filter, you simply do not submit any store ID.

Answer 2

I think that this answer here can be useful for you.

In your case, you can make a Viewmodel whose fields are Nullable:

public class ParametrosPesquisaViewModel
{
    public DateTime? DataInicial { get; set; }
    public DateTime? DataFinal { get; set; }
    public int? LojaId { get; set; }
    public int? ItemId { get; set; }
    public int? MarcaId { get; set; }

    public IEnumerable<TabCadStatusOrdemServicoItem> Resultados { get; set; }
}

Example of predicate construction:

        var query = db.TabCadStatusOrdemServicoItem.AsQueryable();
        if (viewModel.DataInicial != null)
        {
            if (viewModel.DataFinal != null)
            {
                query = query.Where(a => a.Data >= viewModel.DataInicial && a.Data <= viewModel.DataFinal);
            }
            else
            {
                query = query.Where(a => DbFunctions.TruncateTime(a.Data) == viewModel.DataInicial);
            }
        }

        if (viewModel.LojaId != null)
        {
            query = query.Where(a => a.LojaId == (int)viewModel.LojaId);
        }

        // E assim por diante.

         return query.ToList();