CODEWITHSUNDEEP
linqdynamicjoin
Keith Barrows
Keith Barrows

Reputation: 25308

LINQ - Joins in a dynamic query

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)

Upvotes: 3

Views: 8860

Answers (3)

extragen
extragen

Reputation: 163

Try to use Spolty Framework. It helps to do dynamic query for Linq To SQL and Entity Framework. You can dynamically create left/inner join, add conditions, orderings and other things. If you use Spolty Framework then 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>();
}

Upvotes: 0

Jacob Proffitt
Jacob Proffitt

Reputation: 12768

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

Upvotes: 6

Erich Mirabal
Erich Mirabal

Reputation: 10038

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.

Upvotes: 0

Related Questions