Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > Bugs & Issues> Inheritance and left join
 

Pages: 1
Bugs & Issues
Inheritance and left join
Page:1/1 

  Print all messages in this thread  
Poster Message
Findev
User



Location:

Joined on:
08-Dec-2014 23:21:38
Posted:
63 posts
# Posted on: 08-Jan-2020 11:04:12.  
Hi,

v5.6.1, SQL Server

I have the following inheritance:
Code:

Entities
    Company
    User
        CompanyUser (has CompanyId)

so some users are regular users and some have a company. I'd like to fetch users with optional company name and id (left join). I do see that when User is fetched the run-time does a left join to the CompanyUser table automatically, that makes sense as I'm querying the base User. I thought I could cheat my way around and do something like:
Code:
var q = qf.Create().From(qf.User.LeftJoin(CompanyUserEntity.Relations.CompanyEntityUsingCompanyId))

however, in this case the left join becomes an inner join (besides multi-part identifier errors which probably can be fixed with aliasing):
Code:

SELECT TOP(@p2) [LPA_L2].[Id] AS [CId],
                [LPA_L1].[Name],
                [XYZ].[dbo].[Users].[Id],
                [XYZ].[dbo].[Users].[FirstName],
                [XYZ].[dbo].[Users].[LastName],
                [XYZ].[dbo].[Users].[Email]
FROM (((([XYZ].[dbo].[Entities] [LPA_L3]
         INNER JOIN [XYZ].[dbo].[Users] [LPA_L4]
             ON [LPA_L3].[Id] = [LPA_L4].[Id])
         INNER JOIN [XYZ].[dbo].[CompanyUsers] [LPA_L5]
             ON [LPA_L4].[Id] = [LPA_L5].[Id])
         LEFT JOIN [XYZ].[dbo].[Companies] [LPA_L2]
             ON [LPA_L2].[Id] = [LPA_L5].[CompanyId])
        LEFT JOIN [XYZ].[dbo].[Entities] [LPA_L1]
            ON [LPA_L1].[Id] = [LPA_L2].[Id])


so far came up with:
Code:
var q = qf.Create().From(qf.User.LeftJoin(qf.CompanyUser).On(CompanyUserFields.Id == UserFields.Id).LeftJoin(CompanyUserEntity.Relations.CompanyEntityUsingCompanyId))


which turns into:
Code:

SELECT TOP(100 /* @p2 */) [LPA_L7].[Id] AS [CId],
                [LPA_L6].[Name],
                [LPA_L2].[Id],
                [LPA_L2].[FirstName],
                [LPA_L2].[LastName],
                [LPA_L2].[Email]
FROM (((((([XYZ].[dbo].[Entities] [LPA_L1]
             INNER JOIN [XYZ].[dbo].[Users] [LPA_L2]
                 ON [LPA_L1].[Id] = [LPA_L2].[Id])
            LEFT JOIN [XYZ].[dbo].[CompanyUsers] [LPA_L5]
                ON [LPA_L5].[Id] = [LPA_L2].[Id])
         LEFT JOIN [XYZ].[dbo].[Users] [LPA_L4]
             ON [LPA_L4].[Id] = [LPA_L5].[Id])
         LEFT JOIN [XYZ].[dbo].[Entities] [LPA_L3]
             ON [LPA_L3].[Id] = [LPA_L4].[Id])
         LEFT JOIN [XYZ].[dbo].[Companies] [LPA_L7]
             ON [LPA_L7].[Id] = [LPA_L5].[CompanyId])
        LEFT JOIN [XYZ].[dbo].[Entities] [LPA_L6]
            ON [LPA_L6].[Id] = [LPA_L7].[Id])


CORRECTION: changed inner to left
Is it possible to rework the query so there's no additional two left joins which I don't really need for this query results, but I'd suspect they are being added due to inheritance for the CompanyUser itself. To this:
Code:

SELECT TOP(100 /* @p2 */) [LPA_L7].[Id] AS [CId],
                [LPA_L6].[Name],
                [LPA_L2].[Id],
                [LPA_L2].[FirstName],
                [LPA_L2].[LastName],
                [LPA_L2].[Email]
