Is this possible in LINQ?

Question

Is it possible to return only those who match all the list values in LINQ?

I have one table klist in which two columns are available:

Kid  -  Tableid  
001      1
001      2
002      1
003      3
004      1
004      2

and I have a list that contains tid value:

List<int> tid = new List<int>();
tid.Add(1);
tid.Add(2);

now the tid list have 1 and 2 value.

So my question is that from klist table I will only get those kid which contain all the tid list values and compare with table id.

The expected result after this is

kid - 001
kid - 004

I tried the query below:

var lb = Klist.Where(t => tid .Contains(t.Tableid)).Select(k => k.Kid).Distinct().ToList();

But that returns all the matching kid, if even single match then they give that result, but I want only those who match all the table id with tid list. And result of my query is

kid - 001
kid - 002
kid - 003
kid - 004

Please suggest a LINQ query for this.

Answer 1

In order to achieve what you want, you first need to Group your values by Kid:

Klist.GroupBy(k => k.Kid)

The result is a list of distinct Kids with their corresponding Tableids.

After that, we need to select the groups for which all their Tableids are the same as the tids. This can be achieved by removing the identical elements from both lists, with Except. We then check if there's no remaining elements (i.e. both sets are equal).

.Where(g => tid.Except(g.Select(s => s.Tableid)).Count() == 0)

Finally, get the Kid of the remaining groups, which is stored as the Key in the group:

.Select(g => g.Key);

If we assemble everything together, we get the following LINQ query:

var lb = Klist.GroupBy(k => k.Kid).Where(g => tid.Except(g.Select(s => s.Tableid)).Count() == 0).Select(g => g.Key);

returning the elements 001 and 004

Answer 2

You can group kList first,then use SequenceEqual with tid,here is a demo

List<KeyValuePair<string, int>> kList = new List<KeyValuePair<string, int>>()
{
    new KeyValuePair<string, int>("001",1),
    new KeyValuePair<string, int>("001",2),
    new KeyValuePair<string, int>("002",1),
    new KeyValuePair<string, int>("003",3),
    new KeyValuePair<string, int>("004",1),
    new KeyValuePair<string, int>("004",2)
};
List<int> tid = new List<int>() { 1, 2 };
var query = kList.GroupBy(i => i.Key)
    .Where(g => g.Select(j => j.Value).Distinct().OrderBy(i => i).SequenceEqual(tid))
    .SelectMany(g => g.Select(x => x.Key)).Distinct();
Console.WriteLine(string.Join(",", query));

and maybe SequenceEqual has some problem,if you want contains all tid,not Equal,then you can use Intersect to instand of SequenceEqual,the code is like this

List<KeyValuePair<string, int>> kList = new List<KeyValuePair<string, int>>()
{
    new KeyValuePair<string, int>("001",1),
    new KeyValuePair<string, int>("001",2),
    new KeyValuePair<string, int>("002",1),
    new KeyValuePair<string, int>("001",3),//special one
    new KeyValuePair<string, int>("003",3),
    new KeyValuePair<string, int>("004",1),
    new KeyValuePair<string, int>("004",2)
};
List<int> tid = new List<int>() { 1, 2 };
var query = kList.GroupBy(i => i.Key)
    .Where(g => g.Select(j => j.Value).Distinct().Intersect(tid).Count()==tid.Count)
    .SelectMany(g => g.Select(x => x.Key)).Distinct();
Console.WriteLine(string.Join(",", query));