Hi,

I'm just getting going with llbl, and I've hit a sticking point with filtering. I've been looking at the docs and similar threads on these forums, but can't get my head around something. I'm actually wondering if it's possible, and whether my approach is all wrong (about to look at using a sp for this). A worked example may just sort me out though.

Here is a schematic of three tables, showing only pertinent fields:

Owner

OwnerID

Mare

MareID OwnerID

Booking

BookingID MareID Season (int eg. 2003|2004|2005)

Given an OwnerEntity, I would like to get a MareCollection containing Mares that belong to the Owner, but only containing Mares that do not have a Booking for the current season. In SQL my query would be:

SELECT m.* FROM Mare m WHERE m.OwnerID = 1234 AND m.MareID NOT IN ( SELECT MareID FROM Booking WHERE Season = 2005 )

I'm not sure whether to focus on owner.GetMultiMares() or owner.SetCollectionParametersMares(); owner.Mares(); and how do I construct the Predicate expression?

Many thanks in advance,

Trevor Jobling

ps - on another project, we have managed to create a database schema and llblgen project file that generates code that will not compile. It's to do with hiding certain relationships which then leads to "type expected" compile errors. Would anyone in Solutions Design be interested in a copy of it, or is this something that's already understood by people more experienced than I?

TrevorJobling wrote:

I'm just getting going with llbl, and I've hit a sticking point with filtering. I've been looking at the docs and similar threads on these forums, but can't get my head around something. I'm actually wondering if it's possible, and whether my approach is all wrong (about to look at using a sp for this). A worked example may just sort me out though.

It can be done, with the FieldCompareSetPredicate class. I've written an example below.

Here is a schematic of three tables, showing only pertinent fields:

Owner

OwnerID

Mare

MareID OwnerID

Booking

BookingID MareID Season (int eg. 2003|2004|2005)

Given an OwnerEntity, I would like to get a MareCollection containing Mares that belong to the Owner, but only containing Mares that do not have a Booking for the current season. In SQL my query would be:

SELECT m.* FROM Mare m WHERE m.OwnerID = 1234 AND m.MareID NOT IN ( SELECT MareID FROM Booking WHERE Season = 2005 )

I'm not sure whether to focus on owner.GetMultiMares() or owner.SetCollectionParametersMares(); owner.Mares(); and how do I construct the Predicate expression?

Many thanks in advance,

// filters
IPredicateExpression filter = new PredicateExpression();
filter.Add(PredicateFactory.CompareValue(MareFieldIndex.OwnerID, ComparisionOperator.Equal, 1234));
filter.AddWithAnd(new FieldCompareSetPredicate(
    EntityFieldFactory.Create(MareFieldIndex.MareID),
    EntityFieldFactory.Create(BookingFieldIndex.MareID),
    SetOperator.In,
    PredicateFactory.CompareValue(BookingFieldIndex.Season, ComparisonOperator.Equal, 2005),
    true));

// calling the fetch, performing a forced fetch
owner.GetMultiMares(true, filter);

ps - on another project, we have managed to create a database schema and llblgen project file that generates code that will not compile. It's to do with hiding certain relationships which then leads to "type expected" compile errors. Would anyone in Solutions Design be interested in a copy of it, or is this something that's already understood by people more experienced than I?

You can't hide a single side of a relation in Selfservicing. You always have to hide both sides together in selfservicing, there is a warning of this in the documentation (but you might have missed it, it's not stated in big caps or anything )

Otis wrote:

You can't hide a single side of a relation in Selfservicing. You always have to hide both sides together in selfservicing, there is a warning of this in the documentation (but you might have missed it, it's not stated in big caps or anything )

aah... caught out on RTFM

I really appreciate that example though - it's clarified things and got me going. Thanks a million.

-t