Error when searching large amount of data in Entity framework,

Question

I am putting together a report, and in it there is the option to consult by date period. When I consult with the interval of 30 or 60 days, I can obtain the data, where I group and return.

However, when increasing the query period, 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 the time out or the amount of data to be processed.

Obs: This same query that I made by Entity, I performed directly in sql server, passing the parameters, to check if really return data(and returned as expected).

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;
    }

Answer 1

It turns out that you are returning a lot of data, using grouping functions (Group By, Sum) and this requires a lot of Entity framework. When working with a lot of data it is recommended to use the SQL query itself, because 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 remains poor, I recommend creating indexes for your database.