Many to many - get appropriate records from table - EF LINQ

Question

I have two tables with relationship many-to-many. Let's say A and B tables.
I also have List<List<int>> TagIdList with ids of B table's elements.
How can I find every elements from table A, who have all TagIdList[i] elements? I need just ids from table A, so it doesn't have to be all TASKS rows from table.

Example:

A: TASKS:
id: 1,2,3,4,5,6

B: TAGS:
id: 1,2,3,4

A-B links:
1-2; 1-3; 2-1; 2-2; 5-3; 5-4; 6-1; 6-6;

List<List<int>> TagIdList  //(ids from TAGS)   
TagIdList[0]= {2,3}   
TagIdList[1]= {1}   
TagIdList[2]= {2,6}

Result: (ids from TASKS)

i=0; -> 1  
i=1; -> 2,6  
i=2; -> null

I've tried:

List<int> tags = model.TagIdList[i].IdList; //I've got it from my View
List<TASKS> tasks = myEntity.TASKS.Where(t => t.TAGS == tags).ToList();

And I can't get tasks, because there was an error: Unable to create a constant value of type. Only primitive types are supported in this context.

Any ideas?

score 0 · Answer 1 · edited May 23 '17 at 12:05

0

Your problem is here: myEntity.TASKS.Where(t => t.TAGS == tags)

If i understand the question correct you need something like this:

A compare method for two lists of int (Taken from here)

public static bool ScrambledEquals<T>(IEnumerable<T> list1, IEnumerable<T> list2) {
  var cnt = new Dictionary<T, int>();
  foreach (T s in list1) {
    if (cnt.ContainsKey(s)) {
      cnt[s]++;
    } else {
      cnt.Add(s, 1);
    }
  }
  foreach (T s in list2) {
    if (cnt.ContainsKey(s)) {
      cnt[s]--;
    } else {
      return false;
    }
  }
  return cnt.Values.All(c => c == 0);
}

Than use this method inside linq expression:

myEntity.TASKS.AsEnumerable().Where(t => ScrambledEquals<int>(t.TAGS.Select(tag=>tag.id).ToList(),tags))

edited May 23 '17 at 12:05

Community

1
1

answered Sep 23 '14 at 09:04

Alex Art.

8,711
3
29
47

I got such error: _Can't convert from 'System.Collections.Generic.ICollection' to 'System.Collections.Generic.IEnumerable_ – Monic Sep 23 '14 at 09:30
Is TAGS a List of complex objects and not integers? – Alex Art. Sep 23 '14 at 09:37
I don't know why it doesn't work. It doesn't give me all tasks, just a few. I used: `myEntity.TASKS.AsEnumerable().Where(t => ScrambledEquals(t.TAGS.Select(tag=>tag.Id).ToList(), tags)).ToList();`, because I wanted to have a list at the end. – Monic Sep 24 '14 at 06:28

score 0 · Accepted Answer · answered Sep 29 '14 at 14:26

I found solution. It's maybe not ideal, but works.

List<int> tags = model.TagIdList[i].IdList;

List<List<int>> whole_list = new List<List<int>>();

foreach (var t in tags) //I'm looking for every tasks' ids in every given tag
{   
    var temp = myEntity.TAGS.Find(t).TASKS.Select(task => task.Id).ToList();
    whole_list.Add(temp);
}

//now I want first list from whole_list to compare with the other lists
List<int> collection = whole_list[0]; //collection it's tasks
whole_list.Remove(collection);

//I'm taking a part which is common to all lists from whole_list
foreach (List<int> k in whole_list)
{   
    var temp = collection.Intersect(k);
    collection = temp.ToList();
}

//the collection is now what I wanted for TagIdList[i] - every task which has every tags from TagIdList[i].IdList

Many to many - get appropriate records from table - EF LINQ

2 Answers2