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