Fetching an Entity and specified relations in one SQL Statement

Posts   
 
    
TopDog74
User
Posts: 40
Joined: 27-Apr-2012
# Posted on: 20-Feb-2015 12:26:53   

Hi,

Is there a way when fetching an entity to specify all the relationships i would like to fetch at the same time and therefore have one SQL query with the necessary joins specified.

For example, if i have a Person Entity with a property List<AddressEntity>

is there a way to fetch the PersonEntity with the list of AddressEntities also populated using just 1 SQL statement.

For example, the SQL would be something like this:

SELECT * FROM Person p INNER JOIN Address a ON p.PersonId = a.PersonId

Thanks in advance for your help

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 21-Feb-2015 04:41:22   

No built-in, but you can fetch a DynamicList (DataTable) and project it (your own code) to a new entity graph structure.

David Elizondo | LLBLGen Support Team
TopDog74
User
Posts: 40
Joined: 27-Apr-2012
# Posted on: 24-Feb-2015 03:28:27   

Thanks for your response. So, ultimately i can write QuerySpec, set up a DynamicList, or even call a stored procedure, but at the end of the day, i need to hand craft the code to map from the result of the query to the object graph.

Could the need to fetch an object and some (or all) of it's related entities and collections within 1 SQL statement generating the required JOINS be part of a future release, or is it by design that this is not implemented?

I am interested to know the reasoning behind it. As a product, I have a lot of respect for the work that has been done, but i am thinking that the need to get an entity and it's related entities/collections from the db in one query must be a common scenario.

Thanks again for your help.

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 24-Feb-2015 07:44:42   

Quoting Frans blog ( http://weblogs.asp.net/fbouma/developing-linq-to-llblgen-pro-part-14 ) :

Our prefetch path code has been in the framework since 2004 and it has gone through some revisions where it received more and more tweaks. One of the main tweaks, which is developer controllable, is the setting when LLBLGen Pro should switch from a subquery based filter to an IN (values...) based filter for fetching related entities. LLBLGen Pro uses 1 query per path node and merges sets through hash-value comparisons. It doesn't use joins between parent/child in a tree, as that leads to problems: with 1:n relations it leads to a lot of duplicates and with multi-branched trees it leads to complicated scenarios to fetch the data out the resultset and to very wide resultsets. So using 1 query per path node is much more efficient, and as a bonus: the fetch code for the node itself is the same as you'd normally use so easier to implement as well. The Entity Framework uses joins for related entities, which I don't think is a wise thing to do. 

... I think that the answer is that you might think that 1-query per graph is more efficient but maybe it isn't really. Benchmark your code about that. If you handle well parametized prefetchpath threshold, it will be very efficient. Remember that llblgen just generate one query per graph level, without retrieving unnecessary duplicates values due to joins.

David Elizondo | LLBLGen Support Team
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 24-Feb-2015 10:29:53   

To my knowledge the EF team is also in the process of switching to 1 query per graph node. There are situations however when a joined query could be faster, namely with m:1/1:1 relationships and a single branched graph. As soon as there are multiple branches in the graph or 1:n/m:n relationships in play, you'll get duplicates and it's slower.

We're thinking about adding an option to v5 where you can configure this per query though, to offer the flexibility.

Frans Bouma | Lead developer LLBLGen Pro