C# Dynamic WHERE clause in a LINQ to SQL Query

Question

I would like to execute a LINQ query with a dynamic where clause depending on how many different options a user has entered for their criteria.

Is this possible?

I have posted code below of how I would like it to work.

Anyone got any suggestions?

P.S. I have attempted using the .Contains method (generating a WHERE IN on SQL, however the number of parameters was above the 2100 threshold and caused the error "LINQ The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request. The maximum is 2100".

private struct ProductStruct
{
    public long ProductID;
}

private struct FilterStruct
{
    public long ProductTypeFieldID;
    public long ValueNumber;
}

List<FilterStruct> filterList = new List<FilterStruct>();
filterList.Add(new FilterStruct { ProductTypeFieldID = 3, ValueNumber = 195 });
filterList.Add(new FilterStruct { ProductTypeFieldID = 8, ValueNumber = 55 });
 
List<ProductStruct> productList = new List<ProductStruct>();
 
productList = (from pfv in dC.ProductFieldValues
                           where
                                foreach (FilterStruct filter in filterList)
                                {
                                    pfv.ProductTypeFieldID == filter.ProductTypeFieldID
                                    && pfv.ValueNumber == filter.ValueNumber
                                }
                           select new ProductStruct
                           {
                               ProductID = pfv.ProductID
                           }).ToList();

EDIT

This looks as if it could be handy, but doesnt work with a dynamic where in?

private void Option2()
{
    try
    {
        LinqDataDataContext dataConnection = new LinqDataDataContext(ConnectionString);

        List<FilterStruct> filterList = new List<FilterStruct>();
            filterList.Add(new FilterStruct { ProductTypeFieldID = 3, ValueNumber = 195 });
            filterList.Add(new FilterStruct { ProductTypeFieldID = 8, ValueNumber = 55 });

        string whereClause = null;
        foreach (FilterStruct filter in filterList)
        {
            if (whereClause != null)
                whereClause += "AND ";

            whereClause += string.Format("ProductID IN (SELECT ProductID FROM ProductFieldValue WHERE ProductTypeFieldID = {0} AND ValueNumber = {1})", filter.ProductTypeFieldID, filter.ValueNumber);
        }

        List<ProductStruct> productList = new List<ProductStruct>();
            productList = (from pfv in dataConnection.ProductFieldValues.Where(whereClause)
                            select new ProductStruct
                                  {
                                  ProductID = pfv.ProductID
                                  }).ToList();
    } 
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
}

Answer 1

From the accepted answer here: How do I build up LINQ dynamically

This works well for .AsQueryable() classes, but .Enumerable() classes can be used if you use "as iQueryable()"