I'm new to LLBLGen Pro and working on quite a substantially sized project (approx. 800 tables). I need some help with a very basic prefetch (well, in my mind it should be, but I just can't get it)

Technology: MSSQL 2005 LLBLGEN Pro - DataAdapter - v2.6 C#

Tables and relations: Customer - 1:m -> CustomerContact -n:m-> Contact - 1:1 -> ContactAddress (Synonym for view in CRM db)

We have custom Properties on the Contact entity that gives us access to the full CRM address for the contact in the form ContactEntity.PrimaryFullAddress.

I want to fetch the a single Customer entity where the account number is '000000000003' and filter on some other values i.e. CustomerContact.IsPrimary = 1.

Below is the SQL query I am trying to achieve ... but I JUST CAN'T translate it into LLBLGen code correctly :

SELECT TOP 1 dbo.tblStatement.CustomerID, dbo.tblCustomer.AccountNo, dbo.tblContact.ContactFName, dbo.tblContact.ContactLName, dbo.tblStatement.StmtDate FROM dbo.tblStatement INNER JOIN dbo.tblCustomer ON dbo.tblStatement.CustomerID = dbo.tblCustomer.CustomerID INNER JOIN dbo.tblCustomerContact ON dbo.tblCustomer.CustomerID = dbo.tblCustomerContact.CustomerID INNER JOIN dbo.tblContact ON dbo.tblCustomerContact.ContactID = dbo.tblContact.ContactID INNER JOIN dbo.tblContactAdrs ON dbo.tblContact.ContactID = dbo.tblContactAdrs.ContactID where tblcustomer.accountno = '000000000003' and tblcustomercontact.isprimary=1 and datediff(month,getdate(),tblStatement.stmtdate) <= 1 ORDEr BY tblStatement.stmtDate

I can calc the datediff of course, but I just can't seem to get the query prefetch translated to LLBLGen Pro code so that I can fetch the customerentity customercontact, contact and address all in one.

[Edit] The current prefetch query ... EntityCollection<CustomerEntity> customers = new EntityCollection<CustomerEntity>();

               SortExpression sorter = new SortExpression(StatementFields.StmtDate| SortOperator.Descending);

                IRelationPredicateBucket bucket = new RelationPredicateBucket();
                bucket.PredicateExpression.Add(CustomerFields.CompanyId == companyId);
                bucket.PredicateExpression.Add(CustomerFields.AccountNo == accountNo);

                PrefetchPath2 prefetch = new PrefetchPath2((int)EntityType.CustomerEntity);
                prefetch.Add(CustomerEntity.PrefetchPathCustomerContact)
                    .SubPath.Add(CustomerContactEntity.PrefetchPathContact)
                    .SubPath.Add(ContactEntity.PrefetchPathContactAdrs);

//(adapter is the IDataAccessAdapter instance) adapter.FetchEntityCollection(customers,bucket, 0, sorter, prefetch);

Looking forward to clarity

Dave

SELECT TOP 1 dbo.tblStatement.CustomerID, dbo.tblCustomer.AccountNo, dbo.tblContact.ContactFName, dbo.tblContact.ContactLName, dbo.tblStatement.StmtDate

Do you want to fetch the above flat list of fields? Or, do you want to fetch a graph of entities, starting by the Customer and then fetching its related customerContact, contact and address entities? (but this won't be in one go, as it will execute one query for each type).

Well, truth be told I just want those fields. It should only return a single row of data. I'm still getting use to how it all works so I wanted to fetch a collection, then just return the first entity of that collection.

Please forgive my ignorance. I'll get there one day

Well, truth be told I just want those fields.

Then use a DynamicList and forget all about PrefetchPaths.

Walaa wrote:

Well, truth be told I just want those fields.

Then use a DynamicList and forget all about PrefetchPaths.

You rock!!

Thank you!

Walaa wrote:

Well, truth be told I just want those fields.

Then use a DynamicList and forget all about PrefetchPaths.

Does this work for adapter template as wel as self-servicing?

Yep