Hi, I might be missing something obvious here, but I can't get this to work:

(the names of tables etc have been changed to protect the brain from the lunacy of third-party financial database design)

I have a many to many join from Customer to Address via AddressTS (a timestamped join table), where AddressTS contains a FROM_DATE and TO_DATE field. I'm only interested in Address records where TO_DATE > Today in the join table. How do I add a filter into the automatically executed lazy loading of the AddressTS records (where CustomerID = xxx)? Adding this as a CustomFilter to a RelationCollection only seems to add it at the initial query level (i.e. populating the Customer collection), so how do I (or can I) add a filter that will be used by the lazy queries? Modifying the database or adding views is not an option as this is a read-only database.

Cheers, Jason

Hi Jason,

AFAIK there isn't a direct way in the framework to do that. However you can achieve such thing in various ways:

• Do a CustomerEntity method that ask for the parameter and returns the collection.
• Write a custom property that make the lazy loading based on the filter.
• Overriding the actual m:n property and return the collection in the way you want.

For example, here I wrote a new property that do that in a partial class:

namespace <yourRootNamespace>.EntityClasses
{
    public partial class CustomerEntity  
    {
        /// <summary>
        /// Get the related M:N collection based on a custom filter
        /// </summary>
        public AddressCollection AddressCollectionViaAddressTsFilteredOnDate
        {
            get
            {
                // check if the collection is already fetched
                if (!this.AlreadyFetchedAddressCollectionViaAddressTs)
                {
                    // set the filter
                    IPredicateExpression filter =  new PredicateExpression(
                         AddressTsFields.CustomerId == this.CustomerId 
                         & AddressTsFields.ToDate > DateTime.Now);
                    IRelationCollection relations = new RelationCollection(
                         AddressEntity.Relations.AddressTsEntityUsingAddressId);
                    
                    // fetch and return the collection
                    _addressCollectionViaAddressTs.GetMulti(filter, relations);
                    AlreadyFetchedAddressCollectionViaAddressTs = true;
                    return _addressCollectionViaAddressTs;
                }
                else
                {
                    return this.AddressCollectionViaAddressTs;
                }
            }
        }
    
    }
}

The code could be more simple/complicated depending on what you want to return.

Thanks David,

Having become used to the "You just do it this way" flexibility of LLBLGen I was expecting it to be an oversight of mine

It appears that the method you suggest would indeed return the data I need, but to have this value returned automatically during (for example when using databinding) I would have to hack the generated code to call this function instead of the built-in one, as the default routine is called automatically by the Lazy-load routines.

I did consider using Prefetch to grab the associated records, but with 32,000 People records that would add quite an overhead, particularly if the user only selects one or two Customers. That many rows wouldn't normally be a problem, but this database is no rocket, and data transfer requests need to be kept to a minimum

Not having write access to the database is quite crippling, as almost all of the LLBLGen functionailty is dependant upon existing views to manipulate data. In my case I would love the facility to provide a subquery as a view name, then I could refer to it with its modified and joined fields as a TypedView. Has anyone worked out a way of doing this?

Cheers, Jason

It appears that the method you suggest would indeed return the data I need, but to have this value returned automatically during (for example when using databinding) I would have to hack the generated code to call this function instead of the built-in one, as the default routine is called automatically by the Lazy-load routines.

I suggest you hack the databinding to only fetch the filtered AddressTS records. If for example you are working in a web app, and have a grid to list customers and another one to list the Addresses of a selected Customer, then you may use select parameters to the datasource of the second grid to put filters as you want. Or you can handle the first grid's Select event to manually fetch the filtered Addresses and bind them to the second grid.

Hi Walaa, I might look into the databinding option, although it will obviously be tricky if I have to pass any filter conditions to the underlying function.

Cheers, Jason