Joining on dynamic table name

Posts   
 
    
AlbertK
User
Posts: 44
Joined: 23-Dec-2009
# Posted on: 29-Sep-2016 21:24:41   

Hello,

Is there a way to join code-generated tables with another table whose name I only know at runtime with either LINQ, QuerySpec or any other way with LLBLGen?

Thanks Albert

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 30-Sep-2016 07:34:25   

Could you please elaborate more on the use case?

Are these tables already mapped to entities? If so, then please describe how they are being identified at runtime.

Or are these tables dynamic (created at runtime)? For which this thread might be helpful: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=23557

Also you might need to check Executing Plain SQL in v.5.1 (EAP 2)

AlbertK
User
Posts: 44
Joined: 23-Dec-2009
# Posted on: 30-Sep-2016 14:43:01   

The tables are not mapped to entities. The tables are auto-generated by the application to store custom fields, so I won't know the table name. However, they have an ID of the primary record in a table that is mapped to an entity. I'd like to query the primary table and use the existing LINQ construct to do filtering, etc, but then join on the table whose name I'd like to specify at runtime.

AlbertK
User
Posts: 44
Joined: 23-Dec-2009
# Posted on: 30-Sep-2016 21:39:26   

Another possibility - can I create some sort of pseudo entity and repoint it at runtime to a different table before executing the query? I would also need to update the mapping of one of the entity fields in that entity to point to a different column name with the table.

Conceptually, I have a primary table P and extension tables E1, E2, ... which are created dynamically by the application based on user definition of custom fields. I'd like to run queries like below. The reason, I'm hesitating to do dynamic SQL is because I have many additional filters on P with LINQ and would like to use LINQ projection capabilities based on P. I only need to use E1, E2... to filter the results...

select * from P, join E1 on P.Id = E1.Id where E1.ABC = 'abc'

or

select * from P, join E2 on P.Id = E2.Id where E2.DEF = 'def'

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 01-Oct-2016 08:15:10   

Linq, QuerySpec and LLBLGen Runtime are strongly typed query frameworks, so it's no possible to query what it's not known nor mapped. So you might need to find another approach. You could use the link posted by Walaa where you can run raw sql, but you don't have the typed capabilities of Linq, QuerySpec, etc.

David Elizondo | LLBLGen Support Team