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);