SUM within SUM Entity Framework performance

Question

I made a query with Entity framework and I’m having problems with performance.

 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 as it is scanning record by record, searched several times and did not find a way to improve performance.

Classe Clinicamodel

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

Answer 1

If I’m not mistaken, navigation properties, outside the Iqueryable context are loaded in memory using Lazy load.

In your case, being sum the sum, would return several queries to the bank, as you are probably not using Iqueryable.

I know Voce created this property in order to return the value with a single query, but I think it is not possible because Voce is outside the Context. It is possible to use directly from the context, as this will be possible, because Ocean will work with Dbset iqueryable clincas. Test the following outside the scope of the property and see if performance increases, so Voce 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;

Swirled?