I think this is a very easy problem to solve. I am using the Adapter setup.

Here is the relation:

Listing [1 - M] ListingKeyword

The relevant fields:

Listing -ListingId (PK)

ListingKeyword -ListingId (FK on Listing.ListingId) -KeywordId (FK on a table Keyword)

The desired output in SQL

SELECT DISTINCT Listing.*
FROM   Listing INNER JOIN
            ListingKeyword ON Listing.ListingId = ListingKeyword.ListingId
Where ListingKeyword.KeywordId = "..."

The part I can't figure out is how to get the "DISTINCT" or unique rows based on Listing only. I want to filter with ListingKeyword but not retain their fields, otherwise I will be returned many rows for the same Listing, since a Listing can have many associated ListingKeywords.

What is the BEST way to do this? I don't want to write SQL Syntax in code.

Another feature is that I want to add other tables related to Listing and potentially filter on any of the fields. See the sample relation below:

Listing [M - 1] ListingLocation [M - 1] City

So I thought that using a TypedList would aid with this, except that I can't figure out how to use include/exclude desired fields so that I can remove "duplicate" rows.

Thanks for the help.

I figured out a way that makes sense to me. Please let me know if there is a better way to do this.

I basically followed the examples in the documentation about using FieldCompareSetPredicate to fix my problem:

      filter.Add(new FieldCompareSetPredicate(
                                          ListingFields.ListingId,
                                          null,
                                          ListingKeywordFields.ListingId,
                                          null,
                                          SetOperator.In,
                                          listingFilter));

bucket.PredicateExpression.Add(filter);
adapter.FetchTypedList(listings.GetFieldsInfo(), listings, bucket, 0, null, false);

listingFilter contains predicateExpressions.

I then created a "TypedList" to retrieve other tables that were relatedbut didn't have to be included in the filter. The TypedList is retrieved with the filter by using "FetchTypedList".

This seems to make sense to me... I guess I just have to adjust to a different way of thinking about data retrievals.

You may also look at using a dynamic list to do this. I prefer these sometimes when I don't want to create a new typed list if I will only be using the list in a few places. You can then use the relation that's most likely already defined so that you don't use the set predicate.