LINQ to SQL - Struggling with query

Question

I have a table with server names and logins. I need to retrieve the logins that are common across a group of servers.

Given the following data:

ServerName    Login
-------------------------------
Server1       User1
Server2       User1
Server2       User2

I would pass in Server1,Server2 and get back only User1 as User2 is not associated Server1.

Can anyone tell me how this would be achieved in LINQ to SQL?

I have tried Contains but that returns me all users on any of the servers which is kind of the opposite to what I'm looking for.

EDIT: One of my colleagues managed to write the SQL version of what I'm after....

SELECT Login
  FROM ServerLogins
  WHERE ServerName IN ('Server1', 'Server2')
GROUP BY Login
HAVING count(Login) = 2

but neither of us know how to translate this into a LINQ query.

ADDITIONAL EDIT:

With Ryan's help and some Googling of the differences in LINQ between VB and C# I got the following to work.

Dim logins = From l In dc.ServerLogins _
             Where servers.Contains(l.ServerName) _
             Group l By l.Login Into Group _
             Where Group.Count() = servers.Count _
             Select Login

Thanks again to everyone for their help.

Nick

Answer 1

Personally I think this is a good place NOT to use Linq to SQL and instead to use either a sproc or a standard SQL query. I think that even if you were to come up with the correct query in Linq, it would not be very readable and/or efficient.

The SQL you would have would look something like this:

SELECT Login
FROM ServerLogins
WHERE ServerName IN ('Server1', 'Server2')
GROUP BY Login
HAVING COUNT(*) = 2

Note that the "2" in the last line should be replaced with the number of server names in the list above ("IN ('Server1', 'Server2')").

Answer 2

Here is what I came up with. You'll probably want to check and find out what SQL it actually generates if you're worried about it.

List<string> servers = new List<string>{"Server1", "Server2"};

var logins = from l in context.ServerLogins
             where servers.Contains(l.ServerName)
             group l by l.Login into g
             where g.Count() == servers.Count
             select g.Key;

Answer 3

As long as there's a reasonable practical limit on the # of servers being passed in I'd go with something like this:

public ICollection<Login> GetLoginsForServers(params string[] servers)
{
    if (servers == null || servers.Length == 0)
        return new List<Login>();

    var logins = db.Logins.Where(p => p.ServerName == servers[0]);
    for (int i=1; i<servers.Length; i++)
    {
        logins = logins.Intersect(db.Logins.Where(p => p.ServerName == servers[i]));
    }

    return logins.ToList();
}

Basically you're starting with all the logins associated with the first server then limiting it by those associated with each subsequent. Since the query doesn't get executed until ToList() you still only query the database once, and although the query itself is bound to be ugly, the hope is that the LINQ2SQL provider generates something that will result in an efficient query plan.