Query where contains take(20) - slow performance

[Samircj]

this query takes me 11~13 sec in table with 30.000 registers with another component i had it instantly

if(!String.IsNullOrWhiteSpace(cnpj))
                query = query.Where(c => c.CPF_CNPJ.StartsWith(cnpj));
            else
            {
                query = query.Where(
                    c =>
                    c.NOME_FANTASIA.Contains(pesquisaConteudo) ||
                    c.RAZAO_SOCIAL.Contains(pesquisaConteudo)
                    && c.SITUACAO.Equals("A"));
            }

            var lista = query
                             .Select(
                                 c =>
                                 new ClienteListViewDto
                                 {
                                     ClienteId = (int) c.CLIENTE,
                                     NomeFantasia = c.NOME_FANTASIA,
                                     RazaoSocial = c.RAZAO_SOCIAL,
                                     Cidade = c.NOME_CIDADE + "-" + c.NOME_ESTADO,
                                     Telefone = c.TELEFONE,
                                     Endereco = c.ENDERECO,
                                     CpfCnpj = c.CPF_CNPJ,
                                     RgIe = c.RG_IE,
                                     Situacao = c.SITUACAO,
                                     Cep = c.CEP
                                 }).Take(20)
                             .ToList();

[ssteiner]

I think this is because sqlite-net goes through all the records, then picks the first 20 (so you have all your data parsed to objects - you can verify that by checking out the source, and build your own copy rather than using the nuget package, then step through the code where the query is being sent to the database). Unfortunately, not everything you can do with linq is turned into the most efficient SQL. So you have to work around this by writing queries differently, or go ahead and improve the library itself.