Right now I am missing just this one feature from LLBLGen Pro. Are there any plans to support it?

I mean true subselects with an aliased table, not just FieldCompareSetPredicate. Becauise sometimes some data is required which can be calculated only in subselects. Right now we are forced to do several selects and later merge results with hashtables...

you mean SELECT ... FROM (SELECT ... FROM BAR WHERE... ) As B WHERE...

?

Otis wrote:

you mean SELECT ... FROM (SELECT ... FROM BAR WHERE... ) As B WHERE...

?

yes

Would you consider using a database view for this query. And then map this view to a TypedView or an Entity.

Walaa wrote:

Would you consider using a database view for this query. And then map this view to a TypedView or an Entity.

That's other option we are using...

Bad thing it adds overhead of supporting database view, i.e track changes in tables, which are used by a view without compile time errors. That's what we use LLBLGen for: to get compile time errors when something changes

we use DynamicLists intensively, but sometimes they do not do a job.

The problem is that it's hard to specify in code what exactly should be used as a source for data. Often this can be solved by rewriting the query with joins instead, though I admit, that's not always the case nor is it always more efficient.

With 'hard to specify in code' I mean: if it takes a page of code to specify this from clause, it's of course undoable. Now, the relation objects create the FROM clause (together with additional joins necessary for inheritance), though that model then needs a change in where you can specify an EntityRelation between a set of fields and an EntityType + filter + relations, which gets pretty complicated.

I agree its a bit complicated.

But when you operate in EntityFields instead of entities, it could become easier to write.

Let say for example you have a set o fields you need to fetch as a subquery. You form a normal dynamic list with relations and predicates so far so good.

Then you have to give those fields a name: ContainingObjectName property will suit this need.

When you code main select all you have to add is a relation between main select and subselect which is done easily with LLBLGen, since you can create relations dynamically. and you can incorporate fields form subselect into main result list.

Just think about this. Maybe you will have better ideas.

Maybe another option is to cretae new Entity types dynamically. For example put a set of fields from different entitites to a new entity but this entityt will be substituted with subselect in a final query.

Another option would be to hide this merging with hashtables from a final user even though it will be different selects, like you do with Prefetch paths. But instead of thinking with enitity objects try thinking with sets of fields...

Thanks for the thoughts. I'll take it into consideration. If I can come up with a decent way to specify the queries, it's in

I had a long talk about this today with a Dutch SqlServer MVP, and he said that it almost always could be rewritten using joins. Which would solve your problem today as you then could use a relationcollection and move the predicates you want to use in the subquery to the predicate expression of the main query OR move the predicates as customfilter to the relation.

Could you explain to me why this wouldn't work for you now?