Does permutation exist

Question

I am trying to figure out the proper query in linq to sql, but I just cant figure out how to do so. Lets say I have a table with the following (this table basically is 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 which has for employeeId 5 and 6. The query would return 2 only. I could use 2 where clause, but lets say I would want to input 3 employeeIds, my query would have to modified. If the passed permutation doesnt exist for one matched SupervisorId (ex: 5,6,8 in this case), the result would be null or empty.

The function would look like this:

int FindSuperVisorId(List<int> employeeIds);

I really dont know where to start in linq to sql for this type of scenario.

Thanks

Answer 1

So I'm pretty sure that this query should be converted property to LINQ to SQL, but I'm not completely sure.

So first we group by supervisor so that we have sequences of employees for that supervisor. Then we use Except with the employees you're interested in in both directions. If the the count of both of those Except calls is zero then the sets are exactly equal. There are more efficient ways of determining if two sets are equal in linq-to-objects, but I doubt they would be properly 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();

Answer 2

I had to model your table as a many-many relationship as follows:

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 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 a single SQL query. If you need to check there's only one matching supervisor it's probably best to call ToList() on the returned list of supervisors as in FindSupervisorId.

Trying to do the same thing with LINQ to SQL fails due to the calls to Except with the exception 'NotSupportedException: Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator.'