Hello,

I'm using LLBL v2.5, adapter version.

I've created a TypedList for a table called Sites which has a (1:n) relation to a table called Siteaddresses. The TypedList contains fields for both tables including SiteName and SiteAddressLine1, etc.

The relation has a description of: Site.Idsite - Siteaddresses.Idsite (1:n) - with a 'left' join hint.

The problem is that I want to see address information in the list only when Siteaddressess.Idtypeofaddress = 2, otherwise I don't want the address information to be in the list however I still want to see the site information.

Yet when I apply a predicate expression like the following:

filter.PredicateExpression.Add(SiteaddressFields.Idtypeofaddress.SetObjectAlias("Siteaddresses") == 1);

The predicate will filter out any sites that don't have corresponding addresses of type 1; it seems to be acting as an inner join?

Is there a way around this?

This is actually correct behavior for SQL (and LLBLGen). If you use a filter in the WHERE clause against a table you are LEFT JOINing to, it will effectively make the join into an inner join.

What you'll need to do to get the behavior you want is to make the filter an _ additional condition of the join_.

So:

SELECT
*
FROM
sites s
LEFT JOIN siteaddress sa ON sa.IdSite = s.IdSite AND sa.Idtypeofaddress = 2

You should be able to do this by adding a CustomFilter to the relation in question. So:

myRelation.CustomFilter = New PredicateExpression(SiteaddressFields.Idtypeofaddress.SetObjectAlias("Siteaddresses") == 1);

That's off the top of my head, so it might not compile as-is. But that's the gist of it.

HTH,

Phil

Yes, that's likely what you have to do.

Here is an example: I want to use my OrdersTypedList, I want to Fetch orders and some employee info (with Left join), and I want to show employee info only if s/he if from UK:

// define the custom relation
IRelationPredicateBucket filter = new RelationPredicateBucket();                                    

IEntityRelation rel = OrderEntity.Relations.EmployeeEntityUsingEmployeeId;
rel.CustomFilter = new PredicateExpression(EmployeeFields.Country == "UK");
filter.Relations.Add(rel, JoinHint.Left);

// fetch results
DataTable results = new DataTable();
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchTypedList(new OrdersTypedList().GetFieldsInfo(), results, filter);
}

Sorry, I think this is a more simple approach (if you want the relation behaves always this way):

• On the BuildRelationSet() method of the OrdersTypedList class add this to the CODE_REGION:

// __LLBLGENPRO_USER_CODE_REGION_START AdditionalRelations
IEntityRelation rel = (IEntityRelation) _filterBucket.Relations[0];
rel.CustomFilter = new PredicateExpression(EmployeesFields.Country == "UK");
// __LLBLGENPRO_USER_CODE_REGION_END

• Then you can fill the the typedList normally somewhere in your code:

OrdersTypedList orders = new OrdersTypedList();

using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchTypedList(orders);
}

Thanks for the help, I have it working now.

This is what I ended up with for adding a CustomFilter to my relation.

IRelationPredicateBucket filter = sites.GetRelationInfo();

for (int x = 0; x < filter.Relations.Count; x++)
{
        if (filter.Relations[x].AliasEndEntity.Equals("Siteaddress"))
        {
            filter.Relations[0].CustomFilter = new PredicateExpression(
                     SiteaddressFields.Idtypeofaddress.SetObjectAlias("Siteaddress") == typeOfAddressID);
            break;
        }
}

ResultsetFields fields = (ResultsetFields)sites.GetFieldsInfo();

ISortExpression sorter = new SortExpression(SiteFields.Sitename.SetObjectAlias("Site") | SortOperator.Ascending);

_adapter.FetchTypedList(fields, sites, filter, 0, sorter, true);

Also, to daelmo, I didn't want to add the custom code because this condition is liable to change on the fly.

For the record then, is this a clean way to handle a custom filter or is there a better way?

I'm trying to upgrade to v2.6 and the solution suggested above is now broken. I'm looking in the documentation but have not yet found anything referring to TypedList Left Joins. Can anyone provide any suggestions?

Thanks,

Wm

Sorry I shouldn't have posted the follow on question here, I opened a new thread at:

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=14101