LINQ to SQL 查询帮助-常见值

Question

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  

我应该如何编写一个Linq查询来仅获取常见的按键？

例如，查找用户帐户为(u1、u2、u3)且keyboardkey = kb1的常用按键。这将给出包含A、B的列表。

并查找用户帐户为(u1、u2、u3、u4)且keyboardkey = kb1的常用按键，然后它应该返回空。

谢谢。

- stackoverflowuser

Answer 1

类似以下的代码应该能够正常工作：

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

需要：

1. keys 必须是所涉及的SQL表
2. userKeyList 必须是用户账户的数组，即字符串数组 - 在您的示例中为 (u1, u2, u3)
3. someKeyboardkey 必须是某些键盘键值，如您的示例中的kb1
4. userCount 是userKeyList.Length中用户数量

- BrokenGlass

Answer 2

这对我有效：

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

我在 LINQPad 上测试了这个 MySQL 数据库，它运行良好。

- Enigmativity

Answer 3

没有一行LINQ代码，但这是我能想出的最好的:

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

更新：作为扩展：

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

使用方式如下：

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

- Rob