Nofuzy Nofuzy - 8 days ago 4
C# Question

Get list of items where their ID is equal to some Values - linq c#

Here is a query:

from order in db.tblCustomerBuys
where selectedProducts.Contains(order.ProductID)
select order.CustomerID;


selectedProducts
is a list containing some target products IDs, for example it is
{ 1, 2, 3}
.
The query above will return customerIDs where they have bought one of the
selectedProducts
. for example if someone has bought product 1 or 2, its ID will be in result.

But I need to collect CustomerIDs where they have bought all of the products. for example if someone has bought product 1 AND 2 AND 3 then it will be in result.

How to edit this query?

the tblCustomerBuys are like this:

CustomerID - ID of Customer
ProductID - the product which the customer has bought


something like this:

CustomerID ProdcutID
---------------------------
110 1
110 2
112 3
112 3
115 5


Updated:

due to answers I should do grouping, for some reason I should use this type of query:

var ID = from order in db.tblCustomerBuys
group order by order.CustomerID into g
where (selectedProducts.All(selProdID => g.Select(order => order.ProductID).Contains(selProdID)))
select g.Key;


but it will give this error:
Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator.

Answer

The updated query is the general LINQ solution of the issue.

But since your query provider does not support mixing the in memory sequences with database tables inside the query (other than Contains which is translated to SQL IN (value_list)), you need an alternative equivalent approach of All method, which could be to count the (distinct) matches and compare to the selected items count.

If the { CustomerID, ProductID } combination is unique in tblCustomerBuys, then the query could be as follows:

var selectedCount = selectedProducts.Distinct().Count();
var customerIDs =
    from order in db.tblCustomerBuys
    group order by order.CustomerID into customerOrders    
    where customerOrders.Where(order => selectedProducts.Contains(order.ProductID))
        .Count() == selectedCount
    select customerOrders.Key;

And if it's not unique, use the following criteria:

where customerOrders.Where(order => selectedProducts.Contains(order.ProductID))
    .Select(order => order.ProductID).Distinct().Count() == selectedCount