I'm writing a LinQ query where one of the colums is a total (pscount) and it's being a little bit slow. I'm quite sure the culprit is the subselect to calculate pscount, and I'm also quite sure this query could be optimized, but I don't know exactly how.

Any help?

var q =
    from
        finca in this.db.finca
    join direccion in db.direccion 
        on new { id = finca.direccion_id } equals new { id = direccion.direccion_id }
    join ambito in db.ambito_geografico
        on new { id = direccion.ambito_id } equals new { id = (short?)ambito.ambito_id }
    join acometida in this.db.acometida
        on new { id = finca.acometida_defecto_id } equals new { id = acometida.acometida_id }
    join planificacion in this.db.planificacion
        on new { id = finca.planificacion_defecto_id } equals new { id = planificacion.planificacion_id }
    select
        new FincaViewModel
        {
            direccion = direccion.direccion_postal_1, //.direccion_postal_1,
            ambito = ambito.descripcion,
            acometida = acometida.nombre, //.nombre,
            pscount = (
                from p in this.db.puntosuministroview select p
            ).Where(psum => psum.concesion_id == finca.concesion_id
                && psum.planificacion_id == finca.planificacion_defecto_id
                && psum.acometida_id == finca.acometida_defecto_id).Count(),
            concesion_id = finca.concesion_id,
            planificacion = planificacion.nombre_planificacion,
            disponible_contratacion = finca.disponible_contratacion ? General.Si : General.No,
        };

CodePudding user response：

Try the following query:

var countsQuery = 
    from p in this.db.puntosuministroview 
    group p by new { p.concesion_id, p.planificacion_id, p.acometida_i } into g
    select new 
    {
        g.Key.concesion_id, 
        g.Key.planificacion_id, 
        g.Key.acometida_i,
        count = g.Count()
    };

var q =
    from
        finca in this.db.finca
    join direccion in db.direccion 
        on new { id = finca.direccion_id } equals new { id = direccion.direccion_id }
    join ambito in db.ambito_geografico
        on new { id = direccion.ambito_id } equals new { id = (short?)ambito.ambito_id }
    join acometida in this.db.acometida
        on new { id = finca.acometida_defecto_id } equals new { id = acometida.acometida_id }
    join planificacion in this.db.planificacion
        on new { id = finca.planificacion_defecto_id } equals new { id = planificacion.planificacion_id }
    join c in countsQuery
        on new { finca.concesion_id, finca.planificacion_id, finca.acometida_i } equals new { c.concesion_id, c.planificacion_id, c.acometida_i } into gj
    from c in gj.DefaultIfEmpty()
    select new FincaViewModel
    {
        direccion = direccion.direccion_postal_1, //.direccion_postal_1,
        ambito = ambito.descripcion,
        acometida = acometida.nombre, //.nombre,
        pscount = ((int?)c.count) ?? 0,
        concesion_id = finca.concesion_id,
        planificacion = planificacion.nombre_planificacion,
        disponible_contratacion = finca.disponible_contratacion ? General.Si : General.No,
    };