I have a problem with a query taking too long in c#?

Question

I have a query that has more than 7k records, but it is slow and the client takes more than 30 seconds to respond.

But the view itself only takes a second to execute in SQL.

How can I improve the performance of the following code?

I have tried putting it in a view and in a stored procedure but there was no improvement. The following C# code runs too slowly, while executing the view in SQL only takes a second.

var resultadoProductos = await Context.ViewQueryProductoAlls.Where(a => !a.Delet).AsNoTracking().ToListAsync();

if (model.Producto != null)
{
    resultadoProductos = resultadoProductos
        .Where(a => a.IdProducto.ToString().ToLower().Contains(model.Producto.ToString().ToLower())).ToList();
}

if (model.NombreProducto != null)
{
    resultadoProductos = resultadoProductos
        .Where(a => a.Producto.ToString().ToLower().Contains(model.NombreProducto.ToString().ToLower())).ToList();
}

if (model.Id != null)
{
    resultadoProductos = resultadoProductos.Where(a => a.Id == model.Id).ToList();
}

if (model.categoriaId != null)
{
    resultadoProductos = resultadoProductos.Where(a => a.IdCategoria == model.categoriaId).ToList();
}

if (model.PrecioDesde != null)
{
    resultadoProductos = resultadoProductos.Where(a => a.Precio >= model.PrecioDesde).ToList();
}

if (model.PrecioHasta != null)
{
    resultadoProductos = resultadoProductos.Where(a => a.Precio >= model.PrecioHasta).ToList();
}

var query = resultadoProductos.Select(x => new ProductoDTOList.DetalleProducto
{
    Id = x.Id,
    IdSucursal = x.IdSucursal,
    Sucursal = x.Sucursal,
    IdProducto = x.IdProducto,
    IdCategoria = x.IdCategoria,
    Categoria = x.Categoria,
    IdSubCategorias = Context.MaestraCategoria.Where(b => b.IdProducto == x.IdProducto && b.Delet == false)
        .Select(a => a.IdSubcategoria).FirstOrDefault(),
    IdUnidad = x.IdUnidad,
    Unidad = x.Unidad,
    IdEstado = x.IdEstado,
    Estado = x.Estado,
    CantidadProductoDisponible = x.CantidadProductoDisponible,
    IdModena = 1,
    Moneda = "RD$",
    Precio = x.Precio,
    Costo = (decimal)x.Costo,
    Producto = x.Producto,
    ProductoComentario = x.ProductoComentario,
    ListaImagenes = GetImagesProductoByColor(x.IdProducto),
    ConsultarImagenesDistinct = GetImagesProductoByColorDistint(x.IdProducto),
    Descuentos = GetDescuentosProducto(x.IdProducto),
    CarasteristicaColorSize = GetCarasteristicaProductoColor(x.Id),
    MeGusta = GetMeGusta(model.CodigoCliente, x.Id)
});

if (model.DescuentoDesde != null)
{
    query = query.Where(a => a.Descuentos.Any(b => b.Descuento >= model.DescuentoDesde)).ToList();
}

if (model.DescuentoHasta != null)
{
    query = query.Where(a => a.Descuentos.Any(b => b.Descuento <= model.DescuentoHasta)).ToList();
}

if (model.carasteristicas != null)
{
    query = query.Where(a => a.CarasteristicaColorSize.Any(b => model.carasteristicas.Contains(b.Size))).ToList();
}

Result.TotalRecord = resultadoProductos.Count();
Result.Skip = model.NumeroPagina;
Result.Take = model.CantidadRows;

Result.DataList = query.Skip((model.NumeroPagina - 1) * model.CantidadRows).Take(model.CantidadRows).ToList();

Answer 1

One thing that you should definitely not do is to call ToList on every filter operation. Enumerables are lazy and their execution is deferred and you should leverage that, and you definitely should leverage the dynamic query building capabilities, i.e. try something like:

IQueryable<ViewQueryProducto> query = await Context.ViewQueryProductoAlls.Where(a => !a.Delet).AsNoTracking();

if (model.Producto != null)
{
    query = query.Where(a => a.IdProducto.ToString().ToLower().Contains(model.Producto.ToString().ToLower()));
}
if (model.NombreProducto != null)
{
    query = query.Where(a => a.Producto.ToString().ToLower().Contains(model.NombreProducto.ToString().ToLower()));
}
//....

var result = await query.Select(...).ToListAsync()

You current code fetches all the non-deleted products into memory and then performs filtering client side (i.e. the await Context.ViewQueryProductoAlls.Where(a => !a.Delet).AsNoTracking().ToListAsync() call).

But the biggest problem is the following:

var query = resultadoProductos.Select(x => new ProductoDTOList.DetalleProducto
{
    // ...
    IdSubCategorias = Context.MaestraCategoria
        .Where(b => b.IdProducto == x.IdProducto && b.Delet == false)
        .Select(a => a.IdSubcategoria)
        .FirstOrDefault(),

   // ...
}

Which will result in the database query per resultadoProductos (so 7k queries if there are 7k products).

You need to rewrite the code so EF Core will issue one query (or constant number if something like AsSplitQuery() is used) for all needed data and then process it server side.

Please read:

• What is the difference between IQueryable<T> and IEnumerable<T>?
• How Queries Work
• Filtered include can be used too.