Is there a permutation
I'm trying to figure out the correct query in linq for sql, but I just can't figure out how to do it. Let's say I have a table with the following (this table basically has a one to many relationship)
Id (PK) | SupervisorId | EmployeeId
1 1 5
2 1 6
3 1 7
4 2 5
5 2 6
6 3 7
7 4 7
8 4 8
I want my linq to sql query to find the supervisorId file which has 5 and 6 for employeeId. The query will only return 2. I could use a 2 where clause, but let's say I would like to enter 3 employeeIds, my query would have to change. If the passed permutation does not exist for one matching SupervisorId (ex: 5,6,8 in this case), the result will be null or blank.
The function will look like this:
int FindSuperVisorId(List<int> employeeIds);
I really don't know where to start in linq to sql for this type of scenario.
thank
source to share
So, I'm sure this query should be converted to LINQ to SQL, but I'm not sure.
So first we group a supervisor so that we have employee sequences for that supervisor. We then use it Except
with employees you are interested in both ways. If the count of both of these calls Except
is zero, then the settings are exactly the same. There are better ways to determine if two sets are equal in linq-to-objects, but I doubt they would be correctly converted to SQL code.
var supervisorId = table.GroupBy(item => item.SupervisorId)
.Select(group => new
{
additionalItems = group.Select(item => item.EmployeeId).Except(employees),
missingItems = employees.Except(group.Select(item => item.EmployeeId)),
group = group
})
.Where(queries => queries.additionalItems.Count() == 0
&& queries.missingItems.Count() == 0)
.Select(queries => queries.group.Key)//gets the supervisorID
.FirstOrDefault();
source to share
I had to model the table as many, many relationships like this:
CREATE TABLE [dbo].[Employee](
[Name] [nvarchar](50) NOT NULL,
[Id] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
CREATE TABLE [dbo].[SupervisorEmployees](
[SupervisorId] [int] NOT NULL,
[EmployeeId] [int] NOT NULL,
CONSTRAINT [PK_SupervisorEmployees] PRIMARY KEY CLUSTERED
(
[SupervisorId] ASC,
[EmployeeId] ASC
)
GO
ALTER TABLE [dbo].[SupervisorEmployees] WITH CHECK ADD CONSTRAINT [FK_SupervisorEmployees_Employee] FOREIGN KEY([SupervisorId])
REFERENCES [dbo].[Employee] ([Id])
GO
ALTER TABLE [dbo].[SupervisorEmployees] CHECK CONSTRAINT [FK_SupervisorEmployees_Employee]
GO
ALTER TABLE [dbo].[SupervisorEmployees] WITH CHECK ADD CONSTRAINT [FK_SupervisorEmployees_Employee1] FOREIGN KEY([EmployeeId])
REFERENCES [dbo].[Employee] ([Id])
GO
ALTER TABLE [dbo].[SupervisorEmployees] CHECK CONSTRAINT [FK_SupervisorEmployees_Employee1]
GO
Then, using the Entity Framework database first (not Linq to SQL unfortunately) the following LINQPad code works fine:
void Main()
{
FindSupervisorIds( new List<int>{5,6} ).Dump();
}
IEnumerable<int> FindSupervisorIds(List<int> employeeIds)
{
// two Excepts to do 'sequence equals'
var supervisors = Employees.Where (e =>
!e.Employees.Select (em => em.Id).Except(employeeIds).Any()
&& !employeeIds.Except(e.Employees.Select (em => em.Id)).Any()
);
return supervisors.Select (s => s.Id).Distinct();
}
int? FindSupervisorId(List<int> employeeIds)
{
var supervisors = FindSupervisorIds(employeeIds).ToList();
if(supervisors.Count == 1)
{
return supervisors.First ();
}
return null;
}
FindSupervisorIds generates one SQL query. If you only need to check for one matching supervisor, it is best to call ToList () on the returned supervisor list, as in FindSupervisorId.
An attempt to do the same with LINQ to SQL failed due to Except calls with the exception 'NotSupportedException: Local sequence cannot be used in LINQ to SQL implementation of query statements other than the Contains operator.
source to share
one possibility:
public int FindSuperVisorId(IEnumerable<Employee> employes)
{
var distinctSupervisors = employes.Select(e => e.SuperVisor).Distinct();
var superVisor = distinctSupervisors.Where(supervisor => employes.All(employee => employee.SuperVisor.Equals(supervisor))).FirstOrDefault();
return superVisor;
}
and in case you want all matches with the same supervisors:
public IEnumerable<int> FindSuperVisorId(IEnumerable<Employee> employes)
{
var distinctSupervisors = employes.Select(e => e.SuperVisor).Distinct();
var equalSupervisors = distinctSupervisors .Where(supervisor => employes.All(employee => employee.SuperVisor.Equals(supervisor)));
return equalSupervisors;
}
or directly:
public IEnumerable<int> FindSuperVisorId(IEnumerable<Employee> employes)
{
return employes.Select(e => e.SuperVisor).Distinct()
.Where(supervisor => employes.All(employee => employee.SuperVisor.Equals(supervisor)));
}
source to share