Filtering entities based on entity property value

[Anchor]

I am trying to filter certain entities according to the value of a certain property when performing a query. However, I want to filter the entity based on a child tables property value.

For instance, let's say I have 3 entities where entity A has a 1-* association to entity B which has a 1-* association with entity C. What I want to do is filter out all entity Bs that do not have any entity Cs with a certain ID.

Also, I would like to perform this filtering in one query while I get all three entities. (I was looking at using the Attach and CreateSourceQuery methods but I cannot seem to get it to work.) Is this possible or do I have to break it up into different queries?

Thanks.

[browniepoints]

When you say filter out do you mean exclude the entity Cs without a certain ID or do you mean you want to include those. The first is easy...the second is, easy too (once you understand it it's tough at first to wrap your head around it).

Here is your first scenario

Code: Select all

var query = from a in context.A
let bs= a.B
from b in bs
let cs = b.C
from c in cs
where c.ID==123
select a;

Query will contain a collection of A with B and C populated.

There might be a more efficient query than that but that one works too.