Is this possible in LINQ?

Question

Is it possible to return only those that match all list values ​​in LINQ ?

I have one klist table 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 containing the tid value:

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

now the tid list has values ​​1 and 2.

So my question is that from the klist table I get only those kid that contain all the values ​​of the tid list and are compared with the table identifier.

Expected Result After This

 kid - 001 kid - 004 

I tried the following query:

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

But this returns all the matching baby, if even one match, then they give this result, but I want only those that match all the identifiers of the table with the specified list. And the result of my query

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

Please suggest a LINQ request for this.

Answer 1

To achieve what you want, you first need to Group your Kid values:

 Klist.GroupBy(k => k.Kid) 

The result is a list of different Kid with corresponding Tableid s.

After that, we need to select the groups for which all of their Tableid match tid s. This can be achieved by removing identical items from both lists using Except . Then we check to see if there are any remaining elements (i.e. both sets are equal).

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

Finally, we get the Kid remaining groups, which are saved as Key in the group:

 .Select(g => g.Key); 

If we put 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); 

returns elements 001 and 004

Answer 2

You can group kList and 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 for instand SequenceEqual , the code is as follows

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

Answer 3

This works pretty nicely:

 var kList = new [] { new { Kid = "001", Tableid = 1 }, new { Kid = "001", Tableid = 2 }, new { Kid = "002", Tableid = 1 }, new { Kid = "003", Tableid = 3 }, new { Kid = "004", Tableid = 1 }, new { Kid = "004", Tableid = 2 }, }; var tid = new List<int>() { 1, 2, }; var lookup = kList.ToLookup(k => k.Tableid, k => k.Kid); 

This gives:

 1 => "001", "002", "004" 2 => "001", "004" 3 => "003" var result = tid.Select(t => lookup[t]).Aggregate((ts0, ts1) => ts0.Intersect(ts1)); 

tid.Select(...) creates { { "001", "002", "004" }, { "001", "004" } } , and Aggregate performs the intersection of the two.

Result:

 001 004 

Answer 4

Another way you can do this:

 var list = kList.GroupBy(x => x.Kid) .Select(group => new { Kid = group.FirstOrDefault().Kid, gtid = group.Select(groupItem => groupItem.Tableid).ToList() }) .Where(group => !group.gtid.Except(tid).Any() && !tid.Except(group.gtid).Any()) .Select(group => group.Kid); 

Basically, like other solutions, you group data in kList and create a new list using kid and the Tableid list. And then filter this new list by comparing the Tableid list with your tid (make sure you don't have a list item that is not in another, and vice versa). Then select kid values.

Answer 5

There are many answers that Except(Func<T, bool>).Any() or Except(Func<T, bool>).Count() == 0 , but both of these combinations look as if they should be All(Func<T, bool>) .

Based on your question, you say that you want only the kid value when it exists in the All lines for that kid value. This is how I do it.

 var lb = klist.GroupBy(lst => lst.Kid) .Where(t => tid.All(td => t.Select(x => x.TableId).Contains(td))) .Select(grp => grp.Key); 

This groups the table into groups containing kid . Then it filters out rows where All tid values ​​do not exist. Then he selects a key for those groups that are kid .