Using Contains method within linq's Where clause

Question

I've got a problem with my query. I have two simple classes. Let's say

public class A{
  public List<B> MyCollection{get; set;}
}
public class B{
 public string Id;
}
//I want to do something like that
var myB = new B{Id="1"};
context.A.Where( x=> x.MyCollection.Contains(myB)).ToList();

How can I solve this? I know that I could do something like

context.A.ToList().Where...

but that's not the good idea, especially that I have few thousands records.

UPDATE! context is a EntityFramework context and context.A represents DbSet I'm still getting error "LINQ to Entities does not recognize the method 'Boolean Contains" also I can't use

context.A.ToList().Where(....

because I have thousands of records and it will be to inefficient

`context.A.Where(x=> x.MyCollection.Contains(myB))`, unless I'm misunderstanding the problem? — Matthew, May 14 '13 at 20:24
Well, the problem is I couldn't get to that list as 'LINQ to Entities does not recognize the method Boolean Contains...'. — 7h4ro3, May 14 '13 at 20:27
@Matthew: disabling ToList() won't help because I need to get this QUeryable element to list or something enumerable — 7h4ro3, May 14 '13 at 20:27
is `context.A` just a single instance of `A`, or is it an enumeration itself? — Matthew, May 14 '13 at 20:30
It doesn't work. I can't initialize it to IEnumerable so I can't get those elements. Your soulution is like writing select and not firing it — 7h4ro3, May 14 '13 at 20:38
put a `.ToList()` on the end, this will force EF to execute the statement it generates, otherwise it's deferred until something else enumerates it. — Matthew, May 14 '13 at 20:40
and that's what I did! context.A.Where( x=> x.MyCollection.Contains(myB).ToList(); Look at the code — 7h4ro3, May 14 '13 at 20:42
Your `ToList()` is in the wrong place, you need the `ToList()` to be called from the `Where` result, not from the `Contains` result. `context.A.Where(x=> x.MyCollection.Contains(myB)).ToList()` is what you want. — Matthew, May 14 '13 at 20:46
Yeah, and it still doesn't work. "LINQ to Entities does not recognize the method 'Boolean Contains" — 7h4ro3, May 14 '13 at 20:52
See this (in response to your error): http://stackoverflow.com/a/1069820/507793 — Matthew, May 14 '13 at 20:55

score 2 · Answer 1 · answered May 14 '13 at 20:48

This worked for me:

public class A
{
    public List<B> MyCollection{get; set;}
}

public class B
{
     public string Id;
}

void Main()
{

    // this is what you're searching for
    var myB = new B{Id="1"};


    // here are some A objects to put in your collection
    A a1 = new A();
    a1.MyCollection = new List<B>();
    A a2 = new A();
    a2.MyCollection = new List<B> { myB };
    A a3 = new A();
    a3.MyCollection = new List<B> { new B {Id="1"}};


    // here's a List that represents your context.A
    List<A> contextA = new List<A> {a1, a2, a3};


    // here's your actual search. results has a count of 1
    var results = contextA.Where( x=> x.MyCollection.Contains(myB));
    Console.WriteLine(results.Count()); 
}

Note that this only finds a2, because you literally put the object "myB" in there. It does not find a3, which is a new object created with the same id.

If you wanted to find both a2 and a3, you'd probably want to change the Where to something like this:

var results = contextA.Where( x=> x.MyCollection.Any(b => b.Id == myB.Id));

It work's, because you operate on List. Sorry, I didn't mention I work on DbSet still " LINQ to Entities does not recognize the method 'Boolean Contains " error — 7h4ro3, May 14 '13 at 20:53
@7h4ro3: The last line in Lily's post is the correct answer. Using `Contains` for your query is wrong and `Any` is the correct way. `Contains` performs a comparison by object reference identity but EF and the database manage identity by a key, hence you need to use a key property (`Id`) to compare. — Slauma, May 14 '13 at 22:46

score 1 · Answer 2 · answered May 14 '13 at 20:27

1

var ans = from b in context.A.MyCollection
          where b.Id == 1
          select b;

or

var ans = context.A.MyCollection.Where(b => b.Id == 1);

answered May 14 '13 at 20:27

caesay

16,932
15
95
160

Just to be clear. A doesn't have field "Id". I need to get from database context A elements which have in their field "MyCollection" specified element B – 7h4ro3 May 14 '13 at 20:30

Senad Meškin · Answer 3 · 2013-05-14T20:47:23.157

0

have you tried this

context.A.MyCollection.Where( x= > x.Id == myB.Id).ToList();

edited May 14 '13 at 20:47

answered May 14 '13 at 20:27

Senad Meškin

13,597
4
37
55

class A doesn't have field Id – 7h4ro3 May 14 '13 at 20:32
great, forgot to write MyCollection :) – Senad Meškin May 14 '13 at 20:47

Using Contains method within linq's Where clause

3 Answers3