CODEWITHSUNDEEP
c#linq
sf.
sf.

Reputation: 25470

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

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

Upvotes: 1

Views: 4506

Answers (3)

Amy B
Amy B

Reputation: 110091

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;

Upvotes: 3

tvanfosson
tvanfosson

Reputation: 532435

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;

Upvotes: 1

recursive
recursive

Reputation: 86064

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;

Upvotes: 0

Related Questions