This might be really basic.. but anyhow.. how can I write the following query as a dynamic query

SELECT A.Id, A.FirstName, A.LastName, O.OfficeID FROM Agent A INNER JOIN Office O ON A.OfficeID = O.OfficeID WHERE (FirstName LIKE '%ery%' OR LastName LIKE '%ery%') AND O.RegionID = 1

Hi. I assume you are using Adapter templates and LLBLGenPro v2.5. Next time please post the required information (http://llblgen.com/TinyForum/Messages.aspx?ThreadID=7722) so we can help you better

Here you have two options.

A.Fetch a DynamicList (Ref: LLBLGenPro Help - Using the generated code - Adapter - Using TypedViews, TypedLists and DynamicLists - Using dynamic lists). This will return a datatable with the fields you specify:

// part of the first/last name to search
string namePartToSearch = "%ery%";

// fields to incluide in the call
ResultsetFields fields = new ResultsetFields(4);
fields.DefineField(AgentFields.Id, 3, "AgentId");
fields.DefineField(AgentFields.FirstName, 0, "FirstName");
fields.DefineField(AgentFields.LastName, 1, "LastName");
fields.DefineField(OfficeFields.Id, 2, "OfficeId");

// build the filter
IRelationPredicateBucket filter = new RelationPredicateBucket();
filter.Relations.Add(AgentEntity.Relations.OfficeEntityUsingOfficeId);
filter.PredicateExpression.Add(AgentFields.FirstName % namePartToSearch | AgentFields.ShipCity % namePartToSearch);
filter.PredicateExpression.Add(OfficeFields.RegionId == 1);

// fetch the dynamiclist results into a datatable
DataTable results = new DataTable();
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchTypedList(fields, results, filter);
}

// accessing the results
foreach (DataRow r in results.Rows)
{
    Console.WriteLine("{0}\t{1}\t{2}\t{3}", r[0], r[1], r[2], r[3]);
}

B. Fetch an EntityCollection (Ref: LLBLGenPro Help - Using the generated code - Adapter - Using the entity collection classes). This will return an entitycollection of AgentEntity objects. You can prefetch the OfficeEntity classes if you will need to access them:

// part of the first/last name to search
string namePartToSearch = "%ery%";

// collection to fetch
EntityCollection<AgentEntity> agents = new EntityCollection<AgentEntity>(new AgentEntityFactory());

// build the filter
IRelationPredicateBucket filter = new RelationPredicateBucket();
filter.Relations.Add(AgentEntity.Relations.OfficeEntityUsingOfficeId);
filter.PredicateExpression.Add(AgentFields.FirstName % namePartToSearch | AgentFields.ShipCity % namePartToSearch);
filter.PredicateExpression.Add(OfficeFields.RegionId == 1);

// Build a prefetchPath. This is necessary if you will be accessing more properties of the agent.Office object. Otherwise you
// could just fetch the agent objects.
IPrefetchPath2 path = new PrefetchPath2((int)EntityType.AgentEntity);
path.Add(AgentEntity.PrefetchPathOffice);

// fetch the results
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(Agents, filter, path);
}

// accessing the results
foreach (AgentEntity a in Agents)
{
    Console.WriteLine("{0}\t{1}\t{2}\t{3}", a.AgentId, a.ShipCity, a.Office.CompanyName, a.Office.Region);
}

Please read the pointed help references to fully understand the differences between A and B

Cheers.

thanks david for the quick help.. my apologies for not mentioning the version and template.. i used the code from the option A u suggested.. works like a charm

thanks again