I apologize if this has been asked many times, but I can't seem to find the solution.

My query is this: SELECT * FROM Rating INNER JOIN ClassCodes ON Rating.ClassCodeID = ClassCodes.ClassCodeID AND Rating.ClassCodeTableID = ClassCodes.ClassCodeTableID

I'm having difficulty trying to figure out how to do the JOIN with the AND as shown above.

Thanks for the help.

Use the CustomeFilter inside the IEntityRelation.

"Custom filter for JOIN clauses which are added with AND to the ON clause resulting from this EntityRelation. By adding a predicate expression with fieldcomparevalue predicate objects for example, you can add extra filtering inside the JOIN"

Walaa wrote:

Use the CustomeFilter inside the IEntityRelation.

"Custom filter for JOIN clauses which are added with AND to the ON clause resulting from this EntityRelation. By adding a predicate expression with fieldcomparevalue predicate objects for example, you can add extra filtering inside the JOIN"

I stumbled on to custom filter shortly after I posted the message. However, I'm still having some trouble. I receive the error: Invalid cast from ClassCodesFieldIndex to System.Guid. I assume this is because I need to give it a value and not a field? How do I specify the value?

Thanks again.

                IPredicateExpression customFilter = new PredicateExpression();
                customFilter.Add(PredicateFactory.CompareValue(RatingFieldIndex.ClassCodeTableId, ComparisonOperator.Equal, ClassCodesFieldIndex.ClassCodeTableId));
                IRelationPredicateBucket filter = new RelationPredicateBucket();

                filter.Relations.Add(RatingEntity.Relations.ClassCodeEntityUsingClassCodeId).CustomFilter = customFilter;

                ratings = new EntityCollection(new RatingEntityFactory());

                adapter.FetchEntityCollection(ratings, filter);

Billb,

You're almost there. I made one tweak to your predicateExpression referencing a variable myClassCodeTableID that should contain the specific ID/GUID value you are joining against.

IPredicateExpression customFilter = new PredicateExpression();
customFilter.Add(PredicateFactory.CompareValue(RatingFieldIndex.ClassCodeTableId, ComparisonOperator.Equal, myClassCodeTableID));
IRelationPredicateBucket filter = new RelationPredicateBucket();

filter.Relations.Add(RatingEntity.Relations.ClassCodeEntityUsingClassCodeId).CustomFilter = customFilter;

ratings = new EntityCollection(new RatingEntityFactory());
adapter.FetchEntityCollection(ratings, filter);

I didn't compile it, but the rest looked good to me.

Sorry for being thick, but what is the value of myClassCodeTableID? I'm just doing a join, I don't have a particular value. The value for ClassCodeTableID is coming from whatever is in the Rating table since its a column in that table. Is your solution still applicable?

Thanks again!

Paul.Lewis wrote:

Billb,

You're almost there. I made one tweak to your predicateExpression referencing a variable myClassCodeTableID that should contain the specific ID/GUID value you are joining against.

IPredicateExpression customFilter = new PredicateExpression();
customFilter.Add(PredicateFactory.CompareValue(RatingFieldIndex.ClassCodeTableId, ComparisonOperator.Equal, myClassCodeTableID));
IRelationPredicateBucket filter = new RelationPredicateBucket();

filter.Relations.Add(RatingEntity.Relations.ClassCodeEntityUsingClassCodeId).CustomFilter = customFilter;

ratings = new EntityCollection(new RatingEntityFactory());
adapter.FetchEntityCollection(ratings, filter);

I didn't compile it, but the rest looked good to me.

If you want to compare 2 columns, you have to use a FieldCompareExpressionPredicate, created by PredicateFactory.CompareExpression. You can with that, create a filter like: WHERE Foo = Bar

where both foo and bar are fields in the same table (or another table in the join).

If you use a FieldCompareValuePredicate (created by PredicateFactory.CompareValue), the value passed in is considered a value, and you can create a filter like: WHERE Foo = @value

so if you pass in a field index, like MyTableFieldIndex.Bar, it will consider that as a value, not as a field. So your code should look like:

IPredicateExpression customFilter = new PredicateExpression();
customFilter.Add(PredicateFactory.CompareExpression(
    RatingFieldIndex.ClassCodeTableId, ComparisonOperator.Equal, 
        new Expression (EntityFieldFactory.Create(ClassCodesFieldIndex.ClassCodeTableId))));

IRelationPredicateBucket filter = new RelationPredicateBucket();
filter.Relations.Add(RatingEntity.Relations.ClassCodeEntityUsingClassCodeId).CustomFilter = customFilter;
ratings = new EntityCollection(new RatingEntityFactory());
adapter.FetchEntityCollection(ratings, filter);

I did a bit more jiggling, since I had some other tables to add to the mix and I believe I got it to work. Very cool. Thanks guys!

Otis wrote:

If you want to compare 2 columns, you have to use a FieldCompareExpressionPredicate, created by PredicateFactory.CompareExpression. You can with that, create a filter like: WHERE Foo = Bar

where both foo and bar are fields in the same table (or another table in the join).

If you use a FieldCompareValuePredicate (created by PredicateFactory.CompareValue), the value passed in is considered a value, and you can create a filter like: WHERE Foo = @value

so if you pass in a field index, like MyTableFieldIndex.Bar, it will consider that as a value, not as a field. So your code should look like:

IPredicateExpression customFilter = new PredicateExpression();
customFilter.Add(PredicateFactory.CompareExpression(
    RatingFieldIndex.ClassCodeTableId, ComparisonOperator.Equal, 
        new Expression (EntityFieldFactory.Create(ClassCodesFieldIndex.ClassCodeTableId))));

IRelationPredicateBucket filter = new RelationPredicateBucket();
filter.Relations.Add(RatingEntity.Relations.ClassCodeEntityUsingClassCodeId).CustomFilter = customFilter;
ratings = new EntityCollection(new RatingEntityFactory());
adapter.FetchEntityCollection(ratings, filter);