Filtering on a many to many relationship

We have the following tables in the Lightspeed Model:

Category
Product
CategoryProduct

The relationship is setup in the designer with CategoryProduct a "Through Entity" (ie: many to many).

The scenario that is causing trouble is as follows:

Return all Category and Category.Products where Product.Active is TRUE.

ie: I want a list of all categories and their associated products, but only those products thst have their active field set to true.

I have tried to eager load the aggregate to force only those products with active = true to be set for a given category, but have largely been unsuccessfull.

The linq statement that should return the data wanted is:

from c in unitOfWork.Categorys.WithAggregate("CategoriesWithProducts")
join  cp in unitOfWork.CategoryProducts on c.Id equals cp.CategoryId
join p in unitOfWork.Products on cp.ProductId equals p.Id
where p.Active == true
select c;

However, I get a "Object reference not set to an instance of an object" error.

Is it possible using lightspeed to perform a filter on the Category.Products?

Thanks for your assistance,

Andrew

You can actually just write the query as shown below as we support the use of through associations in LINQ queries.

from c in unitofWork.Categorys.WithAggregate("CategoriesWithProducts")

where c.Products.Any(p => p.IsActive == true)

select c;

I am also wondering what the cause of your NullReferenceException is, do you have a stack trace you can post for this?

Thanks!

Jeremy

Hi Jeremy,

The above linq statement doesn't quite return the results we are after. The result is returning the categories as expected, but is including the products which have Active set to false.

The linq statement produced the following sql:

SELECT

  Product.Id,

  Product.Active,

  Product.Name

FROM

  Product

WHERE

  EXISTS (

    SELECT

      ProductCategory.*

    FROM

      ProductCategory

    WHERE

      ProductCategory.ProductId = Product.Id AND

      EXISTS (

        SELECT

          Category.*

        FROM

          Category

        WHERE

          Category.Id = ProductCategory.CategoryId AND

          EXISTS (

            SELECT

              ProductCategory.*

            FROM

              ProductCategory

            INNER JOIN

              Product

            ON

              ProductCategory.ProductId = Product.Id

            WHERE

              ProductCategory.CategoryId = Category.Id AND

              Product.Active = True

          )

      )

  )

Which had the fowwlowing results:

Id Active Name

1 1 Fron for Dogs

2 0 Fron for Cats

3 1 Fron 200

Line Id 2 should not be displayed as Active = 0.

Thanks,

Andrew

Hi Andrew,

The Products collection is the collection of all Products associated with the Category -- it does not change its meaning depending on how you loaded the Category.

One solution is to write a wrapper that filters the Products:

public IEnumerable<Product> ActiveProducts {
  get { return this.Products.Where(p => p.IsActive); }
}

The downside of this is that it loads the full Products collection and then filters it.  This could be inefficient if you have many more inactive products than active ones.

Another possibility is to query on the ProductCategory objects and then assemble your custom collection that way, something like:

uow.ProductCategories
      .Where(pc => pc.Product.IsActive)
      .AsEnumerable()
      .GroupBy(pc => pc.Category)
      .Select(g => new { Category = g.Key, ActiveProducts = g.Select(pc => pc.Product) });

(not tested!)  For this to be efficient you would need to make the ProductCategory -> Category and -> Product association backreferences eager-load (or use a named aggregate if you don't want them to always eager load, but eager loading is usually a good idea for through associations anyway).