linq to sql query help - common values

userkey     keyboardkey     keypressed
----------------------------------------
u1          kb1             A
u1          kb1             B  
u1          kb2             C
u2          kb1             A
u2          kb1             B
u3          kb1             A
u3          kb1             B
u3          kb1             D  
u4          kb1      开发者_如何转开发       E  

How can i write a linq to sql query to get only the common keypressed.

For instance find the common keypressed where user account is in (u1, u2, u3) and keyboardkey = kb1. This will give the output as alist containing A,B.

And find the common keypressed where user account is in (u1, u2, u3, u4) and keyboardkey = kb1 then it should return nothing.

Thanks.

Something like this should work:

var commonKeysPressed = db.keys
                          .Where(k => userKeyList.Contains(k.userkey) 
                                 && k.keyboardkey == someKeyboardkey)
                          .GroupBy(k => k.keypressed)
                          .Where( g => g.Select( x=> x.userkey).Distinct().Count() == userCount)
                          .Select(g => g.Key)
                          .ToList();

This requires:

1. keys to be the SQL table in question
2. userKeyList to be an array of user accounts, i.e. a string array - in your example (u1, u2, u3)
3. someKeyboardkey to be some keyboard key value, in your example kb1.
4. userCount the number of users in the userKeyList array (userKeyList.Length)

This works for me:

var users = new [] { "u1", "u2", "u3", };

var common = users
    .GroupJoin(
        values.Where(v => v.keyboardkey == "kb1"),
        u => u,
        v => v.userkey,
        (u, vs) => vs.Select(v => v.keypressed))
    .Aggregate(
        (zs, z) => zs.Intersect(z));

I tested this in LINQPad on a MySQL database and it worked fine.

Not a single line of LINQ, but this is the best I could come up with:

private List<String> GetCommonKeys(List<a> input, string[] users, string[] keyboardkeys)
{
    var result = input.Where(c => users.Contains(c.userkey) && keyboardkeys.Contains(c.keyboardkey)).GroupBy (c => c.userkey);
    var returnList = result.First().Select(c => c.keypressed);
    foreach (var ls in result)
    {
        returnList = returnList.Intersect(ls.Select(t => t.keypressed));
    }
    return returnList.ToList();
}

struct a {
    public String userkey;
    public String keyboardkey;
    public String keypressed;
}

Edit: as an extension:

public static class myExtensions
{
    public static IEnumerable<TSelectType> SelectCommon<TSourceType, TSelectType>(this IEnumerable<IGrouping<TSelectType, TSourceType>> source, Func<TSourceType, TSelectType> action)
    {
        var firstResult = source.FirstOrDefault();
        if(firstResult == null)
            return new List<TSelectType>();

        var returnCollection = firstResult.Select(action);

        foreach(var ls in source)
            returnCollection = returnCollection.Intersect(ls.Select(action));

        return returnCollection;
    }
}

Used like this:

var res = lst.Where(c => new[] {"u1", "u2", "u3"}.Contains(c.userkey) && c.keyboardkey == "kb1").GroupBy (c => c.userkey).SelectCommon(c => c.keypressed);