使用动态链接查询子表

本文关键字：查询 链接 动态 | 更新日期: 2023-09-27 18:12:54

我需要编写一个相当复杂的查询。如果可能的话，我希望使用Linq到Sql。数据库是这样的:

客户(First, Last, ID, Gender)
订单(日期、数量、重量、ItemName、价格)
地址(城市，州，邮政编码)

查询将允许用户搜索这些字段中的任何一个，对于数字字段，可以根据需要搜索<， =或>。

像这样的东西将是我需要实现的示例查询:

查询1:选择名字= 'John'且至少有一个订单(重量> 40或数量> 10或价格> 5)且邮政编码= 12345的客户。

查询2:选择名字= 'John'并且至少有一个Weight &lt订单的客户;20和ItemName = 'widget'和Quantity = 10)和zipcode = 12345。

我可以获得搜索客户的基本部分，但我被困在搜索Order表上，用户可以以OR的方式指定<>=。



query = Context.Customers.AsQueryable();
if (searchingFirstName) query = query.Where(cust => cust.First == firstName);
if (searchingLastName) query = query.Where(cust => cust.Last == lastName);
if (searchingZip) query = query.Where(cust => cust.Address.Zip == zip);
// using dynamic Linq
if (searchingGender) query = query.Where("Gender == @0", gender);
// how do I search the Orders?  The dynamic linq functions appear
// to only work on the top level table


    

  
使用动态链接查询子表

您可以使用LinqKit中的PredicateBuilder。它为谓词lambda添加了一些新的扩展方法:

var predicate = PredicateBuilder.True<Customer>();
if (searchingFirstName)
{
    predicate = predicate.And(cust => cust.First == firstName);
}
if (searchingOrders)
{
    // Some code to unify the .And() and .Or() cases
    Expression<Func<Order, bool>> subpredicate;
    Func<Expression<Func<Order, bool>>, Expression<Func<Order, bool>>, Expression<Func<Order, bool>>> joiner;
    if (orderMethodAny)
    {
        subpredicate = PredicateBuilder.True<Order>();
        joiner = PredicateBuilder.And;
    }
    else
    {
        subpredicate = PredicateBuilder.False<Order>();
        joiner = PredicateBuilder.Or;
    }
    if (searchingOrderDate)
    {
        // ...
    }
    if (searchingOrderWeight)
    {
        switch (orderOp)
        {
            case Op.Less:
                subpredicate = joiner(subpredicate, ord => ord.Weight < orderWeight);
                break;
            case Op.LessEqual:
                subpredicate = joiner(subpredicate, ord => ord.Weight <= orderWeight);
                break;
            case Op.Equal:
                subpredicate = joiner(subpredicate, ord => ord.Weight == orderWeight);
                break;
            case Op.GreaterEqual:
                subpredicate = joiner(subpredicate, ord => ord.Weight >= orderWeight);
                break;
            case Op.Greater:
                subpredicate = joiner(subpredicate, ord => ord.Weight > orderWeight);
                break;
            case Op.NotEqual:
                subpredicate = joiner(subpredicate, ord => ord.Weight != orderWeight);
                break;
        }
    }
    if (searchingOrderQuantity)
    {
       // ... 
    }
    if (searchingOrderItemName)
    {
        // ...
    }
    if (searchingOrderPrice)
    {
        // ...
    }
    predicate = predicate.And(cust => cust.Orders.Any(subpredicate));
}
if (searchingZipCode)
{
    predicate = predicate.And(cust => cust.ZipCode == zipCode);
}
var query = Context.Customers.Where(predicate);

在将谓词作为参数传递之前，您可能需要在谓词上调用.Expand()，或者在查询项上调用.AsExpandable()，如果您正在使用实体框架。

相关文章：