Hello,

I'm attempting to recreate this query and the segments in parens - they both amount to the same thing and don't seem to have any impact to the query plan or used indexes - are giving me difficulty:

select ms.* from mystuff ms inner join customers c on c.idcustomer = ms.idcustomer inner join othercustomer otc on otc.idcustomer = ms.idcustomer (and otc.idcountry = ms.idcountry) where ms.createdon >= dateadd(hour, -180, getdate()) (and otc.idcountry = ms.idcountry)

I've got the relationpredicatebucket populating mostly correct as I'm getting back data I'd expect, but also getting stuff that tells me the IDCountry factor is not being incorporated. This is the code so far:

var bucket = new RelationPredicateBucket(); bucket.Relations.Add(MyStuff.Relations.CustomerEntityUsingIdcustomer); bucket.Relations.Add(CustomerEntity.Relations.OtherCustomerEntityUsingIdcustomer); bucket.Relations.Add(OtherCustomerEntity.Relations.CountryEntityUsingIdcountry);

IPredicateExpression predExp = new PredicateExpression(); predExp.Add(MyStuff.CreatedOn >= DateTime.Now.Subtract(new TimeSpan(lookBack, 0, 0)));

bucket.PredicateExpression.Add(predExp);

Thank you in advance!

Above SQL could be generated using this approximate code:

var bucket = new RelationPredicateBucket();
bucket.Relations.Add(MyStuff.Relations.CustomerEntityUsingIdcustomer);

IEntityRelation theCustomRelation = CustomerEntity.Relations.OtherCustomerEntityUsingIdcustomer;
theCustomRelation.AddEntityFieldPair(OtherCustomerFields.IdCountry, MyStuffFields.IdCountry);

bucket.Relations.Add(theCustomRelation);

...

Thanks for the reply.

What I ended up doing was

bucket.PredicateExpression.AddWithAnd(OtherCustomerFields.Idcountry == myStuff.Idcountry)

Appears to be working very nicely.

Thanks again.