We have a site table (SiteID, Alias, SiteName) that links to the user talbe (UserID, SiteID, UserName, Password). Site.Alias has a unique index.

The user logs in using Alias, UserName, Password. Currently, I return an EntityCollection filtered on Site.Alias. This returns 1 site which I then fetch the Users EntityCollecton from the SiteEntity filtering on UserName and Password. I then have to take the UserEntity and Attach the SiteEntity to it as the return value.

Is there a way to generate the following:

SELECT Fields FROM User JOIN Site ON SiteID WHERE SiteAlias="Alias" AND UserName="UserName" AND Password="Password"

Then fill the UserEntity and SiteEntity.

I know, I probably have missed this in the docs somewhere.

Give something like this a try. To create the join you will need to include the relation with the predicate so that the initial search will find the user that you need. Then you just define a prefetch path for the Site and it will also be fetched with the user.

            IRelationPredicateBucket filter = new RelationPredicateBucket();
            filter.Relations.Add(UserEntity.Relations.SiteEntityUsingSiteId);
            filter.PredicateExpression.Add(UserFields.Password == "password");
            filter.PredicateExpression.Add(UserFields.Username == "username");
            filter.PredicateExpression.Add(SiteFields.Alias == "Alias");

            IPrefetchPath2 path = new PrefetchPath2((int)EntityType.UserEntity);
            path.Add(UserEntity.PrefetchPathSite);

            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                EntityCollection users = new EntityCollection(new UserEntityFactory());
                adapter.FetchEntityCollection(users, filter, 0, null, path);
            }