CODEWITHSUNDEEP
c#sqllinqtelerik
Darren Wood
Darren Wood

Reputation: 1528

C# LINQ Query with group by, having and where clause invalid

I have the following Linq queries which are getting the error below. My question is how can I use the T_EquipmentCompetency.Competency in a where clause but not have it in the group by or select list. I am fairly new to LINQ queries. Is there something that I am missing here?

using (EntitiesModel dbContext = new EntitiesModel())
        {
            var competencyForMachine = (from eq in dbContext.T_Equipmentcompetencies
                where eq.MachineId == machineId
                select eq.CompetencyId);

            var competencyForEmployees = (from sm in dbContext.T_OHS_Skillsmatrices
                join em in dbContext.T_Employees on sm.EmployeeID equals em.EmployeeID
                where competencyForMachine.Contains(sm.CompentencyID)
                group sm by new {sm.EmployeeID,em.FirstNameSTR,em.LastNameSTR} into g
                where g.Count() == competencyForMachine.Count()
                select new {g.Key.EmployeeID, g.Key.FirstNameSTR,g.Key.LastNameSTR});

            foreach(var employee in competencyForEmployees)
            {  
                RadMenuItem employeeItem = new RadMenuItem { Text= employee.FirstNameSTR + " " + employee.LastNameSTR, Value = employee.EmployeeID.ToString()};
                Item.Items.Add(employeeItem);
            }
        }

This is the error I am getting

Telerik.OpenAccess.RT.sql.SQLException: Column 
'dbo.T_EquipmentCompetency.CompetencyId' is invalid in the select list 
because it is not contained in either an aggregate function or the GROUP BY clause.

The sql I am trying to emulate works in SQL Server

declare @MachineId int = 1                      
select  sm.EmployeeID,em.FirstNameSTR,em.LastNameSTR 
from    T_OHS_SkillsMatrix sm
inner join T_Employees em on sm.EmployeeID = em.employeeId  
where sm.CompentencyID in  (    select  CompetencyID
                            from    T_EquipmentCompetency
                            where   MachineId = @machineId  
                        )
group by sm.EmployeeID,em.FirstNameSTR,em.LastNameSTR
having count(*) =  (select count(*) from T_EquipmentCompetency where MachineId = @MachineId)

Upvotes: 0

Views: 440

Answers (1)

RoMEoMusTDiE
RoMEoMusTDiE

Reputation: 4824

you got it mispelled

select eq.CompetencyId   --- different spelling

sm.CompentencyID

while in sql sql server

where sm.CompentencyID in  (    select  CompentencyID

Upvotes: 1

Related Questions