Many-to-Many implementation in linq-to-sql

Question

Does anybody know an example of correct many-to-many implementation in Linq-to-Sql? Usually i use intermediate entity (e.g. CustomerProducts) in DataContext, and join it at every query.

The idea is to hide intermediate entity from object level by adding custom properties to partial classes - use collections like IEnumerabe Customer.Products and IEnumerable Product.Customers in each query, but i cannot write code which will be correctly translated to SQL in a subquery.

this should be used this or similar way:

int ProductID = 555;
Customers.Where(customer=>customer.Products.Any(product=>product.Id == productID));

all in SQL level, of course.

Or maybe I miss something and this is not a right way?

UPD. I'm looking for a way to get rid of intermediate entity by some SQL manipulation. Of course I can wrap existing code.

Answer 1

I've not tested this, but perhaps the problem is your looking to use IEnumerable, rather than IQueryable?

Suggest trying the following:

public partial class Customer
{

  public IQueryable<Order> Orders
  {
    get
    {
      return this.CustomerOrders.Select(co => co.Order);
    }
  }
}