-1

How would i translate the following sql query to LINQ

select *
from groupprincipal.members 
where displayname not in (select adusername 
                          from employees)

I am wanting to list all members in an Active Directory group that are not in the employees table

I tried 

var notLoggedIn = from groupMembers in groupPrincipal.Members
                          from loggedIn in employeeRepository.All
                          where groupMembers.DisplayName != loggedIn.ActiveDirectoryName
                          select groupMembers;

But as you will know it did not work.

John Saunders
  • 160,644
  • 26
  • 247
  • 397
Tappies
  • 87
  • 1
  • 10
  • possible duplicate of [LINQ to SQL in and not in](http://stackoverflow.com/questions/3047657/linq-to-sql-in-and-not-in) – Dave Bish Jan 11 '13 at 14:08

2 Answers2

2
var notLoggedIn = from groupMembers in groupPrincipal.Members
                  where !employeeRepository.Select(p=> p.adUserName).Contains(groupMembers.DisplayName)
                  select groupMembers;
Max
  • 6,821
  • 3
  • 43
  • 59
  • thank you, your solution DID work. My bad, i should have been comparing groupMembers.Name instead of groupMembers.DisplayName, its Friday :( – Tappies Jan 11 '13 at 14:29
1

I believe this should do it:

var notLoggedIn = 
from groupMembers in groupPrincipal.Members
where !(from loggedIn in employeeRepository.All
        select loggedIn.ActiveDirectoryName).Contains(groupMembers.DisplayName)
select notLoggedIn;
Lister
  • 170
  • 7
  • sorry @Lister, did not work, select notLoggedIn throws a build error so does select loggedIn, select groupMembers returns all the groupMembers – Tappies Jan 11 '13 at 14:16
  • thank you, your solution DID work. My bad, i should have been comparing groupMembers.Name instead of groupMembers.DisplayName, its Friday :( However change Select notLoggedIn to Select groupMembers – Tappies Jan 11 '13 at 14:30