.net linq query joins

Question

I'm having some trouble with my "where" clause in a linq query. I have the following three tables.

Items {ItemId,Name}

Buckets {BucketId, Name}

ItemsBuckets {Id, BucketId, ItemId}

Items can exist in multiple buckets, which is why I have the ItemsBuckets Table. I want to find all the items that have a specific bucket with name containing bucketname.

System.Linq.IQueryable<Item> query = context.Items.Include("ItemsBuckets")
                     .Include("ItemsBuckets.Buckets");
query = query.Where(n => n.ItemsBuckets.Buckets.Name.Contains(bucketname));
ObservableCollection<Item> items = new ObservableCollection<Item>(query.ToList());

my where clause does not work. I get the following error:

Error   CS1061  'ICollection<ItemsBuckets>' does not contain a definition for 
'Buckets' and no extension method 'Buckets' accepting a first argument of
 type ICollection<ItemsBuckets> could be found (are you missing a using 
directive or an assembly reference?)    

I can not figure out a way to approach this. Any ideas?

Answer 1

Have you tried the following?

var query = context.Items
                   .Where(i => i.ItemsBuckets.Any(ib => ib.Bucket.Name.Contains(bucketname)));

var items = query.ToList();