I love LLBLGen Pro, but I've run into some important scenarios in my project where I don't think it can help me. I hope someone can prove me wrong.

A basic table structure:

Contact -> Individual (subtype) Contact 1-many ContactAddress many-1 Address Contact 1-many Email

ContactAddress and Email both have a field ClientID that is many-1 to a Client table.

The list I need is as follows:

All Individuals with address / email info specific to a client, if it exists. All individuals meaning everyone regardless of if they have address or email info, and, if someone has more than one email/address, with different clientids, just return the address/email info for a given clientid.

I would like the object returned to be a strongly typed datatable or object.

Ideally, I would use a view, as LLBLGen can create an entity from these. But I can't, because the query involves parameters in the where clauses of the sub queries (for the derived tables), and views don't take parameters.

And I'm unable to determine a way to form the query without the derived tables, so I can't use LLBLGen from code.

The easiest thing I've been able to do is create a stored proc that returns the results I need, and have Visual Studio generate a strongly-typed datatable for me from the proc. Can LLBLGen Pro do something similar?

Here is the query I've been using:

SELECT   dbo.Individual.LastName, dbo.Individual.FirstName, dbo.Contact.ContactID, adr.Street, adr.City, adr.State, adr.ZipCode, eml.EmailAddress, 
                      eml.Name
FROM         dbo.Contact INNER JOIN
                      dbo.Individual ON dbo.Contact.ContactID = dbo.Individual.ContactID LEFT OUTER JOIN
                          (SELECT    dbo.Email.ContactID, dbo.Email.EmailAddress, dbo.EmailType.Name
                            FROM          dbo.Email INNER JOIN
                                                   dbo.EmailType ON dbo.Email.EmailTypeID = dbo.EmailType.EmailTypeID WHERE dbo.Email.ClientID=@client) AS eml ON 
                      dbo.Contact.ContactID = eml.ContactID LEFT OUTER JOIN
                          (SELECT    dbo.ContactAddress.ContactID, dbo.Address.Street, dbo.Address.City, dbo.Address.State, dbo.Address.ZipCode
                            FROM          dbo.ContactAddress INNER JOIN
                                                   dbo.Address ON dbo.ContactAddress.AddressID = dbo.Address.AddressID WHERE dbo.ContactAddress.ClientID=@client) AS adr ON dbo.Contact.ContactID = adr.ContactID

Can anyone figure a way to represent this query without the subqueries, thus compatible with LLBLGen? And, if so, is there a way to get a strongly-typed object/datatable of this information for code generation (ie TypedList)?

Thanks!

You could fetch a hierarchy of objects rather than one dimensional list. i.e. fetch all individuals, and use a prefetchPath with a filter to filter related e-mails and addresses in the form of related objects.

But if you want the each list item to contain the previous set of data. The you can use a DynamicList, and you will use dao.GetMultiAsDataTable(selfService) or adapter.FetchTypedList(adapter) to fetch the DynamicList.

And for the Joins you will need to add some relations to the fetchMethod, to either the RelationCollection (selfService) or the RelationPredicateBucket.Relations (adapter)

For the outer Joins with filters, you will add an EntityRelation object and set its CustomFilter, then use JoinHint.Left when you add it to the relations collection.