FROM (((([XYZ].[dbo].[Entities] [LPA_L1]
             INNER JOIN [XYZ].[dbo].[Users] [LPA_L2]
                 ON [LPA_L1].[Id] = [LPA_L2].[Id])
            LEFT JOIN [XYZ].[dbo].[CompanyUsers] [LPA_L5]
                ON [LPA_L5].[Id] = [LPA_L2].[Id])
         LEFT JOIN [XYZ].[dbo].[Companies] [LPA_L7]
             ON [LPA_L7].[Id] = [LPA_L5].[CompanyId])
        LEFT JOIN [XYZ].[dbo].[Entities] [LPA_L6]
            ON [LPA_L6].[Id] = [LPA_L7].[Id])


Curious if there's a good way to optimize that.
Thank you!
  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14567 posts
# Posted on: 09-Jan-2020 05:32:56.  
Try the following, using QueryTarget on the second join.
Code:
var q = qf.Create()
            .From(qf.User.LeftJoin(qf.CompanyUser)
                     .On(CompanyUserFields.Id.Equal(UserFields.Id)));

q.From(QueryTarget.LeftJoin(CompanyUserEntity.Relations.CompanyEntityUsingCompanyId));


  Top
Findev
User



Location:

Joined on:
08-Dec-2014 23:21:38
Posted:
63 posts
# Posted on: 09-Jan-2020 05:55:41.  
Walaa wrote:
Try the following, using QueryTarget on the second join.
Code:
var q = qf.Create()
            .From(qf.User.LeftJoin(qf.CompanyUser)
                     .On(CompanyUserFields.Id.Equal(UserFields.Id)));

q.From(QueryTarget.LeftJoin(CompanyUserEntity.Relations.CompanyEntityUsingCompanyId));


ORM profiler shows that SQL is exactly the same as from my version of the query
  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14567 posts
# Posted on: 09-Jan-2020 05:58:08.  
Could you please attach a repro llblgenproj file, or open a hlepdesk (private) thread and attach the original file.

  Top
Findev
User



Location:

Joined on:
08-Dec-2014 23:21:38
Posted:
63 posts
# Posted on: 09-Jan-2020 06:04:00.  
Walaa wrote:
Could you please attach a repro llblgenproj file, or open a hlepdesk (private) thread and attach the original file.


Alright, will do next week. Thank you!
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37784 posts
# Posted on: 09-Jan-2020 10:27:31.  
The inner join (the one with 'entities') is needed to make sure only the parent rows for that type are read, so supertype inner join subtype and then left joins are added for the additional subtypes.

You can't do without that. If this is a performance problem, then perhaps modeling it differently without inheritance is a better options, although you then still will likely use several tables but you then can control what's joined with what.


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Findev
User



Location:

Joined on:
08-Dec-2014 23:21:38
Posted:
63 posts
# Posted on: 09-Jan-2020 21:52:35.  
Otis wrote:
The inner join (the one with 'entities') is needed to make sure only the parent rows for that type are read, so supertype inner join subtype and then left joins are added for the additional subtypes.

You can't do without that. If this is a performance problem, then perhaps modeling it differently without inheritance is a better options, although you then still will likely use several tables but you then can control what's joined with what.


Seems I made a typo, updated the original question. It was about extra left joins, not inner joins. Sorry about that. Though I suspect you'd say left joins are unavoidable anyway Regular Smiley
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37784 posts
# Posted on: 10-Jan-2020 08:45:59.  
Ah now I understand Regular Smiley Yes these left joins are for the subtypes of the type you're fetching. So you're fetching 'user' instances, so it inner joins its supertypes and then left joins all its subtypes (and subtypes of these etc.). This is by design.

We have a workitem to add a linq/queryspec directive so these extra joins aren't added, but for now these are always added.


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Findev
User



Location:

Joined on:
08-Dec-2014 23:21:38
Posted:
63 posts
# Posted on: 10-Jan-2020 12:19:58.  
Otis wrote:
Ah now I understand Regular Smiley Yes these left joins are for the subtypes of the type you're fetching. So you're fetching 'user' instances, so it inner joins its supertypes and then left joins all its subtypes (and subtypes of these etc.). This is by design.

We have a workitem to add a linq/queryspec directive so these extra joins aren't added, but for now these are always added.


