Complicated SQL query to Linq

Question

I need to convert the following complicated sql query to Linq in C#:

Select Empleador.NombreComercial as Empresa,
Vacante.Puesto as Vacante, 
Vacante.Actividades, 
COUNT(Vacante.CveVacante) as Visitas 
from Vacante 
LEFT JOIN Empleador on Empleador.CveEmpleador=Vacante.CveEmpleador  
LEFT JOIN VisitaVacante on Vacante.CveVacante = VisitaVacante.CveVacante
GROUP BY Empleador.NombreComercial,Vacante.Puesto, Vacante.Actividades, 
Vacante.CveVacante HAVING COUNT(*) > 1 ORDER BY Visitas DESC

For the moment I already have the following:

var Visitas = (from tvacante in db.VacanteT
                           join tEmpleador in db.EmpleadorT on tvacante.CveEmpleador equals tEmpleador.CveEmpleador
                           join tVisitaVacante in db.VisitaVacanteT on tvacante.CveVacante equals tVisitaVacante.CveVacante
                           select new
                           {
                               Empresa = tEmpleador.NombreComercial,
                               Vacante = tvacante.Puesto,
                               tvacante.Actividades,
                               Visitas = tvacante.CveVacante

                           }).GroupBy( );

How can I add the COUNT(Vacante.CveVacante) as Visitas and also the

GROUP BY Empleador.NombreComercial,Vacante.Puesto, Vacante.Actividades, 
Vacante.CveVacante HAVING COUNT(*) > 1 ORDER BY Visitas DESC

to my linq query? I can't find information about how to complete this. The tables are tvacante, templeador, and tvisitaVacante.

Is there a reason you need to convert it? Could you make a SP on the database and call that? — Sam Marion, Aug 30 '17 at 15:19
https://stackoverflow.com/questions/2078736/linq-with-group-by-having-count — C. Helling, Aug 30 '17 at 15:22
Yes I need to convert it as i need to fill a reportViewer by this query, before i used sqlCommand to fill the reportviewer but my boss requested my to use Linq, thats why — , Aug 30 '17 at 15:23

ocuenca · Answer 1 · 2017-08-30T15:38:49.700

Try this:

var Visitas =(from tvacante in db.VacanteT
              join tEmpleador in db.EmpleadorT on tvacante.CveEmpleador equals tEmpleador.CveEmpleador
              join tVisitaVacante in db.VisitaVacanteT on tvacante.CveVacante equals tVisitaVacante.CveVacante
              group new{tEmpleador.NombreComercial,tvacante.Puesto, tvacante.Actividades} by new {tEmpleador.NombreComercial,tvacante.Puesto, tvacante.Actividades} into g
              where g.Count()>1
              select new
              {
                 Empresa = g.Key.tEmpleador.NombreComercial,
                 Vacante =  g.Key.tvacante.Puesto,
                 Actividades= g.Key.tvacante.Actividades,
                 Visitas = g.Count()
              }).OrderByDescending(e=>e.Visitas);

If you want to do it using only linq query syntax and not merging both syntax then you could also do this:

var Visitas = from tvacante in db.VacanteT
              join tEmpleador in db.EmpleadorT on tvacante.CveEmpleador equals tEmpleador.CveEmpleador
              join tVisitaVacante in db.VisitaVacanteT on tvacante.CveVacante equals tVisitaVacante.CveVacante
              group new{tEmpleador.NombreComercial,tvacante.Puesto, tvacante.Actividades} by new {tEmpleador.NombreComercial,tvacante.Puesto, tvacante.Actividades} into g
              where g.Count()>1
              orderby g.Count() descending
              select new
              {
                 Empresa = g.Key.tEmpleador.NombreComercial,
                 Vacante =  g.Key.tvacante.Puesto,
                 Actividades= g.Key.tvacante.Actividades,
                 Visitas = g.Count()
              };

thank you very very much I apreciate your help, your query seems to be perfect but something is missing because the sqlcommand returns 34 rows and your linq query returns only 30 rows, anyways your help is great, thank you Ill check why is the linq query returning less results than the sqlcommand one — , Aug 30 '17 at 15:59
Oh is because the left joins in your sql query, I'm doing inner joins instead. Take a look this post: https://stackoverflow.com/questions/17142151/linq-to-sql-multiple-tables-left-outer-join — ocuenca, Aug 30 '17 at 16:02
@JasBalance, I saw now you are a new user of StackOverflow. Please consider check the checkmark below the votes if you consider I helped you to solve your issue. That's means the question was already answered, take a look this page: https://stackoverflow.com/help/someone-answers — ocuenca, Aug 30 '17 at 20:47

Complicated SQL query to Linq

1 Answers1