LINQ - joins a dynamic query

Question

Due to some business decisions, I need to change a little what I am doing. Give me.:)

I currently 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 using it directly, I now need to join two other tables in order to correctly apply the filter. I am trying to figure out how to correctly join a LINQ dynamic query. In TSQL, it will 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

. , . , lambdas , , linq. , linq , .

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 2

"" , . LINQPad. . LINQ. LINQ, .

Answer 3

Spolty Framework. Linq To SQL Entity Framework. / , , . Spolty Framework, :

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>();
}