That would be great given that I don't need the data coming from these two extra left joins and directive would (fingers crossed) have some positive impact on the db engine performance Regular Smiley

Quickly to reiterate: I'd like to:
Code:
users (keep own inheritance chain) left join company users (skip own inheritance joins as no data is being requested, just a transient PK/FK stuff) left join company (keep own inheritance chain)


I will keep an eye on the change log then Regular Smiley

Thank you!
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37784 posts
# Posted on: 14-Jan-2020 09:41:02.  
Findev wrote:
Otis wrote:
Ah now I understand Regular Smiley Yes these left joins are for the subtypes of the type you're fetching. So you're fetching 'user' instances, so it inner joins its supertypes and then left joins all its subtypes (and subtypes of these etc.). This is by design.

We have a workitem to add a linq/queryspec directive so these extra joins aren't added, but for now these are always added.


That would be great given that I don't need the data coming from these two extra left joins and directive would (fingers crossed) have some positive impact on the db engine performance Regular Smiley

You have Target-per-entity inheritance, if performance is key I really would look into a different setup without inheritance, as it's solely a benefit for convenience, but never for performance.

Quote:

Quickly to reiterate: I'd like to:
Code:
users (keep own inheritance chain) left join company users (skip own inheritance joins as no data is being requested, just a transient PK/FK stuff) left join company (keep own inheritance chain)


That's not how it will work. The directive will, if everything goes as planned, not join subtypes for the type fetched (in your case 'user', so it won't do left join companyuser for the *user* rows. For joined elements the supertypes are always joined. this is necessary as relationships might be inherited as well (in your case that's not true, but in other cases it might).


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Findev
User



Location:

Joined on:
08-Dec-2014 23:21:38
Posted:
63 posts
# Posted on: 14-Jan-2020 10:10:08.  
Back then I was thinking about the other type of inheritance, the TargetPerEntityHierarchy (single table), but TargetPerEntity felt more logical, agree about convenience. If I remember correctly mixing hierarchies is not possible, so basically down the chain it had to be of the same type. Nevertheless, now I see what you meant by the directive, yeah, won't help.

>> You have Target-per-entity inheritance, if performance is key I really would look into a different setup without inheritance, as it's solely a benefit for convenience, but never for performance.

Hypothesis: if I create new (not replace) entities in the designer and map to those tables without inheritance information, I will have the ability to control what's being joined and how. And use the inheritance based ones where it's applicable. If that would be possible, this partially covers what directive is aimed to do, though directive works without the need of creating extra entities (one entity one table), but doesn't cover the join case. Interesting...

Thank you! Regular Smiley
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37784 posts
# Posted on: 14-Jan-2020 11:40:53.  
Findev wrote:

>> You have Target-per-entity inheritance, if performance is key I really would look into a different setup without inheritance, as it's solely a benefit for convenience, but never for performance.

Hypothesis: if I create new (not replace) entities in the designer and map to those tables without inheritance information, I will have the ability to control what's being joined and how. And use the inheritance based ones where it's applicable. If that would be possible, this partially covers what directive is aimed to do, though directive works without the need of creating extra entities (one entity one table), but doesn't cover the join case. Interesting...

Thank you! Regular Smiley

that's indeed how you can solve it today: entity types are just containers for the entity instances (the table *row*) so you can map new entity types onto the tables and fetch the data with those using non-inheritance.


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Findev
User



Location:

Joined on:
08-Dec-2014 23:21:38
Posted:
63 posts
# Posted on: 14-Jan-2020 11:57:46.  
Otis wrote:
Findev wrote:

>> You have Target-per-entity inheritance, if performance is key I really would look into a different setup without inheritance, as it's solely a benefit for convenience, but never for performance.

Hypothesis: if I create new (not replace) entities in the designer and map to those tables without inheritance information, I will have the ability to control what's being joined and how. And use the inheritance based ones where it's applicable. If that would be possible, this partially covers what directive is aimed to do, though directive works without the need of creating extra entities (one entity one table), but doesn't cover the join case. Interesting...

Thank you! Regular Smiley

that's indeed how you can solve it today: entity types are just containers for the entity instances (the table *row*) so you can map new entity types onto the tables and fetch the data with those using non-inheritance.


Thank you! Regular Smiley
  Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.