I need to do some union and intersect between multiple relational predicate bucket.

for example of intersect.

select * from (select x, y from a join b on a.id = b.id where b.name = "XXX") as c inner join (select x,y from a join b on a.id = b.id where b.name="YYY") as d inner join (select x,y from a join b on a.id = b.id where b.name="zzz") ....(the subquery is dynamically generated, it could be 5 or 10 of them)

each subquery will have a relational predicate bucket. can I do it? Do I need the dynamicRelation? if it is possible, I guess the performace would be better.

The other way, I fingered is to return each (select x, y from a join b on a.id = b.id where b.name = "XXX") as entity and use entity level function(union and intersect)

for example

IEnumerable<MyEntity> entity1 returned by FetchEntityCollection for (select x, y from a join b on a.id = b.id where b.name = "XXX") IEnumerable<MyEntity> entity2 returned by FetchEntityCollection for (select x, y from a join b on a.id = b.id where b.name = "XXX") final = entity1.Intersect(entity2);

I will use the loop to go through each of them to get the final result, but I guess performance will be bad.

Which way is better, do we have something better than these in LLBLGen?

Thanks a lot

DynamicTableDefinition indeed could help you in this kind of queries. You also could try useLinq2LLBL or LLBLGen Pro QuerySpec (now in Beta, downloadable from customer area) which can help you obtaining more accurate sql in complex queries.

What I don't understand is your query, as it seems invalid at a quick look..

Chaoyster wrote:

select * from (select x, y from a join b on a.id = b.id where b.name = "XXX") as c inner join (select x,y from a join b on a.id = b.id where b.name="YYY") as d inner join (select x,y from a join b on a.id = b.id where b.name="zzz") ....(the subquery is dynamically generated, it could be 5 or 10 of them)

Could you please post a real example of the SQL query you want to generate? Also, What LLBLGen version and runtime library version are you using? (http://llblgen.com/tinyforum/Messages.aspx?ThreadID=7717)

Thanks daelmo,

I have used Dynamic table define and Dynamic relation to build the query, it was ok, a bit tricky on alias. When I build the Dynamic relation between the sub queries, I got one issue is that it generates extra parameters, but they are not being used in query anyway.

Cheers

Great! If you need further assistance you can post on this thread and it will be re-opened.