Filter criteria with "many to many"

I have two entities (Product and Category) with many to many associations. How can I write queries over uow.Query<Product> to

1. get products included in the category with given Id
2. or more common: get products included in all categories with given Ids

All my attempts like:

int categoryId = 1;
var result = uow.Query<Product>().Where(p => p.Categories.Select(c => c.Id).Contains(categoryId))

result in exceptions like NotSupportedException (where is unsupported, select is unsupported, count is unsupported and so on).

Can anyone offer working lightspeed query?

Your best bet will be to use the underlying query syntax which will trigger an exists subquery to be appended to the criteria. Its a little simpler and clearer to understand what is being queried on.

e.g.

uow.Find<Product>(Entity.Attribute("Categories.Id") == categoryId);

or

int[] categoriesWithGivenIds = new int[3] { 1, 2, 3 };
uow.Find<Product>(Entity.Attribute("Categories.Id").In(categoriesWithGivenIds);

Otherwise you can do this in LINQ by using .Any() as your filter, e.g.

uow.Query<Product>().Where(p => p.Categories.Any(c => c.Id == categoryId));

or

int[] categoriesWithGivenIds = new int[3] { 1, 2, 3 };
uow.Query<Product>().Where(p => p.Categories.Any(c => categoriesWithGivenIds.Contains(c.Id));

Jeremy

Thanks, Jeremy!

"Any" over associated collection is really translated by LightSpeed!

My first case is solved. But your second example slightly differs from my second case. Your query returns products included in at least ONE given category. My need is to return products included in ALL categories. So I try the following (simply substitute "count" instead "any"):

int[] categoryIds = new int[3] { 1, 2, 3 };
uow.Query<Product>()
  .Where(p => p.Categories.Count(c => categoryIds.Contains(c.Id)) == categoryIds.Length);

Actually I started my experiments with this query. And it does not work - NotSupportedException "This overload of method 'Count' has no supported translation in LightSpeed".

Of course I can dynamically build expression and get something like:

int[] categoryIds = new int[3] { 1, 2, 3 };
uow.Query<Product>()
  .Where(p => p.Categories.Any(c => c.Id == categoryIds[0]
      && p => p.Categories.Any(c => c.Id == categoryIds[1]
      && p => p.Categories.Any(c => c.Id == categoryIds[2]);

But it would be nice if I get static expression. Is there any way to do this?

Thanks in advance, Jeremi!

PS: I just found a thread describing similar constraint http://www.mindscapehq.com/forums/thread/4372.