LINQ - 动态查询中的联接操作

Question

因为一些业务决策，我需要改变一些我所做的事情。太好了，现在我已经有：

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

现在我需要通过其他两个表连接来正确应用过滤器，而不是直接进行操作。我正在尝试弄清楚如何在动态LINQ查询中正确地进行连接。在TSQL中，它可能类似于：

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)

- Keith Barrows

---

我还发现了这个链接，讲述了如何将SQL转换为LINQ。它可能会很有用：http://blogs.msdn.com/vbteam/archive/tags/Converting+SQL+to+LINQ/default.aspx。虽然是针对VB.NET的，但里面的内容对于C#仍然很有用。 - Erich Mirabal

Answer 1

哎呀。是的，这是一个复杂的要求。你知道，Lambda表达式是累加的，所以如果你使用连续的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");
}

- Jacob Proffitt

Answer 2

尝试使用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>();
}

- extragen

Answer 3

我没有一个好的“答案”给你，但是有一些旁白。看看LINQPad。你甚至可能在这个页面的右侧看到广告。它非常适合编写LINQ查询。它可能有助于编写和验证您编写的任何其他未来的LINQ查询。

- Erich Mirabal