LINQ query - Multiple where with multiple sub queries based on int array

Question

I have a sql query that performs the type of select I'm after:

select * from Products p where p.ProductId in (
    select distinct ProductId from ProductFacets 
    where ProductId in (select ProductId from ProductFacets where FacetTypeId = 1)
    and ProductId in (select ProductId from ProductFacets where FacetTypeId = 4)
)

There can be multiple FacetTypeIds passed into this query.

This query is constructed in a method based on a parameter argument of type int[].

public IEnumerable<Product> GetProductsByFacetTypes(string productTypeSysName, int[] facetTypeIds)

I'm trying to work out how to achieve this in LINQ. So far I've come up with something like this:

var products = from p in sc.Products
where p.ProductType.SysName == productTypeSysName
where p.FacetTypes.Any(x => x.FacetTypeId == 1)
where p.FacetTypes.Any(x => x.FacetTypeId == 4)
select p;

This returns the correct result set.

However I'm not sure how I can build this query using the int[] facetTypeIds parameter.

EDIT:

ProductFacets contains the following data:

ProductId, FacetTypeId
1, 1
1, 2
2, 1
2, 3
3, 4
3, 5
4, 1
4, 2

As an example, I'd like to be able to select only Products which have a FacetTypeId of 1 AND 2. The result set should contain ProductIds 1 and 4

Answer 1

EDIT: Sorry I misread the question. I would suggest you use a PredicateBuilder and build up the Where clause dynamically if you need all of the types to be present. This would use extension methods directly.

var facetTypeIds = new [] { 1, 4, ... };
var predicate = PredicateBuilder.True<Product>();
foreach (int id in facetTypeIds)
{
    int facetId = id; // avoid capturing loop variable
    predicate = predicate.And( p => p.FacetTypes.Any( x => x.FacetTypeId == facetId );
}

var products = sc.Products
                 .Where( p => p.ProductType.SysName == productTypeSysName )
                 .Where( predicate );

Original (wrong, but left for context):

You want to use Contains. Note also you can use the logical and to replace multiple Where clauses with a single Where clause.

var facetTypeIds = new [] { 1, 4, ... };

var products = from p in sc.Products
where p.ProductType.SysName == productTypeSysName
      &&  p.FacetTypes.Any( x => facetTypeIds.Contains( x.FacetTypeId ) )
select p;

Answer 2

This is based on tvanfosson's code. I have doubts about the performance of this approach though.

var facetTypeIds = new [] { 1, 4, ... };

var products = from p in sc.Products
where p.ProductType.SysName == productTypeSysName
      && facetTypeIds.All(ft => p.FacetTypes.Any(x => x.FacetTypeId == ft))
select p;

Answer 3

A local collection may be transmitted to the database by calling Contains:

from ft in facetTypes
where facetTypeIds.Contains(ft.FacetTypeId)
select ft;

The local collection is translated into sql parameters. Sql Server has a limit of ~2100 parameters, so beware.

---

Products that have any of the facets

from p in sc.Products
where p.ProductType.SysName == productTypeSysName
where 
(
  from ft in p.FacetTypes
  where facetTypeIds.Contains(ft.FacetTypeId)
  select ft
).Any()
select p;

---

Products that have all facets.

int facetCount = facetTypeIds.Count();

from p in sc.Products
where p.ProductType.SysName == productTypeSysName
where 
(
  from ft in p.FacetTypes
  where facetTypeIds.Contains(ft.FacetTypeId)
  select ft.FacetTypeId
).Distinct().Count() == facetCount
select p;

Answer 4

I stumbled onto this post

The following code works correct but there are a few things in it that I don't quite fully understand:

var facetTypeIds = new[] { 1, 4 };

var products6 = from p in sc.Products
                where p.ProductType.SysName == productTypeSysName
                where p.FacetTypes.Any(x => facetTypeIds.Contains(x.FacetTypeId))
                select p;

foreach (var facetTypeId in facetTypeIds)
{
    // This assignment is needed?!?
    var tempFacetTypeId = facetTypeId; 

    products6 = from p in products6 where p.FacetTypes.Any(x => x.FacetTypeId == tempFacetTypeId) select p;
}

First, what are the performance implications of this type of query? Is this even a good way to do something?

Second, why is that assignement needed in the loop?