LINQ - Joins in a dynamic query

Question

Because of some business decisions I need to change a bit of what I was doing. Yay me. :)

Currently, I have:

public IOrderedQueryable<ProductDetail> GetProductList(string productGroupName, string productTypeName, Dictionary<string,List<string>> filterDictionary)
{
    string whereClause = "ProductGroupName='" + productGroupName + "' AND ProductTypeName='" + productTypeName + "'";
    string comma = "";
    foreach (KeyValuePair<string, List<string>> myKVP in filterDictionary)
    {
        comma = "";
        if (myKVP.Value.Count > 0)
        {
            whereClause = String.Format("{0} AND FieldName = {1} AND FieldValue IN (", whereClause, myKVP.Key);
            foreach (string value in myKVP.Value)
            {
                whereClause = String.Format("{0}{1}'{2}'", whereClause, comma, value);
                comma = ",";
            }
            whereClause = String.Format("{0})", whereClause);
        }
    }

    var q = db.ProductDetail
              .Where (whereClause)
              .OrderBy ("ProductTypeName");
    return q;
}

Instead of foing this directly, I now need to join through 2 other tables to apply the filter correctly. I'm trying to figure out how to correctly join in a dynamic LINQ query. In TSQL it would be something like:

SELECT pd.* 
  FROM ProductDetail pd
 INNER JOIN ProductFilterAssignment pfa ON pd.ProductID = pfs.ProductID
 INNER JOIN ProductFilter pf ON pfs.FIlterID = pf.FIlterID
 WHERE pf.FieldName = 'var1' AND pf.FieldValue IN ('var1a','var1b','var1c',etc)
   AND pf.FieldName = 'var2' AND pf.FieldValue IN ('var2a','var2b','var2c',etc)

Answer 1

I don't have a good "answer" for you, but more of an aside. Check out LINQPad. You might even see an ad on the right-hand side of this page, too. It is very slick for writing LINQ queries. It might help in writing and validating this and any other future LINQ query you write.

Answer 2

Ouch. Yeah, that's a complicated requirement. You know, lambdas are cumulative, so you can do this much simpler if you use successive linq expressions. Note that subsequent linq expressions are using the prior expression result and the entire isn't actually executed until iterated.

public IOrderedQueryable<ProductDetail> GetProductList(string productGroupName, string productTypeName, Dictionary<string,List<string>> filterDictionary)
{
    // Initial select on productGroupName and productTypeName
    var products = from product in db.ProductDetail
                   where product.ProductGroupName == productGroupName && product.ProductTypeName == productTypeName
                   select product;

    // Now add each filter item present.
    foreach (KeyValuePair<string, List<string>> myKVP in filterDictionary)
    {
        products = from product in products
                   join pfa in db.ProductFilterAssignment on product.ProductID equals pfa.ProductID
                   join pf in db.Product on pfa.FilterID equals pf.FilterId
                   where pf.FieldName == myKVP.Key && myKVP.Value.Contains(pf.FieldValue)
                   select product;
    }

    return products.OrderBy ("ProductTypeName");
}

Answer 3

Try to use Spolty Framework. It helps to do dynamic query for Linq To SQL and Entity Framework. You can create dynamicly left/inner join, add conditions, orderings and anothere things. If you use Spolty Framework than your code will be look like below:

    public IQueryable<ProductDetail> GetProductList(string productGroupName, string productTypeName, Dictionary<string, List<string>> filterDictionary)
 {
  // create root node 
  JoinNode productDetailNode = new JoinNode(typeof(ProductDetail));
  productDetailNode.AddConditions(new Condition("ProductGroupName", productGroupName),
      new Condition("ProductTypeName", productTypeName));

  // if there are conditions than we create joins
  if (filterDictionary.Count > 0)
  {
   // create joinNode  
   // INNER JOIN ProductFilterAssignment pfa ON pd.ProductID = pfs.ProductID
   JoinNode productFilterAssignmentNode = new JoinNode(typeof(ProductFilterAssignment));
   productDetailNode.AddChildren(productFilterAssignmentNode);

   // create joinNode  
   // INNER JOIN ProductFilter pf ON pfs.FIlterID = pf.FIlterID
   JoinNode productFilterNode = new JoinNode(typeof(ProductFilter));
   productFilterNode.AddChildren(productFilterNode);

   foreach (KeyValuePair<string, List<string>> myKVP in filterDictionary)
   {
    // create condition pf.FieldName = {1} And AND pf.FieldValue IN ('var1a','var1b','var1c',etc)
    productFilterNode.AddConditions(new Condition("FieldName", myKVP.Key),
            OrCondition.Create("FieldValue", myKVP.Value.ToArray()));
   }
  }

  // create result query by JoinNode productDetailNode
  QueryDesigner queryDesigner = new QueryDesigner(db, productDetailNode).
           OrderBy(new Ordering("ProductTypeName"));

  return queryDesigner.Cast<ProductDetail>();
 }