LINQ: query very slow

Question

I have the following database structure (just showing the pertinent tables): I need to get a listing of all the elements in the table "Propietario" and each element must have a list of all its related elements in "Clave". Specifically, I need the field "CodigoClave" from "Clave".

Also, I need to be able to filter the results any of the fields.

This is what I've come up with so far:

int pageSizeP = (pageSize == null || pageSize == 0 || pageSize > 15) ? 15 : (int)pageSize;
int pageNumberP = (pageNumber == null || pageNumber == 0) ? 1 : (int)pageNumber;

var propietariosCount = context.Propietarios.Count();
var propietariosQuery = context.Propietarios
                        .AsEnumerable()
                        .OrderBy(p => p.Nombre).ThenBy(p => p.Apellido1).ThenBy(p => p.Apellido2)
                        .Select(p => new
                        {
                            Id = p.Id,
                            Nombre = p.Nombre,
                            Apellido1 = p.Apellido1,
                            Apellido2 = p.Apellido2,
                            NIF = p.NIF,
                            Claves = string.Join(", ", context.PropietariosFincas
                                                        .Where(pf => pf.PropietarioId == p.Id)
                                                        .Join(context.Expedientes
                                                                .Include(e => e.Clave),
                                                                pf => pf.FincaId,
                                                                e => e.FincaId,
                                                                (pf, e) => (e.Clave.CodigoClave + ((e.Clave.Alias == null) ? "" : " - " + e.Clave.Alias))
                                                        ).Distinct().ToList())
                           // Getting an array of Claves would do; 
                           // I could turn it into a string in the JS app
                           // But it's just as slow
                        });

if (criteriosBusqueda != "")
{
    string[] aCriterios = criteriosBusqueda.Split(new string[] { "," }, StringSplitOptions.None);

    CompareOptions compareOptions = CompareOptions.IgnoreCase;
    if (aCriterios[0] != "")
        propietariosQuery = propietariosQuery
                        .Where(p => p.Nombre != null)
                        .Where(p => p.Nombre.Replace("/", "__").ContainsIgnoreAccents(aCriterios[0], compareOptions));

    if (aCriterios[1] != "")
        propietariosQuery = propietariosQuery
                        .Where(p => p.Apellido1 != null)
                        .Where(p => p.Apellido1.Replace("/", "__").ContainsIgnoreAccents(aCriterios[1], compareOptions));

    if (aCriterios[2] != "")
        propietariosQuery = propietariosQuery
                        .Where(p => p.Apellido2 != null)
                        .Where(p => p.Apellido2.Replace("/", "__").ContainsIgnoreAccents(aCriterios[2], compareOptions));

    if (aCriterios[3] != "")
        propietariosQuery = propietariosQuery
                        .Where(p => p.NIF != null)
                        .Where(p => p.NIF.Replace("/", "__").ToLower().Contains(aCriterios[3].ToLower()));

    if (aCriterios[4] != "")
        propietariosQuery = propietariosQuery
                        .Where(p => p.Claves != null)
                        .Where(p => p.Claves.Replace("/", "__").ToLower().Contains(aCriterios[4].ToLower()));

}
var propietariosList = propietariosQuery.Skip((pageNumberP - 1) * pageSizeP).Take(pageSizeP)
                        .ToList();
var datosPropietarios = new
{
    pageSize = pageSizeP,
    pageNumber = pageNumberP,
    recordsNumber = propietariosQuery.Count()
    titulares = propietariosList
};

return Ok(datosPropietarios);

It works, but it's too slow (it takes several minutes). I've managed to make the initial query (the first page, unfiltered) fast by making recordsNumber = context.Propietarios.Count() but that breaks the pagination when filtering. And anyway, if I filter or if I go to a page near the end, it is again really slow.

How can I make the query faster? My guess is that the query is pretty much traversing all the tables, so I don't know if there's even a solution that doesn't involve changing the data model.

Answer 1

It's because you are calling AsEnumerable, you are fetching all the data from Propietarios table to memory and the rest of operations you do after you call that method are executed using Linq to Objects instead of Linq to Entities, so your query is not translated to sql. Try removing it

Take a look of this post in case you need more info how AsEnumerable works with EF