O'Reilly Community

I've got a dynamic predicate I need to build based on quite a few options that could be passed in.

There are probably 7 tables that need to be joined together and I'm using LoadWith statements.

However, I need to be able to use conditions in several different tables.

For simplicity I'll just list the following tables.

CMS_Lusys_Site
CMS_CollectionItem
CMS_Br_CollectionItem_Category_Site
CMS_Luapp_CollectionCategory

CMS_CollectionItem has a field SiteId with a FK relationship to CMS_Lusys_Site

CMS_Br_CollectionItem_Category_Site is a bridge Table with FK's to CMS_CollectionItem & CMS_Luapp_CollectionCategory

I have a function called GetCollectionItems. This this funciton can take in a siteId, clientId which are both in CMS_Lusys_Site

So my predicate starts such as this.

var predicate = PredicateBuilder.True<CMS_Lusys_Site>();
predicate = predicate.And(o => o.SiteId == query.SiteId.Value);
predicate = predicate.And(o => o.ClientId == query.ClientId);

I also allow a collectionCategoryId to be passed into the function so with an if statement,
I can't figure out how to use a lamba expression based on the Table used in defining the predicate because some of the objects I have to traverse through are collections. Such as o => o.CMS_Luapp_CollectionCategories.

I've since tried to use the datacontext such as this to get the results I'm looking for but no luck.

if (query.CollectionCategoryId.HasValue)
{
predicate = predicate.And(o => db.CMS_Luapp_CollectionCategories.Any(ci => ci.CollectionCategoryId == query.CollectionCategoryId.Value));
}

I'm trying to keep this thread as short as possible. In psuedo code ideally i need to be able to write something such as.
predicate = predicate.And(o => o.CMS_Luapp_CollectionCategories.CMS_Br_CollectionItem_Category_Site.CMS_Luapp_C
ollectionCategory.CollectionCategoryId == query.CollectionCategoryId);

But again I can do it this way because there are objects such as CMS_Luapp_CollectionCategories in the hiearchy that are collections.

Any ideas of a syntax to be able to accompolish with predicatebuilder?

If you have association properties set up, you can query through these with PredicateBuilder:

var predicate = PredicateBuilder.False<Customer>();
predicate = predicate.Or (c => c.Purchases.Any (p => p.Price > 1000));
Customers.Where (predicate).Dump();

You can even reference the DataContext, if (the right) one is in scope:

var predicate = PredicateBuilder.False<Customer>();
predicate = predicate.Or (c => Purchases.Any
(p => p.Price > 1000 && p.CustomerID == c.ID));
Customers.Where (predicate).Dump();

Joe

Hello Joe,

Thank you for your response. I've seen this post and understand how to get it to work. The example your using though is two levels deep. If using Northwind is easier, I'll try this scenario.

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 continue with Order_Details
predicate = predicate.And(e => e.Orders.Order_Details.Product.ProductID == productId.Value;
}
}

To recap what you have posted would work fine if I was using a condition based on a field in Orders in this scenario but since I'm starting with Employee and need to set a criteria based on records with certain values in the Products table, this is what I'm having a time getting figured out.

Thanks.

Is what I'm trying to do not possible with Predicate Builder?

Hi there

Have you tried AssociateWith?


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


If you need a more complex expression, you should then be able to feed a PredicateBuilder into the AssociateWith expression.

Regards

Joe

Hello Joe,

Yes I've tried using AssociateWith. This is a fairly common scenario in the environments I work with but I had been using LLBL Gen Pro which handles these types of situations.

Is there no way to do this with an Expression Tree. Also, I started a thread on this at the MSDN forum to try and broaden the audience. I'll use that thread going forward since you also responded there to try and prevent duplicate efforts.

Thanks.

http://social.msdn.microsoft.com/Forums/en...3-325255d8e178/