Include dynamic Entity Framework Core

Question

I have the following relationship in my database, a product has several presentaciones_prouduct, and in my query I need to include them only if it has at least one product-presentation, for this purpose and created a boolean attribute lista_precios in the product table, as an indicator, this will handle internally.

Make the following query, to include the list, but it takes a long time, and I have several products that do not need it :

var producto = await _context.Producto
                             .Select(x => new
                                          {
                                              x.Id,
                                              x.Nombre,
                                              x.NombreSecundario,
                                              x.MarcaId,
                                              x.IdCategoria,
                                              x.IdUnidad,
                                              x.Precio,
                                              x.PrecioCompra,
                                              x.Codigo,
                                              x.CantidadInicial,
                                              x.CantidadMinima,
                                              x.ListaPrecios,
                    Include------------>      x.PresentacionesProducto,
                                              x.Descripcion
                             }) 
                             .AsNoTracking()
                             .FirstOrDefaultAsync(x => x.Id== IdProducto);

Now I try to cause the least overhead in the system, only including the list if any product has it, this dynamically

 if (producto.ListaPrecios) {}

Question: How would the most efficient consultation in this case be, include the product presentations only if you had them

Answer 1

Firstly, you have to get that one element from db(if exist), and after load navigation property if necessary.

Code:

var producto = await _context.Producto
                    .AsNoTracking() //Maybe it is not need
                    .FirstOrDefaultAsync(x => x.Id == IdProducto);

if(producto != null && producto.ListaPrecios)
     _context.Entry<Producto>(producto)
          .Navigation(nameof(producto.PresentacionesProducto))
          .Load();                         

Answer 2

How about that: x.PresentacionesProducto.Where(p=>producto.ListaPrecios)

With your code:

            var producto = await _context.Producto
                         .Select(x => new
                                      {
                                          x.Id,
                                          x.Nombre,
                                          x.NombreSecundario,
                                          x.MarcaId,
                                          x.IdCategoria,
                                          x.IdUnidad,
                                          x.Precio,
                                          x.PrecioCompra,
                                          x.Codigo,
                                          x.CantidadInicial,
                                          x.CantidadMinima,
                                          x.ListaPrecios,
                                          PresentacionesProducto = x.PresentacionesProducto.Where(p=>producto.ListaPrecios),
                                          x.Descripcion
                         }) 
                         .AsNoTracking()
                         .FirstOrDefaultAsync(x => x.Id== IdProducto);