SUM within SUM Entity Framework perfomance

Question

SUM within SUM Entity Framework perfomance

Navigation

#1 by (2 votes)

0

I've done a query with entity framework and I'm having performance issues.

 public static Decimal ValorPrevisto(this ClinicaModel clinica)
        {
            return clinica.Contratos.Sum(c => c.Mensalidades.Where(m => m.Pagamento != null).Sum(t => t.Valor)) / 2;
        }

This code is taking more than 3 minutes to run because it is scanning per record, I searched several times and could not find a way to improve performance.

ClinicalModel class

public class ClinicaModel : Base.BaseModel
    {
        public string Nome { get; set; }
        public DateTime HoraExpedienteInicio { get; set; }
        public DateTime HoraExpedienteTermino { get; set; }
        public int DuracaoConsulta { get; set; }
        public int DiasExpediente { get; set; }
        public string Email { get; set; }
        public virtual EnderecoModel Endereco { get; set; }
        public virtual ContatoModel Telefone { get; set; }
        public virtual PessoaFisicaModel Responsavel { get; set; }
        public virtual ICollection<UsuarioModel> Usuarios { get; set; }
        public virtual ICollection<DoutorModel> Doutores { get; set; }
        public virtual ICollection<ConvenioModel> Convenios { get; set; }
        public virtual ICollection<ContratoModel> Contratos { get; set; }
        public virtual ICollection<PagamentoModel> Pagamentos { get; set; }
        public ClinicaModel()
        {
            Usuarios = new HashSet<UsuarioModel>();
            Doutores = new HashSet<DoutorModel>();
            Convenios = new HashSet<ConvenioModel>();
            Pagamentos = new HashSet<PagamentoModel>();
            Contratos = new HashSet<ContratoModel>();

        }

        public override string ToString()
        {
            return Nome;
        }
    }

sql c# asp.net entity-framework linq

asked by anonymous 18.03.2016 / 04:22

1 answer

StringReplace on all occurrences A single form with multiple MDI forms or forms. Which option is most efficient in program performance?

score 2 · Accepted Answer

If I'm not mistaken, navigation properties, out of context IQueryable are loaded into memory using lazy load.

In your case, for being sum the sum, I would return several queries to the bank, because you probably are not using IQueryable.

I know that you created this property in order to return the value with a single query, but I believe that it is not possible because you are outside the Context. If you use the context directly, this is possible because you will work with iqueryable DbSet clinks. Test the following outside the scope of the property and see if the performance increases, so you will understand the problem:

 var context = new MyContext();// nao sei qual o nome do seu context de entitdades
 var clinicaId = 1;// ponha o Id de teste aqui

 var valorPrevistoClinca = context.ClinicaModel.Where(c=> c.Id = clinicaId).SelectMany(c=> c.Mensalidades.Where(m=> m.Pagamento != null)).Sum(m=> m.Valor) / 2;

Has it run fast?