How to build custom queries using Entity Framework?

Question

I have an SQL Server database that is accessed via ORM Entity Framework Core, where access to a collection of products, these products are filtered through a DTO that has the terms of the search, which can be composed or simple, example: the customer can inform in the search only the name of the product or Name, Manufacturing material and UF.

The question is how to assemble a query readable to cover all customizable scenarios?

DTO:

public class DtoPesquisaProduto
{
    public string Nome { get; set; }

    public string Material { get; set; }

    public EstadosEnum? Estado { get; set; }

    public decimal PrecoInicial { get; set; }

    public decimal PrecoFinal { get; set; }
}

The Consultation:

_context.Produtos 
.Where(x => x.Nome == pesquisa.Nome 
    && x.Material == pesquisa.Material 
    && x.Estado == pesquisa.Estado 
    && (x.Preco >= pesquisa.PrecoInicial && x.Preco <= pesquisa.PrecoFinal)) 
 .ToListAsync();

The consultation Where should be dynamic considering only the expression whose value is different from null.

Answer 1

One thing few people understand is that LINQ is an expression constructor. He’s not running all that at once, he does it one step at a time, as long as he’s in different methods. Your code is, in essence, the same as:

IQueryable<Produtos> query = _context.Produtos;
query = query.Where(x => x.Nome == pesquisa.Nome);
query = query.Where(x => x.Material == pesquisa.Material);
query = query.Where(x => x.Estado == pesquisa.Estado);
query = query.Where(x => x.Preco >= pesquisa.PrecoInicial && x.Preco <= pesquisa.PrecoFinal);
resultado = query.ToListAsync();

Now just put a if for each row to decide whether the expression will enter or not. Example:

IQueryable<Produtos> query = _context.Produtos;
if (pesquisa.Nome != null) query = query.Where(x => x.Nome == pesquisa.Nome);
if (pesquisa.Material != null) query = query.Where(x => x.Material == pesquisa.Material);
... //aqui coloca tudo o que deseja
resultado = await query.ToListAsync();

I put in the Github for future reference.

And think if really the Async() is the best option, in many cases it gets worse, especially if it is not used if correct form. And wonder if this is the right time to give the ToList(), It is very common for people to give before what they should for not understanding LINQ. _ is not idiomatic in C#. But these are other issues.

This works well for purely and, but if you need something more complex then you can use a predicate Builder or library that builds dynamically for you.

Answer 2

The .Where(this IEnumerable<T>, Predicate<T>) are loaded lazily, that is, they are not processed at the time of the declaration, but rather when the final consultation (the final result of the .Where()) is iterated.

IQueryable<Produto> query = _context.Produtos;

if (!string.IsNullOrEmpty(pesquisa.Nome))
{
    query = query.Where(where => where.Nome == pesquisa.Nome);
}

if (!string.IsNullOrEmpty(pesquisa.Material))
{
    query = query.Where(where => where.Material == pesquisa.Material);
}

if (pesquisa.Estado.HasValue)
{
    query = query.Where(where => where.Estado == pesquisa.Estado.Value);
}

// ... assim por diante, para cada propriedade

return query.ToList(); // Materializa query.

Answer 3

Hello Ricardo good night.

Come on you must want it within the same correct query ?

for this you must use a ternary operator

await _context.Produtos 
.Where(x => pesquisa.Nome != null ? x.Nome == pesquisa.Nome : false)
.Where(x => pesquisa.Material  != null ? x.Material == pesquisa.Material : false)
.Where(x => pesquisa.Estado != null ? x.Estado == pesquisa.Estado : false)
.Where(x => pesquisa.PrecoInicial != null ? x.Preco >= pesquisa.PrecoInicial : false)
.Where(x => pesquisa.PrecoFinal != null ? x.Preco <= pesquisa.PrecoFinal : false)
.ToListAsync();

A bit about ternary operator with Master Macoratti http://www.macoratti.net/14/11/c_tern1.htm

This way you will search for all attributes in the same query.

UPDATE

I apologize I had put a true at the end that picks null values from the table.

Use false that it will not fetch null values from the table.

Below is an example of the query generated in a table of mine with the true

.Where(x => pesquisa.PrecoInicial != null ? x.Preco >= pesquisa.PrecoInicial : true)

SELECT [a].[ViaturasID],[a].[ValorPrestacao]
FROM [Viaturas] AS [a]
WHERE ((@__prestacaoInicial_0 IS NOT NULL AND ([a].[ValorPrestacao] >= @__prestacaoInicial_1)) OR @__prestacaoInicial_0 IS NULL) AND ((@__prestacaoFinal_2 IS NOT NULL AND ([a].[ValorPrestacao] <= @__prestacaoFinal_3)) OR @__prestacaoFinal_2 IS NULL)

and with the false

.Where(x => pesquisa.PrecoInicial != null ? x.Preco >= pesquisa.PrecoInicial : false)

SELECT [a].[ViaturasID],[a].[ValorPrestacao]
FROM [Viaturas] AS [a]
WHERE (@__prestacaoInicial_0 IS NOT NULL AND ([a].[ValorPrestacao] >= @__prestacaoInicial_1)) AND (@__prestacaoFinal_2 IS NOT NULL AND ([a].[ValorPrestacao] <= @__prestacaoFinal_3))

================ NEW SOLUTION ================

Based on the comments of Virgilio Novic I really saw that this will make the consultation more effective.

Since this came all from the reply of Marcelo Uchimura.

IQueryable<Produtos> query = _context.Produtos;

if (pesquisa.Nome != null)
{
    query = query.Where(x => x.Nome == pesquisa.Nome);
}
if (pesquisa.Material  != null)
{
    query = query.Where(x => x.Material == pesquisa.Material);
}
if (pesquisa.Estado != null)
{
    query = query.Where(x => x.Estado == pesquisa.Estado);
}
if (pesquisa.PrecoInicial != null)
{
    query = query.Where(x => x.Preco >= pesquisa.PrecoInicial);
}
if (pesquisa.PrecoFinal != null)
{
    query = query.Where(x => x.Preco <= pesquisa.PrecoFinal);
}
query.ToList();

Example of query generated in a table of mine

SELECT [m].[ViaturasID],[m].[ValorPrestacao]
FROM [Viaturas] AS [m]
WHERE ([m].[ValorPrestacao] >= @__prestacaoInicial_0) AND ([m].[ValorPrestacao] <= @__prestacaoFinal_1)

If you want to use . Tolistasync() change the ending to

await query.ToListAsync();

Answer 4

 await _context.Produtos 
.Where(x => (pesquisa.Nome == null || x.Nome == pesquisa.Nome)
       &&   (pesquisa.Material  == null || x.Material == pesquisa.Material)
       &&   (pesquisa.Estado == null || x.Estado == pesquisa.Estado)
       &&   (pesquisa.PrecoInicial == 0 || x.Preco >= pesquisa.PrecoInicial)
       &&   (pesquisa.PrecoFinal == 0 || x.Preco <= pesquisa.PrecoFinal))
.ToListAsync();``

Make it equal to a query in SQL, if it is null it will not do anything in the query, if there is data it will make the comparison of each of them. Note: Decimal value comparison == 0 because it cannot be null.