Hi!

We are converting some queries implemented using the predicate system to LINQ (specifically to a reusable filter of IQueriable<BaseEntity>). The IQueriable is piped into a method which in turn produces an expression tree with a bunch of ORs (Func< BaseEntity, bool>). This is in turn piped into the where clause of the IQueriable< BaseEntity>. This work fine, for the most part. But when we try to filter on properties on a derived entity (in one of the OR-branches) we see that query is created with an inner join from the derived entity table to the base entity table.

…baseEntity conditions…
||
(x is DerivedEntity) &&
(x as DerivedEntity).NonNullNavigationProp.UserId == userId

We’ve tried various permutations of null checks but they all produce an inner join. What we hope to achieve is to keep the ability to pipe in a IQueriable<BaseEntity> and have it apply the correct filter based on various settings.

Is there a way to achieve this? If not, what would be the recommended workaround?

Using LLBLGen Pro 5.9.2-hotfix-20220720 – SQL Server

Regards Daniel

No, using a base type will always pull in subtype joins as it needs to materialize the subtypes too.

One way to avoid this, if you really don't want the joins, is to map another entity onto the base table and use that in these situations where you don't want the joins.

Rewriting queries to linq ... there's an alternative, using QuerySpec, almost the same expressiveness but you can re-use your predicates and has a more flexible way to append stuff to a query than linq does (it does have the same inheritance characteristics tho) Just a thought in case you need to rewrite a lot

Otis wrote:

No, using a base type will always pull in subtype joins as it needs to materialize the subtypes too.

One way to avoid this, if you really don't want the joins, is to map another entity onto the base table and use that in these situations where you don't want the joins.

Rewriting queries to linq ... there's an alternative, using QuerySpec, almost the same expressiveness but you can re-use your predicates and has a more flexible way to append stuff to a query than linq does (it does have the same inheritance characteristics tho) Just a thought in case you need to rewrite a lot

Thank you for your reply!

Just to clarify my previous post:

The join is expected, as we filter on the properties from the derived entity, but in my head, this should have resulted in a LEFT JOIN as this part of the filter is dependent on the “is” and “as” conditions. The projection is of type BaseEntity, so I’m not sure I see the reason for doing an INNER JOIN from derived to base. I could be wrong, but as I remember it, using only a “x is DerivedType” (without any other conditions) will produce a LEFT JOIN.

Regards Daniel

The 'is' and 'as' operators are merely meant for projections. The main reason is that the type of a row is known only at projection time (so after the joins): as it's determined based on which PK fields are NULL. So if you use an 'is' operator, it is seen as a type filter and it'll filter on the type, using inner joins. You use it in an OR but it doesn't analyze the meaning of the query you specify: so it doesn't know if you use the 'is' as an optional type filter or not. This isn't fixable.

Otis wrote:

The 'is' and 'as' operators are merely meant for projections. The main reason is that the type of a row is known only at projection time (so after the joins): as it's determined based on which PK fields are NULL. So if you use an 'is' operator, it is seen as a type filter and it'll filter on the type, using inner joins. You use it in an OR but it doesn't analyze the meaning of the query you specify: so it doesn't know if you use the 'is' as an optional type filter or not. This isn't fixable.

Ok, then I guess I'll leave it at that 😊

Once again, thanks for quickly getting back to me!

-Daniel

The 'as' filter can work, the 'is' filter doesn't, I think. I tried this query (5.9.3)

var metaData = new LinqMetaData(adapter);
var q = from e in metaData.Employee
        where (e as ManagerEntity).ManagesDepartmentId > 0 ||
               (e as BoardMemberEntity).CompanyCarId > 0 
        select e;

which results in

SELECT DISTINCT [LPA_L1].[EmployeeID]           AS [F7_0],
                [LPA_L1].[Name]                 AS [F7_1],
                [LPA_L1].[StartDate]            AS [F7_2],
                [LPA_L1].[WorksForDepartmentID] AS [F7_3],
                [LPA_L2].[ClerkID]              AS [F4_4],
                [LPA_L2].[JobDescription]       AS [F4_5],
                [LPA_L3].[ManagerID]            AS [F12_4],
                [LPA_L3].[ManagesDepartmentID]  AS [F12_5],
                [LPA_L4].[BoardMemberID]        AS [F2_6],
                [LPA_L4].[CompanyCarID]         AS [F2_7]
FROM   ((([InheritanceTwo].[dbo].[Employee] [LPA_L1]
          LEFT JOIN [InheritanceTwo].[dbo].[Clerk] [LPA_L2]
              ON [LPA_L1].[EmployeeID] = [LPA_L2].[ClerkID])
         LEFT JOIN [InheritanceTwo].[dbo].[Manager] [LPA_L3]
             ON [LPA_L1].[EmployeeID] = [LPA_L3].[ManagerID])
        LEFT JOIN [InheritanceTwo].[dbo].[BoardMember] [LPA_L4]
            ON [LPA_L3].[ManagerID] = [LPA_L4].[BoardMemberID])
WHERE  ((([LPA_L3].[ManagesDepartmentID] > @p1)
      OR ([LPA_L4].[CompanyCarID] > @p2))) 

So you don't need to do an is + as, you can just do a temporary cast to as, it won't fail at runtime as SQL will happily filter on a resultset with NULL values

Sorry for mixing up which of the operators produces the LEFT JOIN.

Yes, the 'AS' operator works for properties (columns) on the derived entity. However, we want to filter on values from a navigation property on the derived entity (example from my previous post):

(x as DerivedEntity).NonNullNavigationProp.UserId == userId

This produces an INNER JOIN.

-Daniel

ah yes, that will introduce an inner join as subtype to parent is always an inner join...