none
Dynamic Expressions RRS feed

  • Question

  • First, I'm a big user of PredicateBuilder but I'm not having much luck getting my question answered at the LinqPad forum so trying here.

    I'll use the Northwind database to try and ask my question.

    Say I have an endpoint called GetEmployeeData. In my MiddleTier, I have an employee data object that consists of data from the following tables. Employees, Orders, OrderDetails, Products.

    In this end point one of the dynamic search values I want to allow a consumer to pass in is productId and also allow an EmployeeId to be passed in as well.

    My DataContext and my Predicate will start with the Employees table.

    public List<EmployeeData> GetEmployeeData(int employeeId, int? productId)
    {
    using (DAL.NWDBDataContext db = new DAL.NWDBDataContext()
    {

    DataLoadOptions options = new DataLoadOptions();
    options.LoadWith<Employee>(e => e.Order);
    options.LoadWith<Order>(o => o.Order_Details);
    options.LoadWith<DAL.Order_Detail>(od => od.Product);

    db.LoadOptions = options;

    var predicate = PredicateBuilder.True<Employee>();

    // Set EmployeeId condition
    predicate = predicate.And(e => e.EmployeeID == employeeId);

    // Here's the part I'm struggling to get to work when I need to go several "levels" deep in associations.
    if(productId.HasValue)
    {
    // This is psuedo code and obviously doesn't work because Orders, OrderDetails are collections
    // If you use e.Orders.Any then you can't continue with Order_Details
    predicate = predicate.And(e => e.Orders.Order_Details.Product.ProductID == productId.Value;
    }
    }

    Even if I can't use PredicateBuilder, I'd be ok to figure out how to construct my own Expressions just to accomplish what I need to do here. 

    Any help would be greatly appreciated.

    Thanks.

    kfrost
    Sunday, May 31, 2009 9:14 PM

Answers

  • Looks like nested any's may at least work.  I haven't verfied the T-SQL this generates but I get no exceptions and getting back what I'm supposed to.

    I'm working in a different database but I think something along these lines would work with the Northwind database.

    predicate = predicate.And(e => e.Orders.Any(o => o.Order_Details.Any(od => od.Product.ProductID == productId.Value)));
    kfrost
    • Marked as answer by kfrost Monday, June 15, 2009 3:27 PM
    Monday, June 15, 2009 3:27 PM

All replies

  • Hi there

    Have you tried AssociateWith?


        if (productId.Value)
            options.AssociateWith<Order_Details> (od => od.ProductID == productId.Value);


    Joe
    Write LINQ queries interactively - www.linqpad.net
    Monday, June 1, 2009 1:17 AM
    Answerer
  • yes I've tried associateWith as you have it.

    I get the following exception when I try to add it.

    "Subquery is not supported on 'productId' of type 'DynamicLinq.Program+<>c__DisplayClass0'."}
    kfrost
    Monday, June 1, 2009 2:26 AM
  • Sorry, that should be:


         if (productId.HasValue)
            options.AssociateWith<Orders> (o => o.Order_Details.Where (od => od.ProductID == productId.Value));


    Joe
    Write LINQ queries interactively - www.linqpad.net
    Monday, June 1, 2009 4:15 AM
    Answerer
  • Hello Joe,

    No that doesn't work either.  Plus keep in mind this Northwind example is a scaled version of what I need to accomplish to make it simpler to post on the forum.

    I really need to find some way to figure out a syntax to use an expression tree for the syntax I have above.

    predicate = predicate.And(e => e.Orders.Order_Details.Product.ProductID == productId.Value);

    In the scenario here, OrderDetails and relationship wise is just one level under Orders.  But in reality sometimes I'm working in retrievals to where the equivalent of OrderDetails may be 4 or 5 levels deep in the relationship.  Thus I'm looking for something along the lines of this type of syntax.

    This just equates to in SQL multiple join/where clauses. 

    I've been trying find a syntax (inner and outer here are just made up for demonstration)

    predicate = predicate.And(e => e.Orders.Order_Details.Join(inner, outer).Product.ProductID == productId.Value);
    kfrost
    Monday, June 1, 2009 11:44 AM
  • What happens when you call AssociateWith?

    That code should work no matter how far Order_Details is nested. You can also call AssociateWith more than once, to load it with several filters.


    One further thought: given you said that you're doing n-tier development, are you using separate DTOs? If so, there's also another approach:


    var query =
       from c in Customers
       where ...
       select new DTOCustomer
       {
          FirstName = c.FirstName,
          LastName = c.LastName,
          Orders = new DTOOrderList
          (
             from o in c.Orders
             where ...
             select new DTOOrder
             {
                 Description = o.Description,
                 Price = o.Price,
                 OrderDetails = new DTOOrderDetailList
                 (
                    from od in o.OrderDetails
                    where ...
                    select new DTOOrderDetail
                    {
                       ItemDescription = od.ItemDescription,
                       ...
                    }
                 )
             }
          )
       }

    Joe

    Write LINQ queries interactively - www.linqpad.net
    Tuesday, June 2, 2009 2:04 AM
    Answerer
  • Thanks Joe for your help.  Actually the AssociateWith in that fashion actually works but isn't getting me past the scenarios I've been trying to describe.  I can write these queries out manually but I'm trying to figure out how to do this with linq before throwing in the towel and switching back to another ORM that handles these scenarios.

    To extend off the Northwind example, let's say have can be of multiple types and multiple categories so there are the following tables.

    I'll have two bridge tables and then the ProductType & ProductCategories table.

    Something like this:

    Product
     ProductId

    br_Product<table>             <----  br_ProductCategory<table>
     brProdPkey                                 brPCPkeyI
     ProductId                                    brProdPkey
     prodTypePkId                              prodCatPkId

    ProductType<table>                    ProductCategory<table>
     ProdTypeId                                  ProdCatPkId


    Hopefully these tables make sense but the point I'm trying to get at is I would do something like

    options.AssociateWith<

     

    Order>(o => o.Order_Details.Product.Br_Products.ProdTypeId == 3;

    In this scenario I've been trying to ask is how you'd do something such as this to get past a collection such as Order_Details.

    Order_Details is a collection where only something like a Where is available but nothing I can figure to get you past it to say the Product table in this case.

    For clarity again, I'm trying to figure out how to dynamically build a predicate in these scenarios.  An ORM such as LLBL Gen Pro, this is fairly easy to do and need to figure out a way to do it in Linq in order not to switch back to LLBL or equivalent.

    Thanks
    • Edited by kfrost Thursday, June 4, 2009 2:37 AM
    Thursday, June 4, 2009 2:36 AM
  • Looks like nested any's may at least work.  I haven't verfied the T-SQL this generates but I get no exceptions and getting back what I'm supposed to.

    I'm working in a different database but I think something along these lines would work with the Northwind database.

    predicate = predicate.And(e => e.Orders.Any(o => o.Order_Details.Any(od => od.Product.ProductID == productId.Value)));
    kfrost
    • Marked as answer by kfrost Monday, June 15, 2009 3:27 PM
    Monday, June 15, 2009 3:27 PM