CODEWITHSUNDEEP
c#.netlinq-to-sql
David
David

Reputation: 1871

Querying child tables with dynamic linq to sql?

I have a fairly complex query I need to write. I am hoping to do it using Linq to Sql if possible. The database is something like this:

Customers(First, Last, ID, Gender)
Orders (Date, Quantity, Weight, ItemName, Price)
Address (City, State, Zip)

The query would be to let a user search by any of these fields, and in the case of the number fields, to search <, =, or > as they desire.

Something like this would be sample query that I'm required to implement:

Query 1: Select customers where first name = 'John' and have at least one order with (Weight > 40 OR Quantity > 10 OR Price > 5) and zipcode = 12345.

Query 2: Select customers where first name = 'John' and have at least one order with Weight < 20 and ItemName = 'widget' and Quantity = 10) and zipcode = 12345.

I can get the basic part of searching for customers, but I am stuck on searching the Order table where the user can specify <>= in an OR manner.




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

Upvotes: 0

Views: 1147

Answers (1)

Markus Jarderot
Markus Jarderot

Reputation: 89231

You could use PredicateBuilder from LinqKit. It adds some new extension methods to predicate lambdas:

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

You might need to call .Expand() on the predicates before passing them as arguments, or .AsExpandable() on the queryable, if you are using the Entity Framework.

Upvotes: 1

Related Questions