I am using AdventureWorks database on SQL2005, and as an example I was trying to pull all Employee records where the Contact.First name was equal to "David".

I tried this code and it did not work, could someone please point me in the right direction?

using System;
using System.Collections.Generic;
using System.Text;
using AdventureWorks.Data;
using AdventureWorks.Data.DatabaseSpecific;
using AdventureWorks.Data.EntityClasses;
using AdventureWorks.Data.FactoryClasses;
using AdventureWorks.Data.HelperClasses;
using AdventureWorks.Data.RelationClasses;
using AdventureWorks.Data.ValidatorClasses;
using SD.LLBLGen.Pro.ORMSupportClasses;
using SD.LLBLGen.Pro.DQE;

namespace AdventureWorks.ConsoleSandBox
{
    class Program
    {
        static void Main(string[] args)
        {
            
            // SELECT * FROM Employee WHERE ContactID IN (SELECT ContactID from Contact Where FirstName = "David" )

            EntityCollection employees = new EntityCollection(new EmployeeEntityFactory());

            IPredicateExpression filter = new PredicateExpression();
            filter.Add(new FieldCompareSetPredicate( EmployeeFields.ContactID, null, ContactFields.ContactID, null, SetOperator.In, null));

            IRelationPredicateBucket filterBucket = new RelationPredicateBucket();
            filterBucket.PredicateExpression.Add(filter);
            filterBucket.PredicateExpression.AddWithAnd(PredicateFactory.CompareValue(ContactFieldIndex.FirstName,ComparisonOperator.Equal,"David"));

            ISortExpression sorter = new SortExpression();
            sorter.Add(SortClauseFactory.Create(ContactFieldIndex.FirstName, SortOperator.Descending));

            IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.EmployeeEntity);
            prefetchPath.Add(EmployeeEntity.PrefetchPathContact, 1, null, null, sorter);

            DataAccessAdapter adapter = new DataAccessAdapter();
            adapter.FetchEntityCollection(employees, filterBucket, prefetchPath);

            //==================

            for (int count = 0; count < employees.Count; count++)
            {
                Console.WriteLine(((EmployeeEntity)employees[count]).Contact.FirstName);
            }

            Console.Read();


        }
    }
}

Thank you in advance, Bruno Valle

As long as you have a relation between Employee and Contact using ContactID then you can do something like this that wouldn't require as many queries. If this doesn't help you may look at enabling query tracing, which can be found in the troubleshooting section of the manual and elaborating a bit on what you mean by doesn't work; exception, no results....

EntityCollection employees = new EntityCollection(new EmployeeEntityFactory());

            IRelationPredicateBucket filterBucket = new RelationPredicateBucket();
            filterBucket.PredicateExpression.Add(ContactFields.FirstName == "David");
            filterBucket.Relations.Add(EmployeeEntity.Relations.ContactEntityUsingContactID);

            ISortExpression sorter = new SortExpression();
            sorter.Add(ContactFields.FirstName | SortOperator.Descending);

            IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.EmployeeEntity);
            prefetchPath.Add(EmployeeEntity.PrefetchPathContact, 1, null, null, sorter);

            DataAccessAdapter adapter = new DataAccessAdapter();
            adapter.FetchEntityCollection(employees, filterBucket, prefetchPath);

The person involved in this test is on vacation, so when he returns we will try out.

To answer your questions: Yes, there is a relation between Contact and Employee, and I was getting exception when trying to run the code. It would give me:

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException was unhandled
  Message="An exception was caught during the execution of a retrieval query: The multi-part identifier \"AdventureWorks.Person.Contact.FirstName\" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception."
  Source="SD.LLBLGen.Pro.ORMSupportClasses.NET20"
  QueryExecuted="\r\n\tQuery: SELECT [AdventureWorks].[HumanResources].[Employee].[EmployeeID], [AdventureWorks].[HumanResources].[Employee].[NationalIDNumber], [AdventureWorks].[HumanResources].[Employee].[ContactID], [AdventureWorks].[HumanResources].[Employee].[LoginID], [AdventureWorks].[HumanResources].[Employee].[ManagerID], [AdventureWorks].[HumanResources].[Employee].[Title], [AdventureWorks].[HumanResources].[Employee].[BirthDate], [AdventureWorks].[HumanResources].[Employee].[MaritalStatus], [AdventureWorks].[HumanResources].[Employee].[Gender], [AdventureWorks].[HumanResources].[Employee].[HireDate], [AdventureWorks].[HumanResources].[Employee].[SalariedFlag], [AdventureWorks].[HumanResources].[Employee].[VacationHours], [AdventureWorks].[HumanResources].[Employee].[SickLeaveHours], [AdventureWorks].[HumanResources].[Employee].[CurrentFlag], [AdventureWorks].[HumanResources].[Employee].[rowguid] AS [Rowguid], [AdventureWorks].[HumanResources].[Employee].[ModifiedDate] FROM [AdventureWorks].[HumanResources].[Employee]  WHERE ( ( ( [AdventureWorks].[HumanResources].[Employee].[ContactID] IN (SELECT [AdventureWorks].[Person].[Contact].[ContactID] FROM [AdventureWorks].[Person].[Contact] )) AND [AdventureWorks].[Person].[Contact].[FirstName] = @FirstName1))\r\n\tParameter: @FirstName1 : String. Length: 50. Precision: 0. Scale: 0. Direction: Input. Value: David.\r\n"
  StackTrace:
       at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)
       at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery(IRetrievalQuery queryToExecute, IEntityFactory2 entityFactory, IEntityCollection2 collectionToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo, Boolean allowDuplicates, IValidator validatorToUse, IEntityFields2 fieldsUsedForQuery)
       at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollectionInternal(IEntityCollection2 collectionToFill, IRelationPredicateBucket& filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, Int32 pageNumber, Int32 pageSize)
       at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, IPrefetchPath2 prefetchPath)
       at AdventureWorks.ConsoleSandBox.Program.Main(String[] args) in C:\App\AdventureWorks\ConsoleSandBox\Program.cs:line 39
       at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()

When the adapter.FetchEntityCollection statement ran.

Thank you, bvalle