LINQ-加入动态查询 [英] LINQ - Joins in a dynamic query

问题描述

由于一些业务决策，我需要更改自己的工作方式.是的，我. :)

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

当前，我有:

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

我现在不需要通过直接攻击，而是需要通过其他2个表进行联接才能正确应用过滤器.我试图弄清楚如何正确地加入动态LINQ查询.在TSQL中，它将类似于:

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)

推荐答案

很好.是的，这是一个复杂的要求.您知道，lambda是累积的，因此，如果您使用连续的linq表达式，则可以做得更简单.请注意，后续的linq表达式使用的是先前的表达式结果，并且直到迭代之后，才会真正执行整个表达式.

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

这篇关于LINQ-加入动态查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！