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.
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 Kid
s with their corresponding Tableid
s.
After that, we need to select the groups for which all their Tableid
s are the same as the tid
s. 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
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));
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With