Filtering results from foreign key sub-object

Posts   
 
    
renesisx
User
Posts: 44
Joined: 07-Aug-2006
# Posted on: 13-Feb-2007 01:14:48   

OK, so say I have 2 tables, User and ProductPurchased. There is a foreign key in ProductPurchased which links it to User.

So, in my code I can access the ProductPurchased collection of a User like so:

User.ProductPurchased

And I get the correct results.

Now I just want only the ProductPurchased for the User that cost more than "50".

So I create a predicate:

Dim Filter As New PredicateExpression(ProductPurchasedFields.Price > 50)

How do I get the results I'm expecting?

I tried:

User.ProductPurchased.GetMulti(Filter)

But the results were bizarre. I got a collection which contained ALL the ProductPurchased from all User, but the UserId column in the ProductPurchased was all set the same.

Is my question even sane?

Or should I just create a predicate which has the Price and UserId columns in it and forget trying to take the shortcut through the User object?

bclubb
User
Posts: 934
Joined: 12-Feb-2004
# Posted on: 13-Feb-2007 05:25:51   

You should be able to do something like this.


Dim user As New UserEntity(3)
Dim filter As New PredicateExpression(ProductPurchasedFields.Price > 50)
Dim productPurchased As ProductPurchasedCollection = customer.GetMultiProductPurchased(filter)

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 13-Feb-2007 10:41:19   

You should use the user.GetMultiProductPurchased() overload which accepts a filter. What you've done is simply fetching all products purchased matching the filter and loaded them into the collection, which then synced the userid of user with the fk userid in the productpurchased entities you added to the collection.

Frans Bouma | Lead developer LLBLGen Pro
renesisx
User
Posts: 44
Joined: 07-Aug-2006
# Posted on: 13-Feb-2007 16:12:22   

Thanks guys.

LLBLGen is a powerful tool, but sometimes there are so many possibilities it is hard to find exactly the right thing for the job.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 13-Feb-2007 16:48:18   

renesisx wrote:

Thanks guys.

LLBLGen is a powerful tool, but sometimes there are so many possibilities it is hard to find exactly the right thing for the job.

True, it can sometimes be a bit unclear where to start as there are so many possibilities. With lazy loading, things get done automatically and this can be unclear or blur the way you would think otherwise, like in this particular situation.

Frans Bouma | Lead developer LLBLGen Pro