Hi,

v5.6.1, SQL Server

I have the following inheritance:

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:

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):

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:

var q = qf.Create().From(qf.User.LeftJoin(qf.CompanyUser).On(CompanyUserFields.Id == UserFields.Id).LeftJoin(CompanyUserEntity.Relations.CompanyEntityUsingCompanyId))

which turns into:

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:

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!

Try the following, using QueryTarget on the second join.

var q = qf.Create()
            .From(qf.User.LeftJoin(qf.CompanyUser)
                      .On(CompanyUserFields.Id.Equal(UserFields.Id)));

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

Walaa wrote:

Try the following, using QueryTarget on the second join.

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

Could you please attach a repro llblgenproj file, or open a hlepdesk (private) thread and attach the original file.

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!

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.

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

Ah now I understand 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.

Otis wrote:

Ah now I understand 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

Quickly to reiterate: I'd like to:

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

Thank you!

Findev wrote:

Otis wrote:

Ah now I understand 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

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.

Quickly to reiterate: I'd like to:

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).

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!

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!

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.

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!

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!