We have twelve similar tables which share a certain group of fields. These fields have exactly the same names and types in all of these tables. We need to construct a query that returns selects these fields from several of the tables and returns them as a single result set.

Is there any way to do this in LLBLGen? I know it can be done by using a supertable for the common fields, but inheritance isn't a viable solution in this case.

Hello,

do you use the adapter or the selfservice scenario? If it's all the time the same request on the twelve table, you can use the Fields Mapped on related fields in the designer to create a type list that will contain all the fields you want.

We use the Adapter.

We don't use the designer, though. The database and LLBLGen configuration is programmatically generated. So it looks like time to add features to that generator...

Take a look at using dynamic lists. You can form one result from many sources. Let us know if this will work.

Again, it appears to require that each column of the final result set map to a single field on a single entity.

Inheritance certainly appears to be the only way to get around the UNION requirement. In fact, we've decided that our object model would actually result in faster database queries using inheritance; we've got enforcable foreign key relations defined on every join we'll be making, and the results do not need to be unique. Doing everything in one SELECT without any UNIONs would be quicker, even if we do end up with a few giant tables, eg. for storing audit histories.

well, UNION isn't supported so any combination of select statements has to be done outside the db: you've to combine results into the same resultbucket. I dont' see why inheritance would solve a UNION related problem though.

Using inheritance, the data being selected can all be placed in one table.

This also makes our key constraints a little simpler.