Subquery is not supported on 'IsDeleted' of type 'Entities.Product'

Question

I'm using Linq to SQL and trying to filter data using DataOptions and AssociateWith. I have a table called Products that has a primary key called Id and a flag called IsDeleted with sql-datatype bit.

When I use the following code I get "Subquery is not supported on 'IsDeleted' of type 'Entities.Product'" exception on AssociateWith method.

var context = new DataContext();
DataLoadOptions options = new DataLoadOptions();
options.AssociateWith<Product>(p => !p.IsDeleted);
context.LoadOptions = options;

Any ideas?

Answer 1

I believe that you are only allowed two filter on a subquery of a one->many relationship and can only use a specific set of expressions, detailed here:
Where
OrderBy
ThenBy
OrderByDescending
ThenByDescending
Take

(more info here http://msdn.microsoft.com/en-us/library/bb534221.aspx)