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

Your query doesn't match your expectations. You are using 1 and 4 for your facet ids but want facet ids 1 and 2. Perhaps the error using my answer is related to this? — tvanfosson
– tvanfosson, Commented Jul 27, 2010 at 15:49
That is just an example to try and explain the result set. The values passed in are specific for that example. Sorry if that is confusing. — sf.
– sf., Commented Jul 28, 2010 at 9:10

Amy B · Accepted Answer · 2010-07-27 15:53:15Z

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;

edited Jul 27, 2010 at 15:53

answered Jul 27, 2010 at 14:32

Amy B

110k21 gold badges140 silver badges191 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

sf. Over a year ago

Thanks for this. I've just tested out and it's pulling the wrong result set. I'm after products that have all the FacetTypeIds. eg: products that have both FacetTypeIds and 4 but not products which only have FacetTypeId 1

Dylan - INNO Software Over a year ago

@david-b Regarding the Sql Server limit --- that's a really good point --- I guess there wasn't a way for them (Microsoft) to have Linq pass the entire collection in the query with some way of letting the server itself do the 'contains' filtering.

Amy B Over a year ago

@Dylan-INNOSoftware you may be interested in these articles about passing arrays to Sql Server (sommarskog.se/arrays-in-sql.html)

tvanfosson · Accepted Answer · 2010-07-27 13:47:37Z

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;

edited Jul 27, 2010 at 13:47

answered Jul 27, 2010 at 12:16

tvanfosson

534k102 gold badges703 silver badges801 bronze badges

5 Comments

sf. Over a year ago

Unfortunately this doesn't produce the results I'm after. This shows all products with either FacetTypeId 1 or 4. The Linq query I have shows only products with BOTH FacetTypeId 1 and 4

tvanfosson Over a year ago

@sf - I misread your question (actually, just skimmed it -- sorry). I've updated my answer based on your actual requirements.

cjk Over a year ago

Post edit answer looks good. A bit more intensive than a straightforward call, but the only viable option if the dataset being queries is large.

sf. Over a year ago

Cheers for the update. I misread the last update and only just saw the Predicate style code. The query runs but I can't loop thru the products var. I'm getting an error: "The LINQ expression node type 'Invoke' is not supported in LINQ to Entities."

tvanfosson Over a year ago

@sf - didn't know you were working with LINQ to Entities. The PredicateBuilder must rely on some LINQ to SQL implementation details. I suppose that it could be modified to work with LINQ to Entities, but I haven't used it with them. It is essentially doing what the code in your answer is doing, but building up just the where clause then applying it rather than simply further filtering it. It's more powerful than what I've shown since you can create arbitrarily complex conditions. If it doesn't work with Entities, though, that doesn't help much.

recursive · Accepted Answer · 2010-07-27 12:23:37Z

0

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;

answered Jul 27, 2010 at 12:23

recursive

86.5k36 gold badges155 silver badges246 bronze badges

4 Comments

Dave Markle Over a year ago

Yeah, anything where you cross between the database and IEnumerables in memory tends to be be "brute forced" by the SQL generator. It's good for small "n", though! :)

sf. Over a year ago

hmm, this is proving to be trickier than I thought. The data set is going to be fairly large and hit quite often too.

Ronald Wildenberg Over a year ago

This won't work. You'll get an exception "Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator". Which means that you can only pass the facetTypeIds int array if you use it in a Contains expression.

sf. Over a year ago

This query is still the one that pulls the correct result set. I am however seeing an issue when trying to apply a .Skip to the end result.

Collectives™ on Stack Overflow

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

3 Answers 3

3 Comments

5 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

5 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related