Error when querying large amount of data in the Entity framework,

2

I am putting together a report, and there is the option to query by date range. When I query with the interval of 30 or 60 days, I get the data, where I cluster and return.

However, when you increase the query time, the same method returns the error of: 500 (Internal Server Error) . Searching, I noticed that in some cases the entity framework terminates the query because of time out or the amount of data to process.

public IQueryable ObterNotaSimplificada(DateTime compInicial, DateTime compFinal,
        PorteContribuinte? codigoPorte, string query, int? itemServico, bool? simplesNacional)
    {
        var res = this.Servicos
            .Include(x => x.Prestador)
            .Where(a => (a.Competencia >= compInicial && a.Competencia <= compFinal) &&
                        (codigoPorte == null || a.Prestador.Porte.Value == codigoPorte) &&
                        (simplesNacional == null || a.Prestador.IsSimplesNacional == simplesNacional) &&
                        (itemServico == null || a.ItemListaServico.Id == itemServico) &&
                        ((query == null || a.Prestador.RazaoSocial.Contains(query))
                         || (a.Prestador.CpfCnpj.Contains(query))))
            .Where(a => a.DataCancelamento == null)
            .GroupBy(a => a.Prestador.CpfCnpj)
            .Select(g => new
            {
                CNPJ = g.Select(x => x.Prestador.CpfCnpj),
                Competencia = g.Select(x => x.Competencia),
                RazaoSocial = g.Select(x => x.Prestador.RazaoSocial),
                SimplesNacional = g.Select(x => x.Prestador.IsSimplesNacional),
                Porte = g.Select(x => x.Prestador.Porte),
                DescricaoPorte = g.Select(x => x.Prestador.Porte.ToString()),
                QtdNotasEmitidas = g.Count(),
                BaseDeCalculo = g.Select(x => x.BaseCalculo),
                ValorDeducoes = g.Select(x => x.ValorDeducoes),
                ValorServicos = g.Select(x => x.ValorServicos),
                ValorIss = g.Select(x => x.ValorInss),
                TotalBaseDeCalculo = g.Sum(x => x.BaseCalculo),
                TotalValorDeducoes = g.Sum(x => x.ValorDeducoes),
                TotalValorServicos = g.Sum(x => x.ValorServicos),
                TotalValorIss = g.Sum(x => x.ValorIss),
                IssRetido = g.Select(x => x.IssRetido > decimal.Zero),
                ItemDeServico = g.Select(x => x.ItemListaServico.Descricao),
                CodItemServico = g.Select(x => x.ItemListaServico.Codigo)
            });
        return  res;
    }
    
asked by anonymous 28.06.2018 / 21:16

1 answer

0

It turns out that you are returning a lot of data, using grouping functions (Group By, Sum) and this requires a lot from the entity framework. When working with a lot of data it is recommended to use the SQL query itself, because the performance is better.

Try using this example, but adapting to your query:

var results = context.Database.SqlQuery<string>("SELECT COLUNAS FROM NOMETABELA").ToArray();

If performance is still poor, I recommend that you create indexes for your database.

    
28.06.2018 / 21:40