I'm using LLBLGen 2.6 and the Adapter .Net 3.5 code generator.

I need to do the equivalent of the following SQL query, but I'm not sure it's possible:

select ph.Id
from tblPublishedQuestionareHeader ph
inner join tblPublishedUserTagQuestionare pt on pt.PublishedQuestionareHeaderId = ph.Id
inner join tblUser on tblUser.TagText LIKE '%' + pt.TagText
where tblUser.Id = 41

The part i'm unsure about is how to join on tblUser.TagText where the 'on' clause uses a like predicate matching against a field in the original table, not a predetermined string.

I've got this far:

EntityCollection<PublishedQuestionareHeaderEntity> headers = new EntityCollection<PublishedQuestionareHeaderEntity>();

IRelationPredicateBucket bucket = new RelationPredicateBucket();         bucket.Relations.Add(PublishedQuestionareHeaderEntity.Relations.PublishedUserTagQuestionareEntityUsingPublishedQuestionareHeaderId);

IPredicateExpression likeFilter = new PredicateExpression();
likeFilter.Add(new FieldLikePredicate(UserFields.TagText, null, "%lemon%"));

EntityRelation relation = new EntityRelation(UserFields.TagText, PublishedUserTagQuestionareFields.TagText, RelationType.OneToMany);
IEntityRelation rel = bucket.Relations.Add(relation, JoinHint.Inner);
rel.CustomFilter = likeFilter;
rel.CustomFilterReplacesOnClause = true;

bucket.PredicateExpression.Add(new PredicateExpression(UserFields.Id == 41);
adapter.FetchEntityCollection(headers, bucket);

Clearly I don't want the FieldLikePredicate(UserFields.TagText, null, "%lemon%") part done like that as the "%lemon%" is just a string, whereas I'd like to be able to specify a field in the table that's being joined - PublishedUserTagQuestionareFields.TagText.

Is there any way to do this?

Paul

What DB are you using? and Did you test that query in your DB? See this: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=10651&StartAtMessage=0&#59405

Sorry, forgot to mention that I'm using SQL Server 2008.

Yes, the SQL query I posted works fine when run in Sql Server Management Studio.

You can use a Custom Filter on the EntityRelation That's where you should use a FieldLikePredicate as David mentioned in his posted link.

Further you should set the EntityRelation.CustomFilterReplacesOnClause to true.

Thanks for your help. In the end I decided to implement the feature in a different way so I didn't need to use this particular join. But the answer is what I would have needed to do.

Paul