I am sure someone has already asked this question, but I just cannot find it in the forums.

I have 2 tables Site and SiteTemplateType. Basically I want to build a search which returns me only SiteTemplateType records that are used in Site. I can build the following query on SQL and it returns me the correct result set.

SELECT DISTINCT ST.SiteTemplateTypeID, ST.SiteTemplateTypeName FROM TBLSITE S JOIN TBLSITETEMPLATETYPE ST ON ST.SiteTemplateTypeID = S.SiteTemplateTypeID

Could someone point me in the right direction on how to do this with generated code? I am using adapter style.

Thank you, Bruno Valle

When you are filling a collection you won't have duplicate records. So all you need to do is run a search that joins to Site from SiteTemplateType.

Try something like this.

using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                EntityCollection siteTemplates = new EntityCollection(new SiteTemplateTypeEntityFactory());
                filter.Relations.Add(SiteTemplateTypeEntity.Relations.SiteEntityUsingSiteTemplateTypeId);
                adapter.FetchEntityCollection(siteTemplates, filter);
